Re: Support logical replication of DDLs - Mailing list pgsql-hackers

Hi,

On Tue, Feb 3, 2026 at 1:04 AM Vitaly Davydov <v.davydov@postgrespro.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.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com