TODO: Add pg_get_acldef(), pg_get_typedefault(), pg_get_attrdef(), pg_get_tabledef(), pg_get_domaindef(), pg_get_functiondef()

"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. +

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

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

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. +