On Wed, Jun 21, 2023 at 6:38 PM Jelte Fennema <postg...@jeltef.nl> wrote: > > (to be clear I only skimmed the end of this thread and did not look at > all the previous messages) > > I took a quick look at the first patch (about deparsing table ddl) and > it seems like this would also be very useful for a SHOW CREATE TABLE, > like command. Which was suggested in this thread: > https://www.postgresql.org/message-id/flat/CAFEN2wxsDSSuOvrU03CE33ZphVLqtyh9viPp6huODCDx2UQkYA%40mail.gmail.com > > On that thread I sent a patch with Citus its CREATE TABLE deparsing as > starting point. It looks like this thread went quite a different route > with some JSON intermediary representation. Still it might be useful > to look at the patch with Citus its logic for some inspiration/copying > things. I re-attached that patch here for ease of finding it.
Thank You for attaching the patch for our ease. We rely on JSONB because of the flexibility it provides. It is easy to be parsed/processed/transformed arbitrarily by the subscriber using generic rules. It should be trivial to use a JSON tool to change schema A to schema B in any arbitrary DDL command, and produce another working DDL command without having to know how to write that command specifically. It helps in splitting commands as well. As an example, we may need to split commands like "ALTER TABLE foo ADD COLUMN bar double precision DEFAULT random();" so that random() have consistent values on publisher and subscriber. It would be convenient to break commands via deparsing approach rather than via plain string. Above being said, show table command can be implemented from ddl deparse code using below steps: 1) Deparsing to create JSONB format using deparsing API ddl_deparse_to_json. 2) Expanding it back to DDL command using expansion API ddl_deparse_expand_command. But these APIs rely on getting information from parse-tree. This is because we need to construct complete DDL string and info like "IF NOT EXISTS", "CONCURRENTLY" etc can not be obtained from pg_catalog. Even if we think of getting rid of parsetree, it may hit the performance, as it is more efficient for us to get info from parse-tree instead of doing catalog-access for everything. We will try to review your patch to see if there is anything which we can adopt without losing performance and flexibility. Meanwhile if you have any suggestions on our patch which can make your work simpler, please do let us know. We can review that. thanks Shveta