Re: Adding SHOW CREATE TABLE - Mailing list pgsql-hackers

From Kirk Wolak
Subject Re: Adding SHOW CREATE TABLE
Date
Msg-id CACLU5mS5TqEhPCnEFEQNcXxSZg1tL9Te4gLQ4USzrmA86utx-A@mail.gmail.com
Whole thread Raw
In response to Re: Adding SHOW CREATE TABLE  (Stephen Frost <sfrost@snowman.net>)
List pgsql-hackers
On Sat, May 20, 2023 at 2:33 PM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,

On Sat, May 20, 2023 at 13:32 David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sat, May 20, 2023 at 10:26 AM Stephen Frost <sfrost@snowman.net> wrote:
> A server function can be conveniently called from any client code.

Clearly any client using libpq can conveniently call code which is in
libpq.

Clearly there are clients that don't use libpq.  JDBC comes to mind.

Indeed … as I mentioned up-thread already.

Are we saying that we want this to be available server side, and largely duplicated, specifically to cater to non-libpq users?  I’ll put out there, again, the idea that perhaps we put it into the common library then and make it available via both libpq and as a server side function ..?

We also have similar code in postgres_fdw.. ideally, imv anyway, we’d not end up with three copies of it.

Thanks,

Stephen

First, as the person chasing this down, and a JDBC user, I really would prefer pg_get_tabledef() as Laurenz mentioned.

Next, I have reviewed all 3 implementations (pg_dump [most appropriate], psql \d (very similar), and the FDW which is "way off",
since it actually focuses on "CREATE FOREIGN TABLE" exclusively, and already fails to handle many pieces not required in
creating a "real" table, as it creates a "reflection" of table.

I am using pg_dump as my source of truth.  But I noticed it does not create "TEMPORARY" tables with that syntax.
[Leading to a question on mutating the pg_temp_# schema name back to pg_temp. or just stripping it, in favor of the TEMPORARY]

I was surprised to see ~ 2,000 lines of code in the FDW and in psql...  Whereas pg_dump is shorter because it gets more detailed 
table information in a structure passed in.

I would love to leverage existing code, in the end.  But I want to take my time on this, and become intimate with the details.
Each of the above 3 approaches have different goals.  And I would prefer the lowest risk:reward possible, and the least expensive
maintenance.  Having it run server side hides a ton of details, and as Tom pointed out, obviates DDL versioning control for other server versions.

Thanks for the references to the old discussions.  I have queued them up to review.

Kirk...


pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: Naming of gss_accept_deleg
Next
From: Peter Eisentraut
Date:
Subject: Re: createuser --memeber and PG 16