Quick Links

pg_get_tabledef

Lists:	pgsql-hackers

From:	"Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com>
To:	<pgsql-hackers(at)postgresql(dot)org>
Subject:	pg_get_tabledef
Date:	2007-05-21 13:20:48
Message-ID:	46519CB0.2080609@enterprisedb.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi, 
 
i was following a thread some time ago where adding a function pg_get_tabledef
was one of the TODOs for 8.2, but it somehow didn't make it to the
release perhaps because the functionality was not clearly defined? not
sure. 
 
Anyway  i happen to come up with a function for pg_get_tabledef which
works something like following 
 
postgres=# select pg_get_tabledef(16388) ; 
        pg_get_tabledef         
-------------------------------- 
 CREATE TABLE  public.dept 
 ( 
  deptno numeric(2,0) NOT NULL, 
  dname  character varying(14), 
  loc    character varying(13) 
 ) 
 WITHOUT OIDS; 
(1 row) 
 
 
 
i wanted to submit a patch for this, IFF the community wants this
function. The rationale is obviously to help Application developers
writing applications like pgAdmin. Currently this part of SQL needs to
be constructed manually for postgres by the tools. 
 
it is arguable that a table defintion will have constraints , triggers
etc as well, and they can be added without much problem, but i think if
a tool needs to construct an SQL for all table related objects then
functions are already available for them like pg_get_constraintdef,
pg_get_ruledef, pg_get_indexdef, pg_get_triggerdef etc 
 
i understand that you guys don't like someone to develop a patch in
isolation and just come up with it one day, but it really came out as a
by-product of some other work , and i thought you guys might be
interested in it. 
 
if it is desired,  i will submit  a patch for it, within a day or so. 
 
Regards, 
Usama Munir 
EnterpriseDB (<a class="moz-txt-link-abbreviated" href="http://www.enterprisedb.com">www.enterprisedb.com</a>) 
 
 
 
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	2.1 KB

From:	Naz Gassiep <naz(at)mira(dot)net>
To:
Cc:	pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 15:05:03
Message-ID:	4651B51F.3000908@mira.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Just a question, is there any advantage to having this then building a
function in applications that wrap and use pg_dump with a few options?
Surely that's a more appropriate way to achieve this functionality?
- Naz.

Usama Munir wrote:
> Hi,
>
> i was following a thread some time ago where adding a function
> *pg_get_tabledef* was one of the TODOs for 8.2, but it somehow didn't
> make it to the release perhaps because the functionality was not
> clearly defined? not sure.
>
> Anyway i happen to come up with a function for pg_get_tabledef which
> works something like following
>
> /postgres=# select pg_get_tabledef(16388) ;
> pg_get_tabledef
> --------------------------------
> CREATE TABLE public.dept
> (
> deptno numeric(2,0) NOT NULL,
> dname character varying(14),
> loc character varying(13)
> )
> WITHOUT OIDS;
> /(1 row)
>
>
>
> i wanted to submit a patch for this, IFF the community wants this
> function. The rationale is obviously to help Application developers
> writing applications like pgAdmin. Currently this part of SQL needs to
> be constructed manually for postgres by the tools.
>
> it is arguable that a table defintion will have constraints , triggers
> etc as well, and they can be added without much problem, but i think
> if a tool needs to construct an SQL for all table related objects then
> functions are already available for them like pg_get_constraintdef,
> pg_get_ruledef, pg_get_indexdef, pg_get_triggerdef etc
>
> i understand that you guys don't like someone to develop a patch in
> isolation and just come up with it one day, but it really came out as
> a by-product of some other work , and i thought you guys might be
> interested in it.
>
> if it is desired, i will submit a patch for it, within a day or so.
>
> Regards,
> Usama Munir
> EnterpriseDB (www.enterprisedb.com)
>
>
>

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	Naz Gassiep <naz(at)mira(dot)net>
Cc:	pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 15:26:25
Message-ID:	11221.1179761185@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Naz Gassiep <naz(at)mira(dot)net> writes:
> Just a question, is there any advantage to having this then building a
> function in applications that wrap and use pg_dump with a few options?
> Surely that's a more appropriate way to achieve this functionality?

Refactoring pg_dump into some sort of library would clearly be a better
solution. Unfortunately it's also a huge amount of work :-(

There are several reasons why trying to push pg_dump's functionality
into the backend is largely doomed to failure:

* pg_dump needs to be able to dump from older server versions, and
having two completely different code paths for servers before and after
version X would be a mess.

* pg_dump can't consider a table as a monolithic object anyway; problems
like breaking circular dependencies involving DEFAULT expressions
require getting down-and-dirty with the constituent elements. If there
were a monolithic pg_get_table_def function, pg_dump couldn't use it.

* pg_dump ought to be dumping a snapshot of the DB as of its transaction
start time. Most of the backend's catalog access works on SnapshotNow
and hence fails this test. (I fear that we already have some issues
from the get_xxx_def functions that pg_dump uses now.)

regards, tom lane

From:	"Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com>
To:	"Naz Gassiep" <naz(at)mira(dot)net>
Cc:	<pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 15:31:41
Message-ID:	4651BB5D.8070806@enterprisedb.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I think using pg_dump in some cases is a good option , but not all the
time, having a function makes it much cleaner to use 
 
Consider pgAdmin lets say (and there are many such applications out
there) , you need to show object DDL on the RHP and its nicely
formatted and you can copy paste it and then perhaps export it in an
SQL file. 
 
Now imagine you need to spawn a new process from inside the app for
pg_dump, and then make it write to a file and then read the file to
display the object DDL, which is possible but very messy looking code.
Then there are issues with launching external processes on certain
platforms (for example in Java if you start a new process from the
runtime(), you need to make sure you properly flush out its stdout and
stderr streams otherwise it can go in a deadlock etc), i would use a
function, if available anyday 
 
Additionally  there are such functions for other objects, but for
tables you needed to construct it manually, so i also thought this
would just complete the set and make it easier to write an SQL / DDL
exporting app. 
 
 
 
Naz Gassiep wrote:
<blockquote cite="mid4651B51F(dot)3000908(at)mira(dot)net" type="cite">
<pre wrap="">Just a question, is there any advantage to having this then building a
function in applications that wrap and use pg_dump with a few options?
Surely that's a more appropriate way to achieve this functionality?
- Naz.

Usama Munir wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi,

i was following a thread some time ago where adding a function
*pg_get_tabledef* was one of the TODOs for 8.2, but it somehow didn't
make it to the release perhaps because the functionality was not
clearly defined? not sure.

Anyway i happen to come up with a function for pg_get_tabledef which
works something like following

/postgres=# select pg_get_tabledef(16388) ;
pg_get_tabledef
--------------------------------
CREATE TABLE public.dept
(
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13)
)
WITHOUT OIDS;
/(1 row)

i wanted to submit a patch for this, IFF the community wants this
function. The rationale is obviously to help Application developers
writing applications like pgAdmin. Currently this part of SQL needs to
be constructed manually for postgres by the tools.

it is arguable that a table defintion will have constraints , triggers
etc as well, and they can be added without much problem, but i think
if a tool needs to construct an SQL for all table related objects then
functions are already available for them like pg_get_constraintdef,
pg_get_ruledef, pg_get_indexdef, pg_get_triggerdef etc

i understand that you guys don't like someone to develop a patch in
isolation and just come up with it one day, but it really came out as
a by-product of some other work , and i thought you guys might be
interested in it.

if it is desired, i will submit a patch for it, within a day or so.

Regards,
Usama Munir
EnterpriseDB (<a class="moz-txt-link-abbreviated" href="http://www.enterprisedb.com">www.enterprisedb.com</a>)

</pre>
</blockquote>
<pre wrap="">
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
</pre>
</blockquote>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.5 KB

From:	Andrew Dunstan <andrew(at)dunslane(dot)net>
To:	usama(dot)munir(at)enterprisedb(dot)com
Cc:	Naz Gassiep <naz(at)mira(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 16:20:55
Message-ID:	4651C6E7.7040309@dunslane.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Usama Munir wrote:
> I think using pg_dump in some cases is a good option , but not all the
> time, having a function makes it much cleaner to use

That's why having a shared pgdump library as has been previously
mentioned is by far the best solution.

We have discussed this before, and factoring out this functionality into
a shared lib is what needs to be done. I'm not convinced it is as much
work as Tom suggests, but it is certainly a non-trivial task.

cheers

andrew

From:	"Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com>
To:	"Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc:	"Naz Gassiep" <naz(at)mira(dot)net>, <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 18:11:00
Message-ID:	4651E0B4.2020908@enterprisedb.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

When you say pgdump library, do you mean taking all catalog querying
functionality into a contrib like module , exposed as functions and
then have a simple pgdump executable which calls those functions to dump
to a file, because you would still need a pgdump executable i suppose
for people to be able to backup their stuff. Is my understanding
somewhere near actual idea or i am way off here?

Are there any discussions on this topic which could give me a little
more idea? because i would definitely like to take a shot at this.

Regards,
Usama Munir
EnterpriseDB (www.enterprisedb.com)

Andrew Dunstan wrote:
>
>
> Usama Munir wrote:
>> I think using pg_dump in some cases is a good option , but not all
>> the time, having a function makes it much cleaner to use
>
> That's why having a shared pgdump library as has been previously
> mentioned is by far the best solution.
>
> We have discussed this before, and factoring out this functionality
> into a shared lib is what needs to be done. I'm not convinced it is as
> much work as Tom suggests, but it is certainly a non-trivial task.
>
> cheers
>
> andrew

From:	Andrew Dunstan <andrew(at)dunslane(dot)net>
To:	usama(dot)munir(at)enterprisedb(dot)com
Cc:	Naz Gassiep <naz(at)mira(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 18:27:58
Message-ID:	4651E4AE.1020807@dunslane.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

I mean as a shared library - a .so for Unix (or whatever the flavor of
unix uses instead) or a DLL on WIndows. And no, it would not be in
contrib - as I mentioned in another thread yesterday I want to propose
that contrib disappear.

Certainly pg_dump would use the library, and retain all the file
handling processing it does now. But we could also link it into psql,
for example, and expose the results via \ commands.

If you want to have a go at that you'll probably make lots of people
very happy.

cheers

andrew

Usama Munir wrote:
> When you say pgdump library, do you mean taking all catalog querying
> functionality into a contrib like module , exposed as functions and
> then have a simple pgdump executable which calls those functions to
> dump to a file, because you would still need a pgdump executable i
> suppose for people to be able to backup their stuff. Is my
> understanding somewhere near actual idea or i am way off here?
>
>
> Are there any discussions on this topic which could give me a little
> more idea? because i would definitely like to take a shot at this.
>
> Regards,
> Usama Munir
> EnterpriseDB (www.enterprisedb.com)
>
>
> Andrew Dunstan wrote:
>>
>>
>> Usama Munir wrote:
>>> I think using pg_dump in some cases is a good option , but not all
>>> the time, having a function makes it much cleaner to use
>>
>> That's why having a shared pgdump library as has been previously
>> mentioned is by far the best solution.
>>
>> We have discussed this before, and factoring out this functionality
>> into a shared lib is what needs to be done. I'm not convinced it is
>> as much work as Tom suggests, but it is certainly a non-trivial task.
>>
>> cheers
>>
>> andrew
>

From:	"Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com>
To:	"Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc:	"Naz Gassiep" <naz(at)mira(dot)net>, <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 18:37:23
Message-ID:	4651E6E3.3030801@enterprisedb.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Got it. Thanks for the clarification , i suppose the way you described
it , it needs to go over libpq for the database interface, not the
HeapTuple / Form_pg_* , way.

I guess the way forward for me would be to crawl back in my corner,
write up a mini - spec of how i intend to implement it and get back to
you guys.

Thanks for your feedback.

Regards,
Usama Munir.

Andrew Dunstan wrote:
>
> I mean as a shared library - a .so for Unix (or whatever the flavor of
> unix uses instead) or a DLL on WIndows. And no, it would not be in
> contrib - as I mentioned in another thread yesterday I want to propose
> that contrib disappear.
>
> Certainly pg_dump would use the library, and retain all the file
> handling processing it does now. But we could also link it into psql,
> for example, and expose the results via \ commands.
>
> If you want to have a go at that you'll probably make lots of people
> very happy.
>
> cheers
>
> andrew
>
>
> Usama Munir wrote:
>> When you say pgdump library, do you mean taking all catalog querying
>> functionality into a contrib like module , exposed as functions and
>> then have a simple pgdump executable which calls those functions to
>> dump to a file, because you would still need a pgdump executable i
>> suppose for people to be able to backup their stuff. Is my
>> understanding somewhere near actual idea or i am way off here?
>>
>>
>> Are there any discussions on this topic which could give me a little
>> more idea? because i would definitely like to take a shot at this.
>>
>> Regards,
>> Usama Munir
>> EnterpriseDB (www.enterprisedb.com)
>>
>>
>> Andrew Dunstan wrote:
>>>
>>>
>>> Usama Munir wrote:
>>>> I think using pg_dump in some cases is a good option , but not all
>>>> the time, having a function makes it much cleaner to use
>>>
>>> That's why having a shared pgdump library as has been previously
>>> mentioned is by far the best solution.
>>>
>>> We have discussed this before, and factoring out this functionality
>>> into a shared lib is what needs to be done. I'm not convinced it is
>>> as much work as Tom suggests, but it is certainly a non-trivial task.
>>>
>>> cheers
>>>
>>> andrew
>>

From:	Andrew Dunstan <andrew(at)dunslane(dot)net>
To:	usama(dot)munir(at)enterprisedb(dot)com
Cc:	Naz Gassiep <naz(at)mira(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 18:45:55
Message-ID:	4651E8E3.5020507@dunslane.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Usama Munir wrote:
> Got it. Thanks for the clarification , i suppose the way you described
> it , it needs to go over libpq for the database interface, not the
> HeapTuple / Form_pg_* , way.
>
>

Yes, for many reasons including those mentioned in Tom's email on this
subject today.

cheers

andrew

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	usama(dot)munir(at)enterprisedb(dot)com
Cc:	"Andrew Dunstan" <andrew(at)dunslane(dot)net>, "Naz Gassiep" <naz(at)mira(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject:	Re: pg_get_tabledef
Date:	2007-05-21 21:22:38
Message-ID:	28947.1179782558@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

"Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com> writes:
> I guess the way forward for me would be to crawl back in my corner,
> write up a mini - spec of how i intend to implement it and get back to
> you guys.

Well, the *first* thing to do is read pg_dump for awhile. Until you've
grokked what it does to support multiple backend versions and how it
handles inter-object dependencies (dump ordering problems), you won't be
able to write a spec that has any credibility. In particular, the
business about breaking circular dependency loops is something I have
no idea how to handle in a simple "dump library" API.

regards, tom lane

From:	John DeSoi <desoi(at)pgedit(dot)com>
To:	<usama(dot)munir(at)enterprisedb(dot)com>
Cc:	<pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: pg_get_tabledef
Date:	2007-05-22 01:43:04
Message-ID:	3ACF1275-3770-4C99-B2DA-728C27EE00EA@pgedit.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

Hi Usama,

On May 21, 2007, at 9:20 AM, Usama Munir wrote:

> i wanted to submit a patch for this, IFF the community wants this
> function. The rationale is obviously to help Application developers
> writing applications like pgAdmin. Currently this part of SQL needs
> to be constructed manually for postgres by the tools.

I would like to have this functionality (yesterday :) -- I'm
currently working on this for pgEdit. Let me know if you need help
with review or testing.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL