Message-ID: <a702a699-bd10-cc89-b6be-03dfa749609d@joeconway.com>
Date: Sat, 23 Jul 2022 11:33:43 -0400
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.11.0
Subject: Re: Support logical replication of DDLs
Content-Language: en-US
To: Zheng Li <zhengli10@gmail.com>,
 "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
 Masahiko Sawada <sawada.mshk@gmail.com>, Japin Li <japinli@hotmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>, Dilip Kumar
 <dilipbalaut@gmail.com>, rajesh singarapu <rajesh.rs0541@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Peter Smith <smithpb2250@gmail.com>
References: 
 <OS0PR01MB5716D3B79A607B547F2AB90D94819@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CAHut+Ps9QyGw4KRFP50vRnB0tJKbB_TS1E7rZ_-+pc2Nvwv_zw@mail.gmail.com>
 <OS0PR01MB571642FDCB049FC00EBDA5EC94869@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <OS0PR01MB5716FCCEF8009E4464042476948E9@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CAAD30ULBUSHG4n-_zZ7yLGPri-LkS0o+vp64WCKj-mFcF27ekg@mail.gmail.com>
From: Joe Conway <mail@joeconway.com>
In-Reply-To: 
 <CAAD30ULBUSHG4n-_zZ7yLGPri-LkS0o+vp64WCKj-mFcF27ekg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/a702a699-bd10-cc89-b6be-03dfa749609d%40joeconway.com>
Precedence: bulk

On 7/22/22 17:18, Zheng Li wrote:
> Here is a patch that supports replication of global object commands,
> these include ROLE statements, database statements and tablespace statements.
> The patch should be applied on top of the v13 DDL replication patch set that
> ZJ Hou sent in the previous email.
> 
> Global objects commands are different from other DDL commands in
> that:
> 1. Global objects commands are allowed to be executed in any databases
> 2. Global objects are not schema qualified
> 2. Global objects commands are not captured by event triggers
> 
> This patch supports 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.

I have not looked at the patch but +1 for the general concept. Seems 
like you might want to start a separate thread, perhaps after the 
currently running commitfest is over.

> A key problem is global objects can get inconsistent between the
> publisher and the subscriber if a command changes the global object
> in a database (on the source side) which doesn't configure logical replication.
> I think we can work on the following directions in order to avoid such
> inconsistency:
> 
> 1. Introduce a publication option for global objects command replication
> and document that logical replication of global objects commands is preferred
> to be configured on all databases. Otherwise inconsistency can happen
> if a command changes the global object in a database which doesn't configure
> logical replication.
> 
> 2. Introduce database cluster level logical replication to avoid
> such inconsistency, this is especially handy when there is a large
> number of databases to configure for logical replication.

I would strongly favor #2, although I admittedly have no idea what 
complexities it adds.

-- 
Joe Conway
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com