Thread

Re: Adding SHOW CREATE TABLE

Kirk Wolak <wolakk@gmail.com> — 2023-06-01T16:57:25Z

On Thu, May 25, 2023 at 9:23 AM Jelte Fennema <postgres@jeltef.nl> wrote:

> On Mon, 22 May 2023 at 13:52, Andrew Dunstan <andrew@dunslane.net> wrote:
> > A performant server side set of functions would be written in C and
> follow the patterns in ruleutils.c.
>
> We have lots of DDL ruleutils in our Citus codebase:
>
> https://github.com/citusdata/citus/blob/main/src/backend/distributed/deparser/citus_ruleutils.c
>
> I'm pretty sure we'd be happy to upstream those if that meant, we
> wouldn't have to update them for every postgres release.
>
> We also have the master_get_table_ddl_events UDF, which does what SHOW
> CREATE TABLE would do.
>

Jelte, this looks promising, although it is a radically different approach
(Querying from it to get the details).

I was just getting ready to write up a bit of  an RFC... On the following
approach...

I have been trying to determine how to "focus" this effort to move it
forward.  Here is where I am at:
1) It should be 100% server side (and psql \st would only work by calling
the server side code, if it was there)
     In reviewing... This simplifies the implementation to the current
version of PG DDL being generated.
     Also, as others have mentioned, it should be C based code, and use
only the internal tables.
2) Since pg_get_{ triggerdef | indexdef | constraintdef } already exists, I
was strongly recommending to not include those.
    -- Although including the inlined constraints would be fine by me
(potentially a boolean to turn it off?)
3) Then focusing the reloptions WITH (%s)

It appears CITUS code handles ALL of this on a cursory review!

The ONLY thing I did not see was "CREATE TEMPORARY " syntax?  If you did
this on a  TEMP table,
does it generate normal table syntax or TEMPORARY TABLE syntax???

So, from my take... This is a great example of solving the problem with
existing "Production Quality" Code...
I like it...

Can this get turned into a Patch?  Were you offering this code up for
others (me?) to pull, and work into a patch?
[If I do the patch, I am not sure it gives you the value of reducing what
CITUS has to maintain.  But it dawns on
me that you might be pushing a much bigger patch...  But I would take that,
as I think there is other value in there]

Others???

Thanks,

Kirk...
PS: It dawned on me that if pg_dump had used server side code to generate
its DDL, its complexity would drop.