MIME-Version: 1.0
References: <OSZPR01MB63102C42A24D59FACF6D9CD6FD4A9@OSZPR01MB6310.jpnprd01.prod.outlook.com>
 <OS3PR01MB6275328379FBE5734B4585619E54A@OS3PR01MB6275.jpnprd01.prod.outlook.com>
 <ZIgf3qBvFoTsMhIc@paquier.xyz> <CAA4eK1J8WOK9VZ9RpQRNRhRYhFOKQCu=GLCu+iBOePNO3JwLbQ@mail.gmail.com>
 <ZIjmGhPWdoJUfjjE@paquier.xyz> <CAJpy0uDaubBHyqPc1k0OysuBYDOVdoUgTWG4jXDCYj-OVSU8hg@mail.gmail.com>
 <CAA4eK1+K8KMsB=+jJO6wDUSt7wF1RiXKtF-HN48nCOEOv-J-3Q@mail.gmail.com>
 <CAJpy0uDLLBYAOzCePYObZ51k1epBU0hef4vbfcujKJprJVsEcQ@mail.gmail.com>
 <CAJpy0uAhLjQZ0Dh0KWDFP8mrnG0rbx99_heavwn8Ke8ZuD-Umg@mail.gmail.com>
 <OS0PR01MB5716A47D23EFAF988475D4A99431A@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <CAD21AoCXCAQ5QyXu9-xs30ViUHtUxQMmf-818d8GX--5pTmZ7g@mail.gmail.com>
 <OS0PR01MB57163E6487EFF7378CB8E17C9438A@OS0PR01MB5716.jpnprd01.prod.outlook.com>
 <B38B7239-92B0-4212-A8BF-FE506C3B0F66@yandex-team.ru> <CAA4eK1JpAcvnfOqF2DQo79pf7Cqp5=3HU5UDwBonWXW4V9ot=w@mail.gmail.com>
 <080f9394-c127-4cef-865e-10f2f997125c@postgrespro.ru>
In-Reply-To: <080f9394-c127-4cef-865e-10f2f997125c@postgrespro.ru>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 4 Feb 2026 16:39:38 +0900
Message-ID: <CAD21AoCzjzr5VqVkpVWkN7V6vrFhac=yXSNMz6rkSE+KOPPU0w@mail.gmail.com>
Subject: Re: Support logical replication of DDLs
To: Vitaly Davydov <v.davydov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAD21AoCzjzr5VqVkpVWkN7V6vrFhac%3DyXSNMz6rkSE%2BKOPPU0w%40mail.gmail.com>
Precedence: bulk

Hi,

On Tue, Feb 3, 2026 at 1:04=E2=80=AFAM Vitaly Davydov <v.davydov@postgrespr=
o.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 replicat=
ion
> subsystem (walsender). This approach has a major challenge that was alrea=
dy
> 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 representatio=
n 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 =3D logical_ddl). I think, not all sy=
stem
> catalog changes are needed for decoding (not sure, we have to decode pg_d=
epend
> changes).
>
> 2. Implement a decoder of system catalog changes, that can produce a pars=
e tree
> using existing structures from parsenodes.h.
>
> 3. Based on the decoded parse tree, we can convert it into json or DDL SQ=
L
> statements in the output plugin. ParseTree to DDL SQL converter can be bu=
ilt-in
> into the core. Output plugin can decide which converter to use. DDL sql c=
an be
> directly applied on the replica.
>
> 4. Another option is to create json/ddl-sql from system catalog changes w=
ithout
> an intermediate representation, but, anyway, when we interpret system cat=
alog
> changes we have to temporary save current data in some structures. Parsen=
odes
> 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,

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