Re: Support logical replication of DDLs

Hi,

>If you don't mind, would you like to share the POC or the branch for this work?

The POC patch is attached. It currently supports the following functionalities:
1. Configure either database level or table level DDL replication via
the CREATE PUBLICATION command.

2.Supports replication of DDL of the following types when database
level DDL replication is turned on. Other less common DDL types could
be added later.
T_CreateSchemaStmt
T_CreateStmt
T_CreateForeignTableStmt
T_AlterDomainStmt
T_DefineStmt
T_CompositeTypeStmt
T_CreateEnumStmt
T_CreateRangeStmt
T_AlterEnumStmt
T_ViewStmt
T_CreateFunctionStmt
T_AlterFunctionStmt
T_CreateTrigStmt
T_CreateDomainStmt
T_CreateCastStmt
T_CreateOpClassStmt
T_CreateOpFamilyStmt
T_AlterOpFamilyStmt
T_AlterOperatorStmt
T_AlterTypeStmt
T_GrantStmt
T_AlterCollationStmt
T_AlterTableStmt
T_IndexStmt

3.Supports replication of DDLs of the following types if only table
level DDL replication is turned on.
T_AlterTableStmt
T_IndexStmt

4.Supports seamless DML replication of new tables created via DDL
replication without having to manually running “ALTER SUBSCRIPTION ...
REFRESH PUBLICATION" on the subscriber.

Here is a demo:
source_db=# create publication mypub FOR ALL TABLES with (ddl = ‘database’);

target_db=# create subscription mysub CONNECTION 'dbname=source_db
host=localhost user=myuser port=5432' PUBLICATION mypub;

source_db=#
BEGIN;
CREATE TABLE foo (a int);
CREATE INDEX foo_idx ON foo (a);
ALTER TABLE foo ADD COLUMN b timestamptz;
CREATE FUNCTION foo_ts()
RETURNS trigger AS $$
BEGIN
NEW.b := current_timestamp;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER foo_ts BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE FUNCTION foo_ts();
INSERT INTO foo VALUES (1);
COMMIT;

source_db=# select * from foo;
a | b
---+-------------------------------
1 | 2022-03-15 19:07:53.005215+00
(1 row)

target_db=# select * from foo;
a | b
---+-------------------------------
1 | 2022-03-15 19:07:53.005215+00
(1 row)

Here is the remaining work for this patch:

1. Test and handle corner case DDLs, such as the ones listed in my
first email[1].

2. Investigate less common DDL types (categorized in function
LogLogicalDDLCommand) and decide if and under what conditions they
should be replicated.

3. Determine if we want to include table-level DDL replication. If so,
we need to continue to enhance and test this feature.

I think this will be ready to consider for commit once 1 and 2 are
resolved. I think 3 can be done in the second phase.

If you are reviewing the code, I’ve provided a detailed description of
the patch below:
1. Syntax, pg_publication and pg_dump change:
Allows the user to configure either database level or table level DDL
replication via the CREATE PUBLICATION command as proposed in my first
email. Two new columns are added to the pg_publication catalog to show
the DDL replication levels, test output publication.out is updated
accordingly. pg_dump is also modified to accommodate the
pg_publication catalog change.

2. Logical logging change
a. A new WAL record type xl_logical_ddl_message is introduced to
support logical logging of DDL command. xl_logical_ddl_message is
similar to the existing xl_logical_message for generic message
logging. The reason for not using xl_logical_message directly as
proposed initially is I found out we need to log more information
(such as user role, search path and potentially more in the future)
than just one string, and we don’t want to make too much changes to
the existing xl_logical_message which may break its current consumers.
b. The logging of DDL command string is processed in function
LogLogicalDDLCommand. We categorize DDL command types into three
categories in this function:
1. replicated in database level replication only (such as CREATE
TABLE, CREATE FUNCTION).
2. replicated in database or table level replication depending on the
configuration (such as ALTER TABLE).
3. not supported for replication or pending investigation.

3. Logical decoding and Reorderbuffer change
Supports logical decoding of the new WAL record
xl_logical_ddl_message. This is similar to the logical decoding of
xl_logical_message. Tests for this change are added in the
test_decoding plugin.

4. Integration with pgoutput
Supports sending and receiving the DDL message using the logical
replication wire protocol. A new LogicalRepMsgType is introduced for
this purpose: LOGICAL_REP_MSG_DDLMESSAGE = 'L'.

5. Logical replication worker change
Supports execution of the DDL command in the original user role and
search_path. For any new table created this way, we also set its
srsubstate in the pg_subscription_rel catalog to SUBREL_STATE_INIT, So
that DML replication can progress on this new table without manually
running "ALTER SUBSCRIPTION ... REFRESH PUBLICATION".

6. TAP test
A new TAP test 030_rep_ddl.pl is added. We mainly focused on testing
the happy path of database level replication so far. Corner case DDLs
and table level DDL replication are still to be carefully tested.

>> However, there are still many edge cases to sort out because not every
>> DDL statement can/should be replicated. Some of these include:

>> 3. CREATE TABLE AS and SELECT INTO, For example:
>>
>> CREATE TABLE foo AS
>> SELECT field_1, field_2 FROM bar;
>>
>> There are a few issues that can occur here. For one, it’s possible
>> that table bar doesn't exist on the subscriber. Even if “bar” does
>> exist, it may not be fully up-to-date with the publisher, which would
>> cause a data mismatch on “foo” between the publisher and subscriber.

>In such cases why don't we just log the table creation WAL for DDL
>instead of a complete statement which creates the table and inserts
>the tuple? Because we are already WAL logging individual inserts and
>once you make sure of replicating the table creation I think the exact
>data insertion on the subscriber side will be taken care of by the
>insert WALs no?

The table creation WAL and table insert WAL are available. The tricky
part is how do we break down this command into two parts (a normal
CREATE TABLE followed by insertions) either from the parsetree or the
WALs. I’ll have to dig more on this.

I look forward to your feedback.

Regards,
Zheng

[1] https://www.postgresql.org/message-id/CAAD30U+pVmfKwUKy8cbZOnUXyguJ-uBNejwD75Kyo=OjdQGJ9g@mail.gmail.com

On Sun, Mar 13, 2022 at 7:35 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Mon, Feb 21, 2022 at 9:43 PM Zheng Li <zhengli10(at)gmail(dot)com> wrote:
> >
> > Hello,
> >
> > One of the most frequently requested improvements from our customers
> > is to reduce downtime associated with software updates (both major and
> > minor versions). To do this, we have reviewed potential contributions to
> > improving logical replication.
> >
> > I’m working on a patch to support logical replication of data
> > definition language statements (DDLs). This is a useful feature when a
> > database in logical replication has lots of tables, functions and
> > other objects that change over time, such as in online cross major
> > version upgrade.
>
> +1
>
> > I put together a prototype that replicates DDLs using the generic
> > messages for logical decoding. The idea is to log the candidate DDL
> > string in ProcessUtilitySlow() using LogLogicalMessge() with a new
> > flag in WAL record type xl_logical_message indicating it’s a DDL
> > message. The xl_logical_message record is decoded and sent to the
> > subscriber via pgoutput. The logical replication worker process is
> > dispatched for this new DDL message type and executes the command
> > accordingly.
>
> If you don't mind, would you like to share the POC or the branch for this work?
>
> > However, there are still many edge cases to sort out because not every
> > DDL statement can/should be replicated. Some of these include:
>
> > 3. CREATE TABLE AS and SELECT INTO, For example:
> >
> > CREATE TABLE foo AS
> > SELECT field_1, field_2 FROM bar;
> >
> > There are a few issues that can occur here. For one, it’s possible
> > that table bar doesn't exist on the subscriber. Even if “bar” does
> > exist, it may not be fully up-to-date with the publisher, which would
> > cause a data mismatch on “foo” between the publisher and subscriber.
>
> In such cases why don't we just log the table creation WAL for DDL
> instead of a complete statement which creates the table and inserts
> the tuple? Because we are already WAL logging individual inserts and
> once you make sure of replicating the table creation I think the exact
> data insertion on the subscriber side will be taken care of by the
> insert WALs no?
>
>
> --
> Regards,
> Dilip Kumar
> EnterpriseDB: http://www.enterprisedb.com