Horton

Re: Support logical replication of DDLs

Zheng Li <zhengli10@gmail.com>

View thread
From: Zheng Li <zhengli10@gmail.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, rajesh singarapu <rajesh.rs0541@gmail.com>, Ajin Cherian <itsajin@gmail.com>, Hou, Zhijie/侯 志杰 <houzj.fnst@fujitsu.com>
Date: 2022-05-10T15:48:01Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add a run_as_owner option to subscriptions.

  2. Refactor pgoutput_change().

  3. Print the correct aliases for DML target tables in ruleutils.

  4. Fix object identity string for transforms

  5. Add grantable MAINTAIN privilege and pg_maintain role.

  6. Get rid of recursion-marker values in enum AlterTableType

  7. Release cache tuple when no longer needed

  8. Empty search_path in logical replication apply worker and walsender.

  9. Refactor format_type APIs to be more modular

  10. Use wrappers of PG_DETOAST_DATUM_PACKED() more.

> > > I agree that it adds to our maintainability effort, like every time we
> > > enhance any DDL or add a new DDL that needs to be replicated, we
> > > probably need to change the deparsing code. But OTOH this approach
> > > seems to provide better flexibility. So, in the long run, maybe the
> > > effort is worthwhile. I am not completely sure at this stage which
> > > approach is better but I thought it is worth evaluating this approach
> > > as Alvaro and Robert seem to prefer this idea.
> >
> > +1, IMHO with deparsing logic it would be easy to handle the mixed DDL
> > commands like ALTER TABLE REWRITE.  But the only thing is that we will
> > have to write the deparsing code for all the utility commands so there
> > will be a huge amount of new code to maintain.
>
> Actually, the largest stumbling block on this, IMO, is having a good
> mechanism to verify that all DDLs are supported.  The deparsing code
> itself is typically not very bad, as long as we have a sure way to twist
> every contributor's hand into writing it, which is what an automated
> test mechanism would give us.
>
> The code in test_ddl_deparse is a pretty lame start, not nearly good
> enough by a thousand miles.  My real intention was to have a test
> harness that would first run a special SQL script to install DDL
> capture, then run all the regular src/test/regress scripts, and then at
> the end ensure that all the DDL scripts were properly reproduced -- for
> example transmit them to another database, replay them there, and dump
> both databases and compare them.  However, there were challenges which I
> no longer remember and we were unable to complete this, and we are where
> we are.
>
> Thanks for rebasing that old code, BTW.

I agree that deparsing could be a very useful utility on its own. Not
only for SQL command replication between PostgreSQL servers, but also
potentially feasible for SQL command replication between PotgreSQL and
other database systems. For example, one could assemble the json
representation of the SQL parse tree back to a SQL command that can be
run in MySQL. But that requires different assembling rules and code
for different database systems. If we're envisioning this kind of
flexibility that the deparsing utility can offer, then I think it's
better to develop the deparsing utility as an extension itself.

Regards,
Zheng