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

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.

--
With Regards,
Amit Kapila.