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/