Re: Support logical replication of DDLs

Dear Masahiko, Ashutosh,

Thank you for the feedback!

On 2/4/26 10:39, Masahiko Sawada wrote
 > While it sounds challenging, it sounds promising.

I consider decoding of catalog changes from the WAL promising as well. It hides
all the complexity of DDL queries behind simple catalog changes. CTAS with temp
tables, local context objects (like function variables and arguments) in ddl,
SELECT somefunc() queries with functions where DDL commands are executed - ddl
is pretty diverse and original queries too hard to handle. I see the only
suitable way - to decode WAL changes in system catalog.

The idea to use a stable representation of DDL is reasonable, but creating a new
representation would add more maintenance burden, than reusing some existing
stuff. There is an idea to use existing data structures. A possible simple way
to represent catalog changes as a list inserted/deleted system catalog tuples
logically separated into a number of simple "atomic" DDL commands. Using xid and
cid may help to make a logical separation of system catalog changes into
a number of atomic ddl commands.

In case of system table changes, we may add some handling of different versions.
Changes may be directly applied on a postgresql replica by inserting the tuples
into system tables as is. For other clients, different decoders may be created:
sql ddl, json or something else but it may not be a part of the core. I'm not
sure we can avoid maintenance burden with external clients that require sql,
json or something else. It would be better to help other organizations to create
and maintain such decoders.

The parsenode structures may be another "standard" form to represent DDL, but,
I agree, these structures can be changed and it is hard to validate a complex
node tree. They are too excessive for the purpose to represent decoded simple
DDL commands.

On 2/4/26 14:44, Ashutosh Bapat wrote:
 > Consider an example of ALTER TABLE tab ADD PRIMARY KEY (id) where id
 > is an existing column in table tab. This will add a constraint on the
 > table and also create an index. Thus effectively two DDLs will be
 > executed. If we decode catalog changes we need to make sure that only
 > the original DDL is replicated; else the apply worker downstream will
 > cause an ERROR, stalling the replication. Similar is the case with
 > CREATE TABLE ... AS ... - we should only replicate the CREATE TABLE
 > and let the regular replication handle data replication. I think we
 > need to somehow annotate the WAL containing catalog changes to
 > indicate whether those represent the original DDL or derived DDL and
 > decode only the WAL corresponding to the original DDL.

We may gather all the changed (inserted) system catalog tuples belonging to
a command and then decide which command is represented by these changes. For,
ALTER TABLE ADD PRIMARY KEY we gather inserted tuples in pg_constraints,
pg_index, pg_attribute and then make a decision that these tuples are related
to each other and represent a primary key.

When we decode final changes, it is much easier to handle CTAS because such
statements may be too complex, like CREATE TABLE AS SELECT somefunc(). Only
final changes in the WAL will help us to decode table columns and its types.
Attempt to create a derived ddl sql seems to be very hard to implement.

On 2/4/26 14:44, Ashutosh Bapat wrote:
 > If there are multiple DDLs in the same user SQL, decoding only the
 > original DDL from the WAL would help.

Not sure, I completely got the point. I would say, if we have multiple DDL in
one transaction, each DDL will be attributed with cid. It is not hard to
separate changes in system catalog by its xid and cid. But I'm not sure about
SELECT myfunc(), where myfunc() creates some persistent database objects - cid
may be the same. We may have multiple CREATE TABLE attributed with the same cid.
It would be harder to define DDL command boundaries in this case.

On 2/4/26 14:44, Ashutosh Bapat wrote:
 > Also we will be able to apply table/column based filtering appropriately.
Good point. Not sure how row/column filtering should conceptully work with ddl
replication. Probably, it should affect only data modifications in regular
tables.

With best regards,
Vitaly