Re: Support logical replication of global object commands - Mailing list pgsql-hackers

On Fri, Aug 12, 2022 at 5:41 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Tue, Aug 9, 2022 at 1:31 AM Zheng Li <zhengli10@gmail.com> wrote:
> >
> > Hello,
> >
> > Logical replication of DDL commands support is being worked on in [1].
> > However, global object commands are quite different from other
> > non-global object DDL commands and need to be handled differently. For
> > example, global object commands include ROLE statements, DATABASE
> > statements, TABLESPACE statements and a subset of GRANT/REVOKE
> > statements if the object being modified is a global object. These
> > commands are different from other DDL commands in that:
> >
> > 1. Global object commands can be executed in any database.
> > 2. Global objects are not schema qualified.
> > 3. Global object commands are not captured by event triggers.
> >
> > I’ve put together a prototype to support logical replication of global
> > object commands in the attached patch. This patch builds on the DDL
> > replication patch from ZJ in [2] and must be applied on top of it.
> > Here is a list of global object commands that the patch replicate, you
> > can find more details in function LogGlobalObjectCommand:
> >
> > /* ROLE statements */
> > CreateRoleStmt
> > AlterRoleStmt
> > AlterRoleSetStmt
> > DropRoleStmt
> > ReassignOwnedStmt
> > GrantRoleStmt
> >
> > /* Database statements */
> > CreatedbStmt
> > AlterDatabaseStmt
> > AlterDatabaseRefreshCollStmt
> > AlterDatabaseSetStmt
> > DropdbStmt
> >
> > /* TableSpace statements */
> > CreateTableSpaceStmt
> > DropTableSpaceStmt
> > AlterTableSpaceOptionsStmt
> >
> > /* GrantStmt and RevokeStmt if objtype is a global object determined
> > by EventTriggerSupportsObjectType() */
> > GrantStmt
> > RevokeStmt
> >
> > The idea with this patch is to support global objects commands
> > replication by WAL logging the command using the same function for DDL
> > logging - LogLogicalDDLMessage towards the end of
> > standard_ProcessUtility. Because global objects are not schema
> > qualified, we can skip the deparser invocation and directly log the
> > original command string for replay on the subscriber.
> >
> > A key problem to address is that global objects can become
> > inconsistent between the publisher and the subscriber if a command
> > modifying the global object gets executed in a database (on the source
> > side) that doesn't replicate the global object commands. I think we
> > can work on the following two aspects in order to avoid such
> > inconsistency:
> >
> > 1. Introduce a publication option for global object commands
> > replication and document that logical replication of global object
> > commands is preferred to be enabled on all databases. Otherwise
> > inconsistency can happen if a command modifies the global object in a
> > database that doesn't replicate global object commands.
> >
> > For example, we could introduce the following publication option
> > publish_global_object_command :
> > CREATE PUBLICATION mypub
> > FOR ALL TABLES
> > WITH (publish = 'insert, delete, update', publish_global_object_command = true);
> >
>
> Tying global objects with FOR ALL TABLES seems odd to me. One possible
> idea could be to introduce publications FOR ALL OBJECTS. However, I am
> not completely sure whether tying global objects with
> database-specific publications is what users would expect but OTOH I
> don't have any better ideas here.
>

Can we think of relying to send WAL of such DDLs just based on whether
there is a corresponding publication (ex. publication of ALL OBJECTS)?
I mean avoid database-specific filtering in decoding for such DDL
commands but not sure how much better it is than the current proposal?
The other idea that occurred to me is to have separate event triggers
for global objects that we can store in the shared catalog but again
it is not clear how to specify the corresponding function as functions
are database specific.

Thoughts?

--
With Regards,
Amit Kapila.