Re: Support logical replication of DDLs

On Wed, Jun 21, 2023 at 6:38 PM Jelte Fennema <postgres(at)jeltef(dot)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