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.
