Обсуждение: TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()

Hello,

I can guess some of these:

pg_get_tabledef() : Would take a table name and return the columns and 
associated types

pg_get_acldef(): Would take an object name and return the associated 
roles and permissions for the object

pg_get_typedefault(): This one I am unsure about

pg_get_attrdef(): This one I am unsure about

pg_get_domaindef(): Would take the name of a domain constraint and 
return the definition

pg_get_functionef(): Would take the name of a function and return its 
soure. However, a function can have the same name with different
arguments, so I am a little unsure.

So could I get some further definition?

Joshua D. Drake

"Joshua D. Drake" <jd@commandprompt.com> writes:
> So could I get some further definition?

There are two fairly strong reasons for NOT trying to push more logic
into the backend from pg_dump:

1. It would remove the freedom we currently have to make pg_dump adapt
dumps from old servers to match newer syntax/semantics.  This has saved
our bacon more than once in the past, so it shouldn't be given up
lightly.

2. The backend functions invariably read the catalogs under SnapshotNow
rules, making pg_dump unable to promise a consistent snapshot to the
extent that it relies on them.
        regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> So could I get some further definition?
> 
> There are two fairly strong reasons for NOT trying to push more logic
> into the backend from pg_dump:
> 
> 1. It would remove the freedom we currently have to make pg_dump adapt
> dumps from old servers to match newer syntax/semantics.  This has saved
> our bacon more than once in the past, so it shouldn't be given up
> lightly.
> 
> 2. The backend functions invariably read the catalogs under SnapshotNow
> rules, making pg_dump unable to promise a consistent snapshot to the
> extent that it relies on them.


O.k. color me stupid but what does what you said above have in any way 
to do with what the requirements for these functions are?

Maybe I am misunderstanding the TODO (which is entirely possible due to 
the complete lack of documentation on the feature) but I *thought* all I 
was going to do was create 6 functions that could be called to get 
various useful information?

For example, pg_get_tabledef() would be a very handy function to use for 
just about any abstracted API. As it stands now most (like Pear) create 
their own custom queries/functions to handle it but they are more often 
then not very innefficient.

?

Sincerely,

Joshua D. Drake



> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> >> So could I get some further definition?
> > 
> > There are two fairly strong reasons for NOT trying to push more logic
> > into the backend from pg_dump:
> > 
> > 1. It would remove the freedom we currently have to make pg_dump adapt
> > dumps from old servers to match newer syntax/semantics.  This has saved
> > our bacon more than once in the past, so it shouldn't be given up
> > lightly.
> > 
> > 2. The backend functions invariably read the catalogs under SnapshotNow
> > rules, making pg_dump unable to promise a consistent snapshot to the
> > extent that it relies on them.
> 
> 
> O.k. color me stupid but what does what you said above have in any way 
> to do with what the requirements for these functions are?
> 
> Maybe I am misunderstanding the TODO (which is entirely possible due to 
> the complete lack of documentation on the feature) but I *thought* all I 
> was going to do was create 6 functions that could be called to get 
> various useful information?
> 
> For example, pg_get_tabledef() would be a very handy function to use for 
> just about any abstracted API. As it stands now most (like Pear) create 
> their own custom queries/functions to handle it but they are more often 
> then not very innefficient.

I thought the TODO item was exactly what you described:
* %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),  pg_get_tabledef(), pg_get_domaindef(),
pg_get_functiondef()

We have per-server-version checks in pg_dump, so I figured the idea was
to use more of those functions if the exist, like we do now.  It is true
that you can't modify them for old versions as easily as you can if they
are hardcoded in pg_dump, but we our existing functions seems to work
fine.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

>> Maybe I am misunderstanding the TODO (which is entirely possible due to 
>> the complete lack of documentation on the feature) but I *thought* all I 
>> was going to do was create 6 functions that could be called to get 
>> various useful information?
>>
>> For example, pg_get_tabledef() would be a very handy function to use for 
>> just about any abstracted API. As it stands now most (like Pear) create 
>> their own custom queries/functions to handle it but they are more often 
>> then not very innefficient.
> 
> I thought the TODO item was exactly what you described:
> 
>     * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
>       pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()
> 
> We have per-server-version checks in pg_dump, so I figured the idea was
> to use more of those functions if the exist, like we do now.  It is true
> that you can't modify them for old versions as easily as you can if they
> are hardcoded in pg_dump, but we our existing functions seems to work
> fine.
> 

