Re: Support logical replication of DDLs

On Wed, Feb 4, 2026 at 1:10 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Tue, Feb 3, 2026 at 1:04 AM Vitaly Davydov <v(dot)davydov(at)postgrespro(dot)ru> wrote:
> >
> > Dear Hackers,
> >
> > I see, that the primary idea in this thread is to capture DDL statements with
> > the use of the utility hook, convert it into a json-like format, save it in the
> > WAL (xl_logical_message) and send it to a peer using the logical replication
> > subsystem (walsender). This approach has a major challenge that was already
> > highlighted: DDL statements may be too complex to convert it into a json string
> > (for example, unlogged/temporary objects in DDL statements).
> >
> > An alternative approach, that was highlighted in the discussion by Dilip Kumar
> > is to decode system catalog changes. It helps to deal with complex DDL
> > statements containing temp objects and to work with a final representation of
> > changes stored in the WAL. Personally, I like this approach.
> >
> > I would share the following idea:
> >
> > 1. Log into the WAL system catalog changes (tuples) suitable for logical
> > decoding (introduce a new wal_level = logical_ddl). I think, not all system
> > catalog changes are needed for decoding (not sure, we have to decode pg_depend
> > changes).
> >
> > 2. Implement a decoder of system catalog changes, that can produce a parse tree
> > using existing structures from parsenodes.h.
> >
> > 3. Based on the decoded parse tree, we can convert it into json or DDL SQL
> > statements in the output plugin. ParseTree to DDL SQL converter can be built-in
> > into the core. Output plugin can decide which converter to use. DDL sql can be
> > directly applied on the replica.
> >
> > 4. Another option is to create json/ddl-sql from system catalog changes without
> > an intermediate representation, but, anyway, when we interpret system catalog
> > changes we have to temporary save current data in some structures. Parsenodes
> > is the already existing solution for it.
>
> IIUC, one of the main challenges of the "deparsing DDL parse tree"
> idea is the maintenance burden. If we implement logic to deparse parse
> nodes back to SQL text, we would end up updating that deparsing code
> every time the underlying parse node definition changes (which happens
> frequently in internal structures). This introduces a substantial and
> ongoing maintenance cost.
>
> In that light, the idea proposed above seems to increase this burden
> rather than alleviate it. It requires implementing not only the DDL
> deparsing (converting parse nodes back to SQL query) at step 3, but
> also the reconstruction of system catalog changes into parse nodes at
> Step 2. This implies maintaining complex mappings in two places
> whenever internal structures change.
>
> I've been researching this area lately and experimenting with several
> approaches (including the idea of decoding system catalog changes).
> While I'm still not sure what is the best approach for DDL
> replication, even with the deparse approach I agree that performing
> this work at decoding time would be better than doing that at
> execution time using event triggers, as it allows all logical decoding
> plugins to handle DDLs consistently.
>
> > The open question: can we unambiguously decode system catalog changes?
>
> While it sounds challenging, it sounds promising. ISTM it is possible
> to infer the executed DDL command from catalog changes. The crucial
> point is how to implement this cleanly and to minimize the maintenance
> burden as much as possible, especially because this code would be used
> only for logical decoding. Instead of reconstructing the full internal
> parse nodes, we might want to construct a stable, abstract
> representation containing only the necessary information for
> replication.

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.

If there are multiple DDLs in the same user SQL, decoding only the
original DDL from the WAL would help. Also we will be able to apply
table/column based filtering appropriately.

--
Best Wishes,
Ashutosh Bapat