Re: Support logical replication of DDLs, take2

On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Mon, Feb 23, 2026 at 5:21 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> >
> > One idea I'm experimenting with is that we define an abstract data
> > type that can represent a DDL (like CollectedCommand) and write it to
> > a new WAL record so that logical decoding processes it. For CREATE
> > DDLs, we can use pg_get_xxx_def() function while using a historical
> > snapshot to get the DDLs. We would need to implement the codes to
> > generate DROP and ALTER DDLs from the data. I believe DROP DDLs would
> > not be hard. For ALTER DDLs, we would incur the initial implementation
> > costs, but we would not change these codes often.
> >
>
> DDL support for logical replication is one of the biggest missing
> pieces in logical replication. I'd like to resume this work for PG20.

Thanks for working on this.

> We made a lot of effort on this feature through 2022 and 2023, but the
> development is currently inactive. The last patch was submitted on Jul
> 18, 2023. I've reviewed the previous patches and discussions, and I
> would like to summarize how DDL replication was implemented, the main
> reasons it stalled, and propose an alternative design to address those
> problems.
>
> The overall idea of the previous patch set was to implement DDL
> deparsing and utilize it for DDL replication. It converted a parse
> tree into a JSON string. For instance, if a user executes "DROP TABLE
> t1", the deparser generates from its parse tree:
>
> {DROPSTMT :objects (("t1")) :removeType 41 :behavior 0 :missing_ok
> false :concurrent false}
>
> to:
>
> {"fmt": "DROP TABLE %{objidentity}s", "objidentity": "public.t1"}
>
> This JSON string is self-documenting, meaning someone who gets it can
> easily reconstruct the original DDL with schema-qualified object
> names. In a dedicated event trigger for logical replication, we
> deparsed the parse tree of a DDL, wrote it into a WAL record, and then
> the logical decoding processed it similarly to DML changes.

I think there was also a discussion on whether to use JSON vs the
existing infrastructure of converting nodes to strings. Although the
JSON is standard format and might provide more flexibility using
existing format avoid extra maintence burden.

> While there are several benefits to the JSON data approach mentioned
> in the wiki [1] -- most notably the flexibility to easily remap
> schemas (e.g., mapping "schema A" on the publisher to "schema B" on
> the subscriber) -- there was a major concern: the huge maintenance
> burden. We would need to maintain the JSON serialization code whenever
> creating or modifying parse nodes, regardless of whether the changes
> were related to DDL replication. IIUC, this was the primary reason the
> feature didn't cross the finish line.

Do you mean that modifying any existing parse node requires changing
the JSON serialization code? But why do we need to do that if that's
not related to DDL? I don't think I understood this point clearly,
can you explain it or point me to the discussion thread?

> Additionally, I think there is another design issue: it is not
> output-plugin agnostic. Since the deparsed DDL was written by a
> logical-replication-specific event trigger, third-party logical
> decoding plugins cannot easily detect DDL events. Ideally, we should
> write DDL information into a WAL record natively when
> wal_level='logical' (or additionally when a GUC enables DDL events
> WAL-logging) so that all decoding plugins can detect them. This also
> allows us to test DDL logical decoding with test_decoding without
> setting up a full logical replication subscription.

Yeah, that's a valid point, but I think we could separate the event
trigger logic from the decoding plugins so that it's available for any
other output plugins?

> To address these two points, I'd like to propose an alternative
> approach: we introduce a new data type, say DDLCommand, that is
> self-contained to represent a DDL (like CollectedCommand), and don't
> rely on event triggers. It would have the command type (and subtype if
> required), the OIDs of the target object and its namespace, and the
> OID of the user who executed the DDL. We write it to a new WAL record
> at appropriate places during DDL execution, and the logical decoding
> layer passes the data to output plugins. That way, any logical
> decoding plugin can detect DDL changes, and it's up to the plugins how
> to decode the DDL information.

Interesting. I'm trying to figure out exactly when we plan to
construct this new DDLCommand data type? And how would we prepare
this, by converting the internal DDL structures or from parsetree?

> In pgoutput, for CREATE DDLs, we can use the pg_get_xxx_ddl()
> functions while using a historical snapshot to get the DDLs, saving
> maintenance costs. We would still need to implement the code to
> generate DROP and ALTER DDLs from the data. I believe DROP DDLs would
> not be hard. For ALTER DDLs, we would incur an initial implementation
> cost, but we would not need to change this code often. We can
> implement the DDL generation code in a way that improves ddlutils.c.
>
> Also, because DDLCommand is separated from parse nodes, we only need
> to change the DDL deparse/replication code when it is actually needed.
> Additionally, this approach would eliminate the code around the
> two-step process (using DCT_TableDropStart and DCT_TableDropEnd) for
> DROP TABLE. While it would miss the flexibility benefits that the JSON
> deparsing approach has, I guess it would not be very hard to implement
> the mapping in the deparse layer even without the JSON data.
>
> Regarding the publication syntax, previous patches proposed:
>
> CREATE PUBLICATION pub FOR ALL TABLES WITH (ddl = 'table');
>
> While simple, it doesn't support critical enterprise use cases (e.g.,
> DWH environments) where users want to replicate CREATE and ALTER, but
> explicitly filter out DROP TABLE to prevent accidental data loss. We
> should consider introducing publish_ddl options to filter operations:
>
> CREATE PUBLICATION pub FOR ALL TABLES WITH (publish_ddl = 'create, alter');

+1

--
Regards,
Dilip Kumar
Google