O.k. so now what I am getting from this thread is, the functions exist 
now in pg_dump but we want to pull them out of pg_dump and push them 
into the backend?

Joshua D. Drake

-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:
> 
> >> Maybe I am misunderstanding the TODO (which is entirely possible due to 
> >> the complete lack of documentation on the feature) but I *thought* all I 
> >> was going to do was create 6 functions that could be called to get 
> >> various useful information?
> >>
> >> For example, pg_get_tabledef() would be a very handy function to use for 
> >> just about any abstracted API. As it stands now most (like Pear) create 
> >> their own custom queries/functions to handle it but they are more often 
> >> then not very innefficient.
> > 
> > I thought the TODO item was exactly what you described:
> > 
> >     * %Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(),
> >       pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()
> > 
> > We have per-server-version checks in pg_dump, so I figured the idea was
> > to use more of those functions if the exist, like we do now.  It is true
> > that you can't modify them for old versions as easily as you can if they
> > are hardcoded in pg_dump, but we our existing functions seems to work
> > fine.
> > 
> 
> O.k. so now what I am getting from this thread is, the functions exist 
> now in pg_dump but we want to pull them out of pg_dump and push them 
> into the backend?

That's what I thought we wanted.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

"Joshua D. Drake" <jd@commandprompt.com> writes:
> O.k. so now what I am getting from this thread is, the functions exist 
> now in pg_dump but we want to pull them out of pg_dump and push them 
> into the backend?

That's exactly what I *don't* want to do.  If you can think of a
use-case for these functions outside of pg_dump, feel free to put them
in the backend, but pg_dump should continue to do things as it does now.
        regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> O.k. so now what I am getting from this thread is, the functions exist 
>> now in pg_dump but we want to pull them out of pg_dump and push them 
>> into the backend?
> 
> That's exactly what I *don't* want to do.  If you can think of a
> use-case for these functions outside of pg_dump, feel free to put them
> in the backend, but pg_dump should continue to do things as it does now.

O.k. well my thought was just to implement the functions for the 
backend. I wasn't even aware of the pg_dump dependency. They would be 
very useful for application developers in general.

So how about this. I can implement them and submit them for hopeful 
inclusion and I will let hackers argue about whether or not they need to 
also be in pg_dump ;).

If we can go down this route, can we go back to my original post so that 
I insure that I develop something that you guys want? Secondly, is this 
something that I can do with SQL and SETOF or do you want them in C?


***
I can guess some of these:

pg_get_tabledef() : Would take a table name and return the columns and 
associated types

pg_get_acldef(): Would take an object name and return the associated 
roles and permissions for the object

pg_get_typedefault(): This one I am unsure about

pg_get_attrdef(): This one I am unsure about

pg_get_domaindef(): Would take the name of a domain constraint and 
return the definition

pg_get_functionef(): Would take the name of a function and return its 
soure. However, a function can have the same name with different
arguments, so I am a little unsure?

So could I get some further definition?
***

Sincerely,

Joshua D. Drake




-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > O.k. so now what I am getting from this thread is, the functions exist 
> > now in pg_dump but we want to pull them out of pg_dump and push them 
> > into the backend?
> 
> That's exactly what I *don't* want to do.  If you can think of a
> use-case for these functions outside of pg_dump, feel free to put them
> in the backend, but pg_dump should continue to do things as it does now.

Oh, OK, I guess.  pg_dump already uses some of those functions so I
figured it should use more, but you work in that area more than I do.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

On Sat, Jun 10, 2006 at 07:33:54PM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> > > O.k. so now what I am getting from this thread is, the functions exist 
> > > now in pg_dump but we want to pull them out of pg_dump and push them 
> > > into the backend?
> > 
> > That's exactly what I *don't* want to do.  If you can think of a
> > use-case for these functions outside of pg_dump, feel free to put them
> > in the backend, but pg_dump should continue to do things as it does now.
> 
> Oh, OK, I guess.  pg_dump already uses some of those functions so I
> figured it should use more, but you work in that area more than I do.

Well, the argument against changing pg_dump is that it would impact the
ability to use the newer version of pg_dump with older backends (which
would be lacking these functions).

ISTM what would be best is to add the functions to the backend, and add
a TODO or comments to pg_dump indicating that it should be changed to
use these functions once 8.1 is no longer supported. Or you could make
pg_dump's use of this code dependent on the server version it connected
to.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Tom Lane said:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> O.k. so now what I am getting from this thread is, the functions exist
>>  now in pg_dump but we want to pull them out of pg_dump and push them
>> into the backend?
>
> That's exactly what I *don't* want to do.  If you can think of a
> use-case for these functions outside of pg_dump, feel free to put them
> in the backend, but pg_dump should continue to do things as it does
> now.
>


ISTR we debated this some time ago and decided that it wasn't a good idea
for pg_dump. I certainly agree with Tom about it.

But I think there is almost certainly a good use case for these apart from
pg_dump. I recall many years ago using IBMs QMF facility that would provide
skeleton select for a table, and maybe it gave a create query too (it was
about 20 years ago, so my memory is not perfect). I have sometimes wished we
had such a thing for use in C&P query construction.

cheers

andrew

> 
> Well, the argument against changing pg_dump is that it would impact the
> ability to use the newer version of pg_dump with older backends (which
> would be lacking these functions).
> 
> ISTM what would be best is to add the functions to the backend, and add
> a TODO or comments to pg_dump indicating that it should be changed to
> use these functions once 8.1 is no longer supported. Or you could make
> pg_dump's use of this code dependent on the server version it connected
> to.

Off list I was speaking with AndrewD and he said that he would expect 
that if we called pg_get_tabledef() it should return the CREATE 
statement for the table.

With all due respect to Andrew, why? At least in my mind these functions 
really belong to app developers.. e.g;

CREATE TABLE foo (id serial);

SELECT pg_get_tabledef(foo) would return

id, serial

Not:

CREATE TABLE foo (id serial);

I mean, I can do either but I would like to get a clear definition of 
what we are looking for here. Maybe:

pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, 
datatype output?

I guess I don't see the advantage of putting pg_dump -s -t in the backend.

Joshua D. Drake


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

On Sat, Jun 10, 2006 at 08:20:15PM -0700, Joshua D. Drake wrote:
> >
> >Well, the argument against changing pg_dump is that it would impact the
> >ability to use the newer version of pg_dump with older backends (which
> >would be lacking these functions).
> >
> >ISTM what would be best is to add the functions to the backend, and add
> >a TODO or comments to pg_dump indicating that it should be changed to
> >use these functions once 8.1 is no longer supported. Or you could make
> >pg_dump's use of this code dependent on the server version it connected
> >to.
> 
> Off list I was speaking with AndrewD and he said that he would expect 
> that if we called pg_get_tabledef() it should return the CREATE 
> statement for the table.
> 
> With all due respect to Andrew, why? At least in my mind these functions 
> really belong to app developers.. e.g;
> 
> CREATE TABLE foo (id serial);
> 
> SELECT pg_get_tabledef(foo) would return
> 
> id, serial
> 
> Not:
> 
> CREATE TABLE foo (id serial);
> 
> I mean, I can do either but I would like to get a clear definition of 
> what we are looking for here. Maybe:
> 
> pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, 
> datatype output?
> 
> I guess I don't see the advantage of putting pg_dump -s -t in the backend.

If all you want is column, datatype, why not just use info_schema, or
newsysviews? Or even the base catalogs?

ISTM what would be of the most value is a way to get the actual DDL you
need to create the table (which includes a heck of a lot more than just
column names and data types).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Joshua D. Drake wrote:

>>
>> Well, the argument against changing pg_dump is that it would impact the
>> ability to use the newer version of pg_dump with older backends (which
>> would be lacking these functions).
>>
>> ISTM what would be best is to add the functions to the backend, and add
>> a TODO or comments to pg_dump indicating that it should be changed to
>> use these functions once 8.1 is no longer supported. Or you could make
>> pg_dump's use of this code dependent on the server version it connected
>> to.
>
>
> Off list I was speaking with AndrewD and he said that he would expect 
> that if we called pg_get_tabledef() it should return the CREATE 
> statement for the table.
>
> With all due respect to Andrew, why? At least in my mind these 
> functions really belong to app developers.. e.g;
>
> CREATE TABLE foo (id serial);
>
> SELECT pg_get_tabledef(foo) would return
>
> id, serial
>
> Not:
>
> CREATE TABLE foo (id serial);
>
> I mean, I can do either but I would like to get a clear definition of 
> what we are looking for here. Maybe:
>
> pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the 
> column, datatype output?
>
> I guess I don't see the advantage of putting pg_dump -s -t in the 
> backend.
>
>


Well, I certainly don't think a setof <name, type> is adequate for 
pg_get_tabledef(). What about constraints? And what you are suggesting 
can probably be got by very simple queries against either the catalog or 
the information schema, and seems to me to have little value.

As for whether or not it belongs in the backend, I don't have strong 
feelings - maybe we could add what I'm suggesting as some \ commands in 
psql - that would certainly be adequate for the purpose I had in mind, 
but might not suit users of higher end design tools.

cheers

andrew

>>
> 
> 
> Well, I certainly don't think a setof <name, type> is adequate for 
> pg_get_tabledef(). What about constraints? And what you are suggesting 
> can probably be got by very simple queries against either the catalog or 
> the information schema, and seems to me to have little value.

Well it isn't simple queries because they aren't documented. It is a lot 
easier to say, select pg_get_tabledesc('foo') then a select with 3 
different joins and a couple of where clauses (I actually don't think it 
is that bad. I have a query that does it.) What I am suggesting is that 
we have a standard way for APIs to get information that they need.

The information doesn't need to be limited to just the name and type, we 
could add cosntraint info. I am not against that at all.

Anyway, I suggest having both functions. One that will spit out the 
actual create information, and the other set that gives user space 
usable information.

Joshua D. Drake



-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

>> CREATE TABLE foo (id serial);
>>
>> I mean, I can do either but I would like to get a clear definition of 
>> what we are looking for here. Maybe:
>>
>> pg_get_tabledef is the actual SQL and pg_get_tabledesc() is the column, 
>> datatype output?
>>
>> I guess I don't see the advantage of putting pg_dump -s -t in the backend.
> 
> If all you want is column, datatype, why not just use info_schema, or
> newsysviews? Or even the base catalogs?

Where do I look in the info_schema? How do I know exactly what I need? 
What is newsysviews?

Of course I know the answers to these but many people don't. Newsysviews 
is a no-op unless it is in the backend (will it be for 8.2?). Secondly 
in a email I just sent I did say we can add anything we want, but the 
CREATE TABLE statement doesn't seem that useful.

I will create either or both I don't really care :).

> ISTM what would be of the most value is a way to get the actual DDL you
> need to create the table (which includes a heck of a lot more than just
> column names and data types).

Name and datatype was just an example. I am trying to get people to 
actually provide feedback (thank you). Andrew brought up that also 
including the constraints would be a good idea which I agree.

Joshua D. Drake


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:
>> If all you want is column, datatype, why not just use info_schema, or
>> newsysviews? Or even the base catalogs?

> Where do I look in the info_schema? How do I know exactly what I need? 
> What is newsysviews?

Exactly the same arguments can be made against any new functions we
invent.  OTOH, I do not think these arguments apply to selecting from
information_schema; that is SQL standard, and if someone doesn't know
what to do with it I don't think it's our fault.
        regards, tom lane

Joshua D. Drake wrote:

> Name and datatype was just an example. I am trying to get people to 
> actually provide feedback (thank you). Andrew brought up that also 
> including the constraints would be a good idea which I agree.

You also need rules, triggers, inheritance, indexes, primary key
specification, foreign keys, default values, CHECK constraints, storage
configuration (i.e., "plain", "extended", etc), statistics
configuration.  Maybe I'm still missing something.  How do you do all
that with a single result set?

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>> If all you want is column, datatype, why not just use info_schema, or
>>> newsysviews? Or even the base catalogs?
> 
>> Where do I look in the info_schema? How do I know exactly what I need? 
>> What is newsysviews?
> 
> Exactly the same arguments can be made against any new functions we
> invent.  OTOH, I do not think these arguments apply to selecting from
> information_schema; that is SQL standard, and if someone doesn't know
> what to do with it I don't think it's our fault.

I am not blaming us :).

I am just saying that certain functions can make life easier.

What is easier?

test=# select column_name, data_type from columns where table_schema != 
'pg_catalog' and table_name = 'email'; column_name | data_type
-------------+----------- score       | real


Or:

select pg_get_user_tabledesc(email);

This is the basis of my argument. I don't really have anything to add.

:)

Joshua D. Drake






> 
>             regards, tom lane
> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Alvaro Herrera wrote:
> Joshua D. Drake wrote:
> 
>> Name and datatype was just an example. I am trying to get people to 
>> actually provide feedback (thank you). Andrew brought up that also 
>> including the constraints would be a good idea which I agree.
> 
> You also need rules, triggers, inheritance, indexes, primary key
> specification, foreign keys, default values, CHECK constraints, storage
> configuration (i.e., "plain", "extended", etc), statistics
> configuration.  Maybe I'm still missing something.  How do you do all
> that with a single result set?
> 
My argument is to find a way to make it a little easier for application 
and API developers.

Most of those people will not need the storage configuration or the 
statistics. Nor will they likely (although less powerful of an argument) 
need the foreign key information.

Default values? Maybe. Check constraints o.k.

It is certainly possible to build a function to return all of this in a 
result set.

Joshua D. Drake

-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Hello,

Trying to get back on point. What is the scope of work for the TODO 
item? Forget everything else I brought up. What is the goal of the 
existing TODO?

Is it to return the CREATE statements for each (where applicable)?

Is it just to create backend versions of the the identical functions in 
pg_dump?

Sincerely,

Joshua D. Drake

-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Trying to get back on point. What is the scope of work for the TODO 
> item? Forget everything else I brought up. What is the goal of the 
> existing TODO?

I'm not sure that the TODO item has a reason to live at all, but surely
the first item of work for it should be to figure out what its use-case
is.  If pg_dump isn't going to use these functions, what will?
        regards, tom lane

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Trying to get back on point. What is the scope of work for the TODO 
>> item? Forget everything else I brought up. What is the goal of the 
>> existing TODO?
> 
> I'm not sure that the TODO item has a reason to live at all, but surely
> the first item of work for it should be to figure out what its use-case
> is.  If pg_dump isn't going to use these functions, what will?

Well I can't think of a reason to use the functions as a way to deliver 
CREATE statements.

Anyone else have thoughts?

Joshua D. Drake

> 
>             regards, tom lane
> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

Joshua D. Drake wrote:
> Tom Lane wrote:
>> "Joshua D. Drake" <jd@commandprompt.com> writes:
>>> Trying to get back on point. What is the scope of work for the TODO 
>>> item? Forget everything else I brought up. What is the goal of the 
>>> existing TODO?
>>
>> I'm not sure that the TODO item has a reason to live at all, but surely
>> the first item of work for it should be to figure out what its use-case
>> is.  If pg_dump isn't going to use these functions, what will?
> 
> Well I can't think of a reason to use the functions as a way to deliver 
> CREATE statements.
> 
> Anyone else have thoughts?

Keeping 'em separate makes sense to me:

1/ API (or info schema views) provides the required data (e.g column 
details for a table).
2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a 
CREATE statement from the column details).

Cheers

Mark

On Mon, Jun 12, 2006 at 03:47:13PM +1200, Mark Kirkwood wrote:
> Joshua D. Drake wrote:
> >Tom Lane wrote:
> >>"Joshua D. Drake" <jd@commandprompt.com> writes:
> >>>Trying to get back on point. What is the scope of work for the TODO 
> >>>item? Forget everything else I brought up. What is the goal of the 
> >>>existing TODO?
> >>
> >>I'm not sure that the TODO item has a reason to live at all, but surely
> >>the first item of work for it should be to figure out what its use-case
> >>is.  If pg_dump isn't going to use these functions, what will?
> >
> >Well I can't think of a reason to use the functions as a way to deliver 
> >CREATE statements.
> >
> >Anyone else have thoughts?
> 
> Keeping 'em separate makes sense to me:
> 
> 1/ API (or info schema views) provides the required data (e.g column 
> details for a table).
> 2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a 
> CREATE statement from the column details).

Which means that every other client that wants to generate DDL
statements has to go through a lot of work to get it right. And then
they have to maintain it for every version of PostgreSQL.

I think it makes a heck of a lot more sense to have a backend function
to do this.

Here's the relevant thread:
http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php

The intention is to flesh out the existing pg_get_blahdef functions,
such as pg_get_viewdef(). This clearly means that the functions should
output a complete CREATE command.

One thing that I think should be clarified... why wouldn't pg_dump be
able to use these functions? Is it because of version compatability?

Also (and I'm not pointing fingers at Bruce here), this is yet another
case where if we had a TODO system that actually captured the relevant
information, we all would have saved a bunch of time here debating how
these functions should work. I really hope there will be a discussion
about this at the anniversary.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:
> On Mon, Jun 12, 2006 at 03:47:13PM +1200, Mark Kirkwood wrote:
>> Keeping 'em separate makes sense to me:
>>
>> 1/ API (or info schema views) provides the required data (e.g column 
>> details for a table).
>> 2/ client (e.g. pg_dump) decides what to do with it (e.g. construct a 
>> CREATE statement from the column details).
> 
> Which means that every other client that wants to generate DDL
> statements has to go through a lot of work to get it right. And then
> they have to maintain it for every version of PostgreSQL.
> 
> I think it makes a heck of a lot more sense to have a backend function
> to do this.
> 
> Here's the relevant thread:
> http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php
> 
> The intention is to flesh out the existing pg_get_blahdef functions,
> such as pg_get_viewdef(). This clearly means that the functions should
> output a complete CREATE command.
> 

Ok, good point, if I'm writing some admin or data movement package, then 
these guys would be great!

I guess a possible compromise for those who want to keep the core 
backend lean is to implement pg_get_blahdef (and friends) in a contrib 
module similar to (or part of) the adminpack stuff.

This would mean that pg_dump would *not* use them - but if I've followed 
this thread properly, that may be fine.

Best wishes

Mark

Mark Kirkwood wrote:

> Jim C. Nasby wrote:
>
>>
>> Here's the relevant thread:
>> http://archives.postgresql.org/pgsql-hackers/2005-12/msg00756.php
>>
>> The intention is to flesh out the existing pg_get_blahdef functions,
>> such as pg_get_viewdef(). This clearly means that the functions should
>> output a complete CREATE command.
>>
>
> Ok, good point, if I'm writing some admin or data movement package, 
> then these guys would be great!
>
> I guess a possible compromise for those who want to keep the core 
> backend lean is to implement pg_get_blahdef (and friends) in a contrib 
> module similar to (or part of) the adminpack stuff.
>
> This would mean that pg_dump would *not* use them - but if I've 
> followed this thread properly, that may be fine.
>
>

Yes ... except that I don't see any good reason to have these in a 
contrib module and keep, say, pg_get_viewdef() in core. They belong 
together, I think, and I don't think they represent so much bloat that 
having them in core would be a huge problem. Either way, pg_dump should 
not use them, I think. One reason pg_dump should not use them is that 
creation might involve several things which it would want to split up 
for reasons of efficiency and robustness, e.g. delaying creation of a 
constraint until after data is loaded.

cheers

andrew

On Mon, Jun 12, 2006 at 08:49:00AM -0400, Andrew Dunstan wrote:
> Yes ... except that I don't see any good reason to have these in a 
> contrib module and keep, say, pg_get_viewdef() in core. They belong 
> together, I think, and I don't think they represent so much bloat that 
> having them in core would be a huge problem. Either way, pg_dump should 
> not use them, I think. One reason pg_dump should not use them is that 
> creation might involve several things which it would want to split up 
> for reasons of efficiency and robustness, e.g. delaying creation of a 
> constraint until after data is loaded.

I would argue that any case you come up with for why pg_dump shouldn't
use them is most likely going to be an issue for people using these
functions as well, so the functions should handle both. In this case, it
would mean adding a 'without_constraints' option to pg_get_tabledef, and
then the appropriate pg_get_tableconstaintdef() functions.

The only reason I've been able to think of for why pg_dump wouldn't use
a *back end* function for this is because it would then be limited to
dumping in the format provided by that backend, which could become an
issue when upgrading. If that is in fact a problem, it might be useful
to break the code that pg_dump uses for generating DDL into it's own
library that others could include. But even if that is done, I still
feel that these functions should be added to the backend.

Actually, putting the functionality into a library that's used by both
pg_dump and these functions probably makes the most sense. The library
would have to handle multiple server versions, but the functions would
just pass in the current server version.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > Trying to get back on point. What is the scope of work for the TODO 
> > item? Forget everything else I brought up. What is the goal of the 
> > existing TODO?
> 
> I'm not sure that the TODO item has a reason to live at all, but surely
> the first item of work for it should be to figure out what its use-case
> is.  If pg_dump isn't going to use these functions, what will?

The original motivation for the functions was that we already have some
functions like this in the backend, and they are used by pg_dump, so for
completeness someone suggested they should be added, and then we can
decide if pg_dump should use them, but we can revisit all of this.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Jim C. Nasby wrote:
>
> The only reason I've been able to think of for why pg_dump wouldn't use
> a *back end* function for this is because it would then be limited to
> dumping in the format provided by that backend, which could become an
> issue when upgrading. If that is in fact a problem, it might be useful
> to break the code that pg_dump uses for generating DDL into it's own
> library that others could include. But even if that is done, I still
> feel that these functions should be added to the backend.
>
>   

Jim, you referred to previous debate in one of your postings. There has 
been previous debate on this issue too.

Before we pull pg_dump to bits let's identify some actual benefit from 
doing so. If you look at the code you will see that it is more than 
somewhat complex. A large scale move like you are proposing would be 
very high risk, IMNSHO.

cheers

andrew

> 
> Before we pull pg_dump to bits let's identify some actual benefit from 
> doing so. If you look at the code you will see that it is more than 
> somewhat complex. A large scale move like you are proposing would be 
> very high risk, IMNSHO.
From a person who deals with customer migrations daily perspective. 
Anything that is going to put the stability and integrity of 
pg_dump/pg_restore in *any* way, is a no op.


Joshua D. Drake




-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> One thing that I think should be clarified... why wouldn't pg_dump be
> able to use these functions? Is it because of version compatability?

This has already been gone over more than once in this thread, let
alone the prior one, but here are some reasons:

* Unless you want to abandon pg_dump support for prior server versions,
these functions won't really help it.  The pg_dump code is structured to
assemble CREATE commands from spare parts; it would be a real mess to do
things significantly differently for some server versions than others.
It certainly wouldn't make pg_dump any smaller or simpler.

* pg_dump doesn't always *want* a unified CREATE command, eg it has to
be able to pull apart tables and their constraints and even sometimes
their default expressions, both for performance and to break circular
references.

* The existing approach makes it possible for pg_dump to adjust syntax
for newer releases when dumping from an older server; functions inside
the older server couldn't do that.  Similarly, the existing approach
sometimes makes it possible for pg_dump to work around bugs in older
servers.  We've done both these things in the past and it's reasonable
to assume we'll need to again.

* Functions inside the backend are probably going to use SnapshotNow
semantics for inspecting the catalogs.  (Avoiding this would generally
mean not depending on *any* existing backend infrastructure such as
catcaches.)  This is very bad from pg_dump's standpoint because pg_dump
wants to deliver a consistent snapshot of the way things were when it
started its transaction.  Arguably, all the places where pg_dump already
depends on backend functions are broken.  (This isn't too bad for table-
related stuff, because pg_dump acquires AccessShareLock which blocks
table DDL changes before it does anything with a table, but I worry
about it in connection with types and functions and so on.)


Basically, going over to a scheme where most of pg_dump's smarts are in
the backend would eliminate the strategy we often recommend of "use the
newer pg_dump when dumping from your older server in preparation for a
major version upgrade".  The newer pg_dump couldn't do anything
different from the older one, if both are mostly reliant on code inside
the older server.  We've had to recommend that approach often enough
that I am not pleased by the prospect of giving up the option in future.
        regards, tom lane

Joshua D. Drake wrote:

> What is easier?
> 
> test=# select column_name, data_type from columns where table_schema != 
> 'pg_catalog' and table_name = 'email';

\d email

So, would you change psql's \d logic to use the new function?  While
answering that, consider that you'd lose the ability to query old
servers that don't have the function.

And before you argue that this is psql-specific, consider that the
(e.g.) pgAdmin developers already use a query to display table
information; what would be the purpose of changing that query?  What use
would be for users to be able to call a function in pgAdmin's query
tool, when they can get the result more easily by using the specific
show-me-the-table feature?


Your query is wrong schema-wise anyway, because it'll mix the columns
from any table named email in any schema (except the only one where it's
pretty unlikely that there'll be a table named email).

Joshua D. Drake wrote:
> Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> >> Trying to get back on point. What is the scope of work for the TODO 
> >> item? Forget everything else I brought up. What is the goal of the 
> >> existing TODO?
> > 
> > I'm not sure that the TODO item has a reason to live at all, but surely
> > the first item of work for it should be to figure out what its use-case
> > is.  If pg_dump isn't going to use these functions, what will?
> 
> Well I can't think of a reason to use the functions as a way to deliver 
> CREATE statements.
> 
> Anyone else have thoughts?

They seem useful because they allow abstract access to system
information without mucking with the system tables.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +