Обсуждение: Retail DDL
Some years ago I gave a talk about $subject, but somehow it dropped off
my radar. Now I'm looking at it again. The idea is to have a function
(or set of functions) that would allow the user to get the DDL for any
database object. Obviously we already have some functions for things
like views and triggers, but most notably we don't have one for tables,
something users have long complained about. I have been trying to think
of a reasonable interface for a single function, where we would pass in,
say, a catalog oid plus an object oid, and maybe some optional extra
arguments. That seems a bit fragile, though. The alternative is that we
have a separate function for each object type, e.g.
pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
sort of consensus before any work gets done.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On Thu, 24 Jul 2025 at 16:26, Andrew Dunstan <andrew@dunslane.net> wrote:
Some years ago I gave a talk about $subject, but somehow it dropped off
my radar. Now I'm looking at it again. The idea is to have a function
(or set of functions) that would allow the user to get the DDL for any
database object. Obviously we already have some functions for things
like views and triggers, but most notably we don't have one for tables,
something users have long complained about. I have been trying to think
of a reasonable interface for a single function, where we would pass in,
say, a catalog oid plus an object oid, and maybe some optional extra
arguments. That seems a bit fragile, though. The alternative is that we
have a separate function for each object type, e.g.
pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
sort of consensus before any work gets done.
Could you do anything with the reg* data types?
Have pg_get_ddl (regclass) return a CREATE TABLE or CREATE VIEW command, as appropriate, while pg_get_ddl (regtype) would return CREATE TYPE etc.
They don't cover all the object types but might be helpful for at least some cases.
On Thu Jul 24, 2025 at 5:26 PM -03, Andrew Dunstan wrote:
> Some years ago I gave a talk about $subject, but somehow it dropped off
> my radar. Now I'm looking at it again. The idea is to have a function
> (or set of functions) that would allow the user to get the DDL for any
> database object. Obviously we already have some functions for things
> like views and triggers, but most notably we don't have one for tables,
> something users have long complained about. I have been trying to think
> of a reasonable interface for a single function, where we would pass in,
> say, a catalog oid plus an object oid, and maybe some optional extra
> arguments. That seems a bit fragile, though. The alternative is that we
> have a separate function for each object type, e.g.
> pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> sort of consensus before any work gets done.
>
What about SHOW CREATE TABLE? Some other databases support this syntax.
--
Matheus Alcantara
On Thu, 24 Jul 2025 at 21:46, Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
>
> On Thu Jul 24, 2025 at 5:26 PM -03, Andrew Dunstan wrote:
> > Some years ago I gave a talk about $subject, but somehow it dropped off
> > my radar. Now I'm looking at it again. The idea is to have a function
> > (or set of functions) that would allow the user to get the DDL for any
> > database object. Obviously we already have some functions for things
> > like views and triggers, but most notably we don't have one for tables,
> > something users have long complained about. I have been trying to think
> > of a reasonable interface for a single function, where we would pass in,
> > say, a catalog oid plus an object oid, and maybe some optional extra
> > arguments. That seems a bit fragile, though. The alternative is that we
> > have a separate function for each object type, e.g.
> > pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> > sort of consensus before any work gets done.
> >
> What about SHOW CREATE TABLE? Some other databases support this syntax.
SHOW is reserved for returning parameters. I don't think we want to
muddy the purpose of that command.
--
Thom Brown
Data Egret (https://dataegret.com)
Andrew Dunstan <andrew@dunslane.net> writes:
> .... I have been trying to think
> of a reasonable interface for a single function, where we would pass in,
> say, a catalog oid plus an object oid, and maybe some optional extra
> arguments. That seems a bit fragile, though. The alternative is that we
> have a separate function for each object type, e.g.
> pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> sort of consensus before any work gets done.
I'm good with pg_get_{objecttype}_ddl. The reason I like it is that
that sets expectations for what the function can do, and we don't
have to immediately cover every object type there is in order to not
have a function with unexpected restrictions.
A small advantage is that, for object types having a reg* pseudotype,
we can declare the function as (say)
pg_get_table_ddl(regclass)
and that means this will work with no additional decoration:
select pg_get_table_ddl('mytable');
Nearby, Isaac suggested sort of the reverse of that, where
you'd have to write
select pg_get_ddl('mytable'::regclass);
but I don't see any great advantages in that --- and it can't scale
to object types that lack a reg* type.
regards, tom lane
On Fri, Jul 25, 2025 at 3:06 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andrew Dunstan <andrew@dunslane.net> writes:
> > .... I have been trying to think
> > of a reasonable interface for a single function, where we would pass in,
> > say, a catalog oid plus an object oid, and maybe some optional extra
> > arguments. That seems a bit fragile, though. The alternative is that we
> > have a separate function for each object type, e.g.
> > pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> > sort of consensus before any work gets done.
>
> I'm good with pg_get_{objecttype}_ddl. The reason I like it is that
> that sets expectations for what the function can do, and we don't
> have to immediately cover every object type there is in order to not
> have a function with unexpected restrictions.
>
> A small advantage is that, for object types having a reg* pseudotype,
> we can declare the function as (say)
>
> pg_get_table_ddl(regclass)
>
> and that means this will work with no additional decoration:
>
> select pg_get_table_ddl('mytable');
>
> Nearby, Isaac suggested sort of the reverse of that, where
> you'd have to write
>
> select pg_get_ddl('mytable'::regclass);
>
> but I don't see any great advantages in that --- and it can't scale
> to object types that lack a reg* type.
OTOH, we can have a common function and pass object type as parameter
i.e. select pg_get_ddl('table', 'mytable'), with this the same
function can be extended for different object types.
--
Regards,
Dilip Kumar
Google
Dilip Kumar <dilipbalaut@gmail.com> writes:
> OTOH, we can have a common function and pass object type as parameter
> i.e. select pg_get_ddl('table', 'mytable'), with this the same
> function can be extended for different object types.
And you'll work regclass/regtype/etc into that how? AFAICS the
only way would involve fundamentally redundant typing:
select pg_get_ddl('table', 'mytable'::regclass);
How is that better?
regards, tom lane
On Fri, Jul 25, 2025 at 9:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Dilip Kumar <dilipbalaut@gmail.com> writes:
> > OTOH, we can have a common function and pass object type as parameter
> > i.e. select pg_get_ddl('table', 'mytable'), with this the same
> > function can be extended for different object types.
>
> And you'll work regclass/regtype/etc into that how? AFAICS the
> only way would involve fundamentally redundant typing:
>
> select pg_get_ddl('table', 'mytable'::regclass);
>
> How is that better?
I got your point that now we need redundant typing for the objects
which already have reg* types, I think the advantage of this is we
don't need to have different functions names if we support multiple
object types like pg_get_table_ddl, pg_get_function_ddl,
pg_get_role_ddl, instead we can just do that with pg_get_ddl('table',
'mytable'); pg_get_ddl('function', 'mytable'); pg_get_ddl('role',
'myrole'); etc.
--
Regards,
Dilip Kumar
Google
Hi Andrew,
On Fri, Jul 25, 2025 at 1:56 AM Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Some years ago I gave a talk about $subject, but somehow it dropped off
> my radar. Now I'm looking at it again. The idea is to have a function
> (or set of functions) that would allow the user to get the DDL for any
> database object. Obviously we already have some functions for things
> like views and triggers, but most notably we don't have one for tables,
> something users have long complained about. I have been trying to think
> of a reasonable interface for a single function, where we would pass in,
> say, a catalog oid plus an object oid, and maybe some optional extra
> arguments. That seems a bit fragile, though. The alternative is that we
> have a separate function for each object type, e.g.
> pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like some
> sort of consensus before any work gets done.
>
We have something roughly in that category in the form of functions in
[1]. How about extending/contracting that interface to give us DDL of
the given object as well?
I mentioned contracting because the new interface may not handle
objects like columns which do not have an independent DDL. Or maybe we
could re-imagine DDL for such objects as ALTER .... ADD variant for
that object. For example, in case of a column it would be ALTER TABLE
... ADD COLUMN ....
[1] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-OBJECT-TABLE
--
Best Wishes,
Ashutosh Bapat
On 2025-Jul-24, Andrew Dunstan wrote:
> Obviously we already have some functions for things like views and
> triggers, but most notably we don't have one for tables, something users
> have long complained about. I have been trying to think of a reasonable
> interface for a single function, where we would pass in, say, a catalog oid
> plus an object oid, and maybe some optional extra arguments.
Reproducing a table might need multiple commands. Do you intend to
return a single string containing multiple semicolon-separated commands,
or are you thinking in a RETURNS SETOF where each row contains a single
command?
What about schema-qualification needed for elements in the commands? We
have the option to schema-qualify everything, _or_ to depend on whether
the schemas are in search_path, _or_ to schema-qualify nothing (which
gives the user the chance to recreate in any schema by changing
search_path).
> That seems a bit fragile, though. The alternative is that we have a
> separate function for each object type, e.g. pg_get_{objecttype}_ddl.
> I'm kinda leaning that way, but I'd like some sort of consensus before
> any work gets done.
It looks like the discussion is leaning this way too. I think it's
a reasonable choice.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
I have been thinking of this from a little different direction. We
already have all the needed functionality in pg_dump so why not just
have an option to do
CREATE EXTENSION pg_dump;
Which would wrap and expose whatever the current version of pg_dump is doing.
It still would need to resolve the difficult question of naming
things, but otherways it looks like just a certain amount of
mechanical work.
We could even have just one set of functions with a few possible argument types
pg_dump(object oid, options text);
pg_dump(object text, options text);
---
Hannu
On Fri, Jul 25, 2025 at 10:35 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2025-Jul-24, Andrew Dunstan wrote:
>
> > Obviously we already have some functions for things like views and
> > triggers, but most notably we don't have one for tables, something users
> > have long complained about. I have been trying to think of a reasonable
> > interface for a single function, where we would pass in, say, a catalog oid
> > plus an object oid, and maybe some optional extra arguments.
>
> Reproducing a table might need multiple commands. Do you intend to
> return a single string containing multiple semicolon-separated commands,
> or are you thinking in a RETURNS SETOF where each row contains a single
> command?
>
> What about schema-qualification needed for elements in the commands? We
> have the option to schema-qualify everything, _or_ to depend on whether
> the schemas are in search_path, _or_ to schema-qualify nothing (which
> gives the user the chance to recreate in any schema by changing
> search_path).
>
>
> > That seems a bit fragile, though. The alternative is that we have a
> > separate function for each object type, e.g. pg_get_{objecttype}_ddl.
> > I'm kinda leaning that way, but I'd like some sort of consensus before
> > any work gets done.
>
> It looks like the discussion is leaning this way too. I think it's
> a reasonable choice.
>
> --
> Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
>
>
A related improvement would be to also support
CREATE EXTENSION psql;
To make at least the `\d ...` commands available to any client
And while we are at it, why not also
CREATE EXTENSION pgbench;
To make the fancy random distribution functions (at least) from
pgbench available from inside the database.
On Fri, Jul 25, 2025 at 10:43 AM Hannu Krosing <hannuk@google.com> wrote:
>
> I have been thinking of this from a little different direction. We
> already have all the needed functionality in pg_dump so why not just
> have an option to do
>
> CREATE EXTENSION pg_dump;
>
> Which would wrap and expose whatever the current version of pg_dump is doing.
>
> It still would need to resolve the difficult question of naming
> things, but otherways it looks like just a certain amount of
> mechanical work.
>
> We could even have just one set of functions with a few possible argument types
>
> pg_dump(object oid, options text);
> pg_dump(object text, options text);
>
> ---
> Hannu
>
>
>
>
>
>
>
>
>
> On Fri, Jul 25, 2025 at 10:35 AM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> >
> > On 2025-Jul-24, Andrew Dunstan wrote:
> >
> > > Obviously we already have some functions for things like views and
> > > triggers, but most notably we don't have one for tables, something users
> > > have long complained about. I have been trying to think of a reasonable
> > > interface for a single function, where we would pass in, say, a catalog oid
> > > plus an object oid, and maybe some optional extra arguments.
> >
> > Reproducing a table might need multiple commands. Do you intend to
> > return a single string containing multiple semicolon-separated commands,
> > or are you thinking in a RETURNS SETOF where each row contains a single
> > command?
> >
> > What about schema-qualification needed for elements in the commands? We
> > have the option to schema-qualify everything, _or_ to depend on whether
> > the schemas are in search_path, _or_ to schema-qualify nothing (which
> > gives the user the chance to recreate in any schema by changing
> > search_path).
> >
> >
> > > That seems a bit fragile, though. The alternative is that we have a
> > > separate function for each object type, e.g. pg_get_{objecttype}_ddl.
> > > I'm kinda leaning that way, but I'd like some sort of consensus before
> > > any work gets done.
> >
> > It looks like the discussion is leaning this way too. I think it's
> > a reasonable choice.
> >
> > --
> > Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
> >
> >
On 2025-07-25 Fr 4:34 AM, Álvaro Herrera wrote:
> On 2025-Jul-24, Andrew Dunstan wrote:
>
>> Obviously we already have some functions for things like views and
>> triggers, but most notably we don't have one for tables, something users
>> have long complained about. I have been trying to think of a reasonable
>> interface for a single function, where we would pass in, say, a catalog oid
>> plus an object oid, and maybe some optional extra arguments.
> Reproducing a table might need multiple commands. Do you intend to
> return a single string containing multiple semicolon-separated commands,
> or are you thinking in a RETURNS SETOF where each row contains a single
> command?
probably SETOF TEXT, but I'm open to persuasion. What would be best for
using it in some psql meta-commands?
> What about schema-qualification needed for elements in the commands? We
> have the option to schema-qualify everything, _or_ to depend on whether
> the schemas are in search_path, _or_ to schema-qualify nothing (which
> gives the user the chance to recreate in any schema by changing
> search_path).
>
Good question. Maybe that needs to be a function argument, say
defaulting to depending on the current search path.
>> That seems a bit fragile, though. The alternative is that we have a
>> separate function for each object type, e.g. pg_get_{objecttype}_ddl.
>> I'm kinda leaning that way, but I'd like some sort of consensus before
>> any work gets done.
> It looks like the discussion is leaning this way too. I think it's
> a reasonable choice.
>
Thanks. The only reason in my head against it was that it would expand
the number of visible functions, but I think that loses out against the
straightforwardness of this approach.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> Reproducing a table might need multiple commands. Do you intend to
> return a single string containing multiple semicolon-separated commands,
> or are you thinking in a RETURNS SETOF where each row contains a single
> command?
In the same vein: would we expect this command to also build the
table's indexes? What about foreign key constraints, which might
well reference tables that don't exist yet?
Once you start crawling down this rabbit-hole, you soon realize
why pg_dump is as complicated as it is.
regards, tom lane
Hi,
On Jul 25, 2025 at 21:35 +0800, Tom Lane <tgl@sss.pgh.pa.us>, wrote:
On Jul 25, 2025 at 21:35 +0800, Tom Lane <tgl@sss.pgh.pa.us>, wrote:
Álvaro Herrera <alvherre@kurilemu.de> writes:Reproducing a table might need multiple commands. Do you intend to
return a single string containing multiple semicolon-separated commands,
or are you thinking in a RETURNS SETOF where each row contains a single
command?
In the same vein: would we expect this command to also build the
table's indexes? What about foreign key constraints, which might
well reference tables that don't exist yet?
Once you start crawling down this rabbit-hole, you soon realize
why pg_dump is as complicated as it is.
First of all, +1 to this suggestion.
I've long believed there should be a standard way to get a table's DDL (like MySQL and Oracle have), especially when our DBAs encounter issues in customer
environments or when we need to cross-validate problems across different cluster versions.
This would make problem reproduction much more convenient. Currently, we're using pg_dump as our workaround.
Regarding the complexity you mentioned - absolutely, it's a real challenge.
MySQL's approach is to include all of a table's indexes in the DDL output. But this becomes problematic when dealing with foreign key dependencies between tables.
I think we could start with implementing basic table DDL and index generation first, as these are the most commonly needed features in practice.
For other objects related to the table, we can clearly document them.
Additionally, I have another suggestion - could we have a quick backslash command to display DDL? Something like \d+ t1, or perhaps \dddl? Looking at the code,
it seems there aren't many available command slots remaining.
--
Zhang Mingli
HashData
Hi Andrew,
On 24/07/2025 22:26, Andrew Dunstan wrote:
> Some years ago I gave a talk about $subject, but somehow it dropped
> off my radar. Now I'm looking at it again. The idea is to have a
> function (or set of functions) that would allow the user to get the
> DDL for any database object. Obviously we already have some functions
> for things like views and triggers, but most notably we don't have one
> for tables, something users have long complained about. I have been
> trying to think of a reasonable interface for a single function, where
> we would pass in, say, a catalog oid plus an object oid, and maybe
> some optional extra arguments. That seems a bit fragile, though. The
> alternative is that we have a separate function for each object type,
> e.g. pg_get_{objecttype}_ddl. I'm kinda leaning that way, but I'd like
> some sort of consensus before any work gets done.
$subject has been appearing on the lists every now and then, without
much great success so far.
I have endeavored to implement such a thing as ddlx postgres extension,
https://github.com/lacanoid/pgddl
The endeavor is somewhat far gone now already. Apparently the extension
is used by some people. It probably has some interesting features. It
needs wider and more testing. I use it a lot. It tries to address some
of the issues on $subject expressed on the lists.
It is implemented as plain SQL functions. There are currently 89
functions with obvious names, one for each postgres object type, as well
as functions to assemble smaller pieces together and such. I think it
implements a rather nice SQL API, also handling some of the things
discussed here.
Of particular note is using oids only (no classid) to specify objects. I
used believe that oid are unique across a postgres database for catalog
objects, but since postgres 14 this no longer the case, see:
https://github.com/lacanoid/pgddl/issues/25 . I don't know if this is
intentional or not. In practice, it does not hinder usage.
On 2025-08-13 We 10:29 PM, Ziga wrote:
> Hi Andrew,
>
> On 24/07/2025 22:26, Andrew Dunstan wrote:
>> Some years ago I gave a talk about $subject, but somehow it dropped
>> off my radar. Now I'm looking at it again. The idea is to have a
>> function (or set of functions) that would allow the user to get the
>> DDL for any database object. Obviously we already have some functions
>> for things like views and triggers, but most notably we don't have
>> one for tables, something users have long complained about. I have
>> been trying to think of a reasonable interface for a single function,
>> where we would pass in, say, a catalog oid plus an object oid, and
>> maybe some optional extra arguments. That seems a bit fragile,
>> though. The alternative is that we have a separate function for each
>> object type, e.g. pg_get_{objecttype}_ddl. I'm kinda leaning that
>> way, but I'd like some sort of consensus before any work gets done.
>
> $subject has been appearing on the lists every now and then, without
> much great success so far.
>
> I have endeavored to implement such a thing as ddlx postgres
> extension, https://github.com/lacanoid/pgddl
>
> The endeavor is somewhat far gone now already. Apparently the
> extension is used by some people. It probably has some interesting
> features. It needs wider and more testing. I use it a lot. It tries to
> address some of the issues on $subject expressed on the lists.
>
> It is implemented as plain SQL functions. There are currently 89
> functions with obvious names, one for each postgres object type, as
> well as functions to assemble smaller pieces together and such. I
> think it implements a rather nice SQL API, also handling some of the
> things discussed here.
>
> Of particular note is using oids only (no classid) to specify objects.
> I used believe that oid are unique across a postgres database for
> catalog objects, but since postgres 14 this no longer the case, see:
> https://github.com/lacanoid/pgddl/issues/25 . I don't know if this is
> intentional or not. In practice, it does not hinder usage.
Interesting. I think there are good reasons to have this as builtin
functions, though, not least that it would allow us to base some psql
meta-commands on it, or possibly an SQL command (DESCRIBE ?). Builtin
functions are also likely to be faster.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi!
On Thu, 14 Aug 2025 at 23:30, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Interesting. I think there are good reasons to have this as builtin
> functions, though, not least that it would allow us to base some psql
> meta-commands on it, or possibly an SQL command (DESCRIBE ?).
DESCRIBE would be confusing with extended protocol Describe message,
used for prepared statements and portals. At least for me this would
be confusing.
> Builtin
> functions are also likely to be faster.
We are not actually aiming for speed here, aren’t we?
Overall, Im +1 on `pg_get_{objecttype}_ddl` or maybe
`pg_show_{objecttype}_ddl` design.
--
Best regards,
Kirill Reshke
On Sat, 16 Aug 2025 at 10:08, I wrote:
> On Thu, 14 Aug 2025 at 23:30, Andrew Dunstan <andrew@dunslane.net> wrote:
> > Builtin
> > functions are also likely to be faster.
>
> We are not actually aiming for speed here, aren’t we?
I want to clarify here: I do not think consuming limiter resources of
catalog OID for builtin functions is worth the benefit here.
>
> Overall, Im +1 on `pg_get_{objecttype}_ddl` or maybe
> `pg_show_{objecttype}_ddl` design.
After putting some more thought into it, maybe we can implement the
whole thing as contrib extension? This would be the most Postgres-y
way to me.
--
Best regards,
Kirill Reshke
On 2025-Aug-16, Kirill Reshke wrote: > After putting some more thought into it, maybe we can implement the > whole thing as contrib extension? This would be the most Postgres-y > way to me. If we do that, then core tools such as psql or pg_dump can never depend on them. -1 from me. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Always assume the user will do much worse than the stupidest thing you can imagine." (Julien PUYDT)
Hello Ziga, On 2025-Aug-14, Ziga wrote: > Of particular note is using oids only (no classid) to specify objects. I > used believe that oid are unique across a postgres database for catalog > objects, but since postgres 14 this no longer the case, see: > https://github.com/lacanoid/pgddl/issues/25 . I don't know if this is > intentional or not. In practice, it does not hinder usage. It's never been the case, actually --- with older versions, you only needed to let the OID counter wrap around, and then it would be possible to create (say) a function with the same OID as a table. Back then it was unusual that OIDs would wrap around, and even then it's hard to be so unlucky that the OID generator gives you the same value exactly when it's time to create an object of a different type; but it's certainly always been a possibility. So the idea of passing just an OID is fundamentally bogus. The class-id (or some other way to identify which type of object the user wants) must be mandatory, in order for the API to be robust. Regards -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "I dream about dreams about dreams", sang the nightingale under the pale moon (Sandman)
=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> On 2025-Aug-16, Kirill Reshke wrote:
>> After putting some more thought into it, maybe we can implement the
>> whole thing as contrib extension? This would be the most Postgres-y
>> way to me.
> If we do that, then core tools such as psql or pg_dump can never depend
> on them. -1 from me.
pg_dump will never depend on any such thing anyway. It has too many
special-purpose requirements, like needing to split up object
definitions in particular ways, cope with very old server versions,
etc etc. Insisting that this feature support pg_dump is a good way
of making sure that nothing useful will emerge at all.
Maybe we could replace (some of) psql's describe.c logic with
server-side code, but I'm skeptical that there'd be much win
there either.
So I don't really buy Álvaro's argument above. It'd be better
to design to some concrete requirement that isn't either of
those. Unfortunately, it's not clear to me that anyone has
a concrete use-case in mind that isn't either of those.
regards, tom lane
On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote: > So I don't really buy Álvaro's argument above. It'd be better > to design to some concrete requirement that isn't either of > those. Unfortunately, it's not clear to me that anyone has > a concrete use-case in mind that isn't either of those. I have wanted this MANY times. I've had this as a PG user: I think it's literally the first thing I did when connecting to a Postgres server the first time. Coming from MySQL, I wanted to see the exact table definition, and there it was as easy as "DESCRIBE some_table". I quickly learned about "\d some_table", but it was not the same. I could not copy paste the result and slightly modify it to create a new (but similar) table, which is something I still would like to have to this day. I do not know table DDL by heart, so often I just want an example to start from. I don't think this should be thought of as replacing \d, but it could greatly improve it. Just like "\d+ some_view" shows the view definition, "\d+ some_table" could be showing the table definition in SQL. Not having this in core, will make it impossible for psql to show such definitions. Finally I've also wanted this as an extension author: We basically built something like this for Citus to be able to recreate shard-tables with the same things as parent tables. And I did a similar thing in pg_duckdb again. I don't think I would have been able to use this code verbatim for these usecases (similarly to how pg_dump couldn't), but I would at least have a good base that I could copy paste from.
Jelte Fennema-Nio <postgres@jeltef.nl> writes:
> On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So I don't really buy Álvaro's argument above. It'd be better
>> to design to some concrete requirement that isn't either of
>> those. Unfortunately, it's not clear to me that anyone has
>> a concrete use-case in mind that isn't either of those.
> I have wanted this MANY times. I've had this as a PG user: I think
> it's literally the first thing I did when connecting to a Postgres
> server the first time. Coming from MySQL, I wanted to see the exact
> table definition, and there it was as easy as "DESCRIBE some_table".
You haven't actually defined what "this" is. For starters, do you
really want this output to be included in \d? Seems like one part
or the other of such output would be clutter, so I'd be more minded
to leave \d alone and invent some new command. (By analogy to \sf,
maybe \st and so on?)
But the real issue is what to print. In the case of a table, should
we also show its indexes? What about foreign keys to or from other
tables? If it's a partitioned table, what about the partitions?
I'm not sure this is as simple as it seems.
regards, tom lane
On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You haven't actually defined what "this" is. For starters, do you
> really want this output to be included in \d? Seems like one part
> or the other of such output would be clutter, so I'd be more minded
> to leave \d alone and invent some new command. (By analogy to \sf,
> maybe \st and so on?)
That makes sense, I don't think I had seen the \s ones before. IMO it
would be super useful to have a \s for every type of object that
currently has a \d (but those don't have to be all added in the same
patchset ofcours, starting with tables seems totally sensible)
> But the real issue is what to print.
I think you're making this sound much harder than it actually is. I
think it would be perfect if it had exactly the same info as \d but in
SQL form instead of some "easy to understand by humans form". So:
> should we also show its indexes?
Yes
> What about foreign keys to or from other
> tables?
Yes, both from and to
> If it's a partitioned table, what about the partitions?
Definitely not by default, way too much clutter.
I think having a DESCRIBE keyword is probably not what we want, but
adding a pg_get_tabledef function seems totally reasonable. I even
proposed that at some point[1], but apparently never followed up with
Kirk (cc-ed now). It could even have options for all the questions
that you're asking like, so we'd "just" need to decide on the
defaults:
SELECT pg_get_tabledef('my_table', include_indexes => true,
include_partitions => true)
[1]: https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com
On 2025-08-18 Mo 9:57 AM, Tom Lane wrote: > Jelte Fennema-Nio <postgres@jeltef.nl> writes: >> On Sat, 16 Aug 2025 at 16:23, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> So I don't really buy Álvaro's argument above. It'd be better >>> to design to some concrete requirement that isn't either of >>> those. Unfortunately, it's not clear to me that anyone has >>> a concrete use-case in mind that isn't either of those. >> I have wanted this MANY times. I've had this as a PG user: I think >> it's literally the first thing I did when connecting to a Postgres >> server the first time. Coming from MySQL, I wanted to see the exact >> table definition, and there it was as easy as "DESCRIBE some_table". > You haven't actually defined what "this" is. For starters, do you > really want this output to be included in \d? Seems like one part > or the other of such output would be clutter, so I'd be more minded > to leave \d alone and invent some new command. (By analogy to \sf, > maybe \st and so on?) Yes, I agree. A separate metacommand would make more sense. Maybe something like \sdx where x is some object type designator. (sd stands for show ddd or show definition) > > But the real issue is what to print. In the case of a table, should > we also show its indexes? What about foreign keys to or from other > tables? If it's a partitioned table, what about the partitions? > I'm not sure this is as simple as it seems. > > Agreed it's not simple, but that doesn't mean we should not do it. Tables are the most obviously complex case. I'm inclined to say foreign keys to but not from, and also include indexes. But maybe we can provide several flavors, by allowing some function options, e.g. \sdt would show the basic table def without FKs or secondary indexes, and \sdt+ would show everything Or we could get more fine-grained. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On Mon, 18 Aug 2025 at 10:32, Andrew Dunstan <andrew@dunslane.net> wrote:
> But the real issue is what to print. In the case of a table, should
> we also show its indexes? What about foreign keys to or from other
> tables? If it's a partitioned table, what about the partitions?
> I'm not sure this is as simple as it seems.
Agreed it's not simple, but that doesn't mean we should not do it.
Tables are the most obviously complex case. I'm inclined to say foreign
keys to but not from, and also include indexes. But maybe we can provide
several flavors, by allowing some function options, e.g.
Are you sure you don't mean from but not to?
If I want foreign keys from a table when looking at that table's definition, they can be part of a single CREATE TABLE statement. If I want foreign keys to that table, I need a bunch of ALTER TABLE statements naming the other tables whose foreign keys point at the table in question.
On 2025-08-18 Mo 10:39 AM, Isaac Morland wrote:
On Mon, 18 Aug 2025 at 10:32, Andrew Dunstan <andrew@dunslane.net> wrote:> But the real issue is what to print. In the case of a table, should
> we also show its indexes? What about foreign keys to or from other
> tables? If it's a partitioned table, what about the partitions?
> I'm not sure this is as simple as it seems.
Agreed it's not simple, but that doesn't mean we should not do it.
Tables are the most obviously complex case. I'm inclined to say foreign
keys to but not from, and also include indexes. But maybe we can provide
several flavors, by allowing some function options, e.g.Are you sure you don't mean from but not to?If I want foreign keys from a table when looking at that table's definition, they can be part of a single CREATE TABLE statement. If I want foreign keys to that table, I need a bunch of ALTER TABLE statements naming the other tables whose foreign keys point at the table in question.
Sorry. I mean FK constraints on the table in question. I guess that's "from but not to", yes.
cheers
andrew
-- Andrew Dunstan EDB: https://www.enterprisedb.com
On Sat, Aug 16, 2025 at 4:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes: > > On 2025-Aug-16, Kirill Reshke wrote: > >> After putting some more thought into it, maybe we can implement the > >> whole thing as contrib extension? This would be the most Postgres-y > >> way to me. > > > If we do that, then core tools such as psql or pg_dump can never depend > > on them. -1 from me. > > pg_dump will never depend on any such thing anyway. It has too many > special-purpose requirements, like needing to split up object > definitions in particular ways, That can be handy for the use of get_ddl_function as well. I remember creating a view, then creating a set-returning function returning that view and then redefining the view to be a select from that function. pg_dump did split this up in a nicel dumpable way, though a little different than the original set of DDL. We definitely want our server functions to be able to also cope with circularities. > cope with very old server versions, This is never needed here, as we only show DDL for our own version. > etc etc. Insisting that this feature support pg_dump is a good way > of making sure that nothing useful will emerge at all. > > Maybe we could replace (some of) psql's describe.c logic with > server-side code, but I'm skeptical that there'd be much win > there either. But we already have some trickier parts in the server, like getting view and function definitions, foreign key definitions, possibly more. > So I don't really buy Álvaro's argument above. It'd be better > to design to some concrete requirement that isn't either of > those. Unfortunately, it's not clear to me that anyone has > a concrete use-case in mind that isn't either of those. IMHO pg_dump is actually a good model for "concrete requirements" as any requirements I can think of - and have needed in th epast - are some subset of pg_dump --schema-only
On Mon, 2025-08-18 at 09:57 -0400, Tom Lane wrote: > Jelte Fennema-Nio <postgres@jeltef.nl> writes: > > > I have wanted this MANY times. I've had this as a PG user: I think > > it's literally the first thing I did when connecting to a Postgres > > server the first time. Coming from MySQL, I wanted to see the exact > > table definition, and there it was as easy as "DESCRIBE some_table". > > You haven't actually defined what "this" is. For starters, do you > really want this output to be included in \d? Seems like one part > or the other of such output would be clutter, so I'd be more minded > to leave \d alone and invent some new command. (By analogy to \sf, > maybe \st and so on?) > > But the real issue is what to print. In the case of a table, should > we also show its indexes? What about foreign keys to or from other > tables? If it's a partitioned table, what about the partitions? > I'm not sure this is as simple as it seems. Right, that is the hard problem. We have had this discussion before, e.g. in https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com Yours, Laurenz Albe
On Mon, Aug 18, 2025 at 10:24 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
On Mon, 18 Aug 2025 at 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You haven't actually defined what "this" is. For starters, do you
> really want this output to be included in \d? Seems like one part
> or the other of such output would be clutter, so I'd be more minded
> to leave \d alone and invent some new command. (By analogy to \sf,
> maybe \st and so on?)
That makes sense, I don't think I had seen the \s ones before. IMO it
would be super useful to have a \s for every type of object that
currently has a \d (but those don't have to be all added in the same
patchset ofcours, starting with tables seems totally sensible)
> But the real issue is what to print.
I think you're making this sound much harder than it actually is. I
think it would be perfect if it had exactly the same info as \d but in
SQL form instead of some "easy to understand by humans form". So:
> should we also show its indexes?
Yes
> What about foreign keys to or from other
> tables?
Yes, both from and to
> If it's a partitioned table, what about the partitions?
Definitely not by default, way too much clutter.
I think having a DESCRIBE keyword is probably not what we want, but
adding a pg_get_tabledef function seems totally reasonable. I even
proposed that at some point[1], but apparently never followed up with
Kirk (cc-ed now). It could even have options for all the questions
that you're asking like, so we'd "just" need to decide on the
defaults:
SELECT pg_get_tabledef('my_table', include_indexes => true,
include_partitions => true)
[1]: https://www.postgresql.org/message-id/CAGECzQRuHBs9gjPbvgabQv8XS3QRU9Ex=nH84S_1=wo4POzBzg@mail.gmail.com
Jelte, you helped me start on something with this. Your code was effectively based on "opening" the reference and loading the type structures.
TBH, I got bogged down on these kinds of issues. First if we do it as "\st" type commands, it is limited to psql.
If we do it elsewhere, it becomes server side code, and incredibly version dependent.
If we do it elsewhere, it becomes server side code, and incredibly version dependent.
And I could not quite get the buy-in (as in this discussion) as to where this belongs, and how to codify the limitations.
FWIW, I wrote a BASH script that generates the file using pg_dump as my workaround.
I was a bit resigned to thinking this is probably best as a SQL based extension that generates the code.
The stickiness was. Can we justify creating and supporting this code in a pg_gettabledef() [Where I believe it belongs]
of none of the clients are using this code. Of course, getting psql to use this code would be trivial for a "\s" once it exists.
but it started feeling like a pretty heavy "sell" to the community.
As I've grown with the community, I understand why it has been missing for so long. What we want from it as a casual
user wanting to quickly see/copy a complete create table command... Is almost orthogonal to what the clients (psql, pg_dump)
are looking for, and why they built them into the client.
I think it needs more "buy-in" and a better "definition" of what it is. Even if we end up with:
pg_get_tabledef() -- User Friendly
pg_get_tabledef_ex() -- Proper for Partitions, Foreign Data Tables, etc.
Thoughts?
Kirk
pg_get_tabledef() -- User Friendly
pg_get_tabledef_ex() -- Proper for Partitions, Foreign Data Tables, etc.
Thoughts?
Kirk