Subject: Re: Support logical replication of DDLs

From: Vitaly Davydov <[email-masked]>
Date: Mon, 9 Feb 2026 13:42:56 +0300

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

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 23 Feb 2026 17:21:50 -0800

On Mon, Feb 9, 2026 at 2:42 AM Vitaly Davydov <[email-masked]> wrote:
> into system tables as is.

We cannot apply the changes to system catalogs made in the publisher
to the subscriber as is because we don't support system catalog
compatibility across major versions.

> to each other and represent a primary key.

I think we need to check not only what system catalogs were changed
but also how system catalog tuples were changed in order to determine
what is the original DDL query. For example, both ALTER TABLE ... SET
STORAGE EXTERNAL and ALTER TABLE ... SET COMPRESSION pglz change the
corresponding pg_attribute tuple but we need to generate different DDL
queries from them.

Also, we need to consider whether we need to replicate the information
that is not present in WAL records of system catalog changes, e.g.,
CASCADE or RESTRICT of DROP TABLE. The subscriber might have a view
referring to the table being dropped on the publisher. If we send only
a delete change of pg_class to delete the table, applying the delete
change on the subscriber might fail due to depending objects.

Similarly, witn the idea of sending DDL queries to subscribers, we
would need to modify the executed DDL query to reflect the publication
definition. For example, while the publisher has table T1, T2, and T3,
the subscriber has only T1, and T1 is being replicated from the
publisher to the subscriber. If user executes DROP TABLE T1, T2, T3,
the logical replication should send DROP TABLE T2 (or equivalent
information) instead of the original DDL query as it is. Also, a
similar consideration would be required for publications with
publish_via_partition_root being true.

> Attempt to create a derived ddl sql seems to be very hard to implement.

There might be cases where we might want to send DDLs instead of
system catalog changes. Taking an example from your recent mail, ALTER
TABLE t ALTER COLUMN x TYPE double precision; command writes
pg_attribute changes with inserting rewrote tuples. If we improve many
ALTER TABLE sub commands so that we can execute them with a lesser
lock level or without table writes, it would be better to delegate how
to execute changing the column type to the subscriber that might be a
newer version.

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.

Regards,

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

---

From: Masahiko Sawada <[email-masked]>
Date: Mon, 20 Apr 2026 16:14:32 -0700

Hi,

> 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.

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.

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.

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.

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.

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');

I have implemented the basic idea with the above changes and it seems
to work well, though the patch is not yet ready to share. I'd like to
resume the discussion to move this project forward. My initial MVP
goal is to support CREATE/ALTER/DROP TABLE, which covers the vast
majority of use cases, and incrementally extend support for other
object types later.

FYI I've experimented with auto-generation approaches too. For
instance, gen_node_support.pl generates C code that converts parse
nodes to the corresponding text representations. Or
gen_node_support.pl generates C code that makes all objects in the
given SQL query text fully-schema qualified. While these ideas are
promising they didn't help reduce the maintenance burden much as the
parse node definitions are already complex and vary on nodes much.

Thank you for taking the time to read through this long email.

Regards,

[1] https://wiki.postgresql.org/wiki/Logical_replication_of_DDLs#JSONB_Benefits

---

From: Dilip Kumar <[email-masked]>
Date: Mon, 27 Apr 2026 11:44:55 +0530

> pieces in logical replication. I'd like to resume this work for PG20.

Thanks for working on this.

> 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.

> 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?

> 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 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?

> CREATE PUBLICATION pub FOR ALL TABLES WITH (publish_ddl = 'create, alter');

+1

-- 
Regards,
Dilip Kumar
Google

---

From: Amit Kapila <[email-masked]>
Date: Tue, 28 Apr 2026 12:02:17 +0530

On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <[email-masked]> wrote:
> burden.

Yes, there will be a maintenance cost of JSON-based deparsing
approach. But note that multiple senior people (Alvaro Herrera, Robert
Haas) [1] seems to favor that approach. So, I am not sure we can
conclude to abandon that approach without those people or some other
senior people agreeing to abandon it. To be clear, I am not against
considering a new/different approach for DDL replication but just that
it is not clear that old/existing approach can be ruled out without
more discussion on it,

 We would need to maintain the JSON serialization code whenever
> decoding plugins cannot easily detect DDL events.
>

Why RmgrId like RM_LOGICALDDLMSG_ID and XLOG_LOGICAL_DDL_MESSAGE wal
info is not sufficient for this? Decoder will add a message like
REORDER_BUFFER_CHANGE_DDL which can be used to detect DDL message, no?

> Ideally, we should

Possible but the point was flexibility and ease with which users can
implement mapping with JSON approach.

> parse node definitions are already complex and vary on nodes much.

Yeah, this is my recollection of a previous attempt for
auto_generating the deparsing code.

[1]: https://www.postgresql.org/message-id/CA%2BTgmoauXRQ3yDZNGTzXv_m1kdUnH1Ww%2BhwKmKUSjtyBh0Em2Q%40mail.gmail.com
-- 
With Regards,
Amit Kapila.

---

From: Amit Kapila <[email-masked]>
Date: Tue, 28 Apr 2026 12:27:17 +0530

> existing format avoid extra maintence burden.

Yes, the discussion related to node-to-string and the pros and cons of
the deparse approach are detailed in email [1]. I think some of these
points could be also related to the new approach as well.

[1] - https://www.postgresql.org/message-id/OS0PR01MB571684CBF660D05B63B4412C94AB9%40OS0PR01MB5716.jpnprd01.prod.outlook.com

-- 
With Regards,
Amit Kapila.

---

From: Hannu Krosing <[email-masked]>
Date: Tue, 28 Apr 2026 21:55:07 +0200

My high-level understanding is, that we should first clearly answer these
two questions

1. What does Logical Decodng infrastructure make available to the decoding
plugin call-backs

2. What the callbacks themselves do in case of DDL

My answer to the 1. is "everything, as it should be the plugin that decides
what it needs". It does not mean that we should always prepack everything
with special logical decoding structures, but there should be a way to get
at anything that is available in the WAL at least. The result is in WAL,
and for DDL it is also in the system tables themselves, in proper
time-travel way.

For 2. I would prefer to "deparse" the DDL from actual system tables at
that snapshot. In logical decoding the system tables are special in that we
keep the actual table content and have real time travel capabilities on
them. This should allow us to use the code we already have in pg_dump for
extracting the "status quo DDL" meaning the DDL for creating everything
from scratch. The main thing missing is DDL for ALTER and DROP which would
need to be added. But that too should be in the plugin, not in the DDL side
.

On Tue, Apr 28, 2026 at 8:57 AM Amit Kapila <[email-masked]> wrote:

> [1] -
> https://www.postgresql.org/message-id/OS0PR01MB571684CBF660D05B63B4412C94AB9%40OS0PR01MB5716.jpnprd01.prod.outlook.com
>
> --
> With Regards,
> Amit Kapila.
>
>
>

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 28 Apr 2026 14:38:35 -0700

> existing format avoid extra maintence burden.

Right, but since the string representation of nodes are major version
dependent, we cannot directly send them to subscribers that might be
different major versions.

> can you explain it or point me to the discussion thread?

IIUC, many parse nodes need to support JSON serialization even if we
only want to support CREATE/ALTER/DROP TABLE commands. This is because
these commands can include expressions (e.g., in DEFAULT clauses or
CHECK constraints), function calls, and column references. We would
need to recursively deparse the entire expression tree.

> trigger logic from the decoding plugins so that it's available for any

Yes, it's possible. But I'm concerned that the operations users (or
plugins) would need to enable DDL events would be quite different from
capturing DMLs. I'm not sure if it's a good user experience or plugin
developer experience that additional steps are required to capture DDL
events while changes of  INSERT/UPDATE/DELETE/TRUNCATE are passed from
the logical decoding by default.

> this, by converting the internal DDL structures or from parsetree?

I'm still unsure what is the best approach but I think we can
construct the DDLCommand data from the internal DDL structures, and
each DDL command can call the function to write the DDL command
information to a WAL record.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 28 Apr 2026 14:48:31 -0700

> it is not clear that old/existing approach can be ruled out without

Thank you for pointing it out. Just to be clear, IIUC what they liked
was to use JSON string representation of DDLs, but not JSON string
representation of DDLs that are deparsed from parse nodes, no? I think
if we do versioning the DDL commands sent to subscribers, we can
support JSON-based DDLs in later versions.

> REORDER_BUFFER_CHANGE_DDL which can be used to detect DDL message, no?

Right, but I'm not sure this is a good developer experience that
additional steps are required to capture DDL events for other plugins
while changes of  INSERT/UPDATE/DELETE/TRUNCATE are passed from the
logical decoding by default.

Regards,

---

From: Dilip Kumar <[email-masked]>
Date: Wed, 29 Apr 2026 09:09:07 +0530

On Wed, Apr 29, 2026 at 1:25 AM Hannu Krosing <[email-masked]> wrote:
>
> My high-level understanding is, that we should first clearly answer these two questions
>
> 1. What does Logical Decodng infrastructure make available to the decoding plugin call-backs
>

Yeah that makes sense, Hannu.

> My answer to the 1. is "everything, as it should be the plugin that decides what it needs". It does not mean that we should always prepack everything with special logical decoding structures, but there should be a way to get at anything that is available in the WAL at least. The result is in WAL, and for DDL it is also in the system tables themselves, in proper time-travel way.
>
> For 2. I would prefer to "deparse" the DDL from actual system tables at that snapshot. In logical decoding the system tables are special in that we keep the actual table content and have real time travel capabilities on them. This should allow us to use the code we already have in pg_dump for extracting the "status quo DDL" meaning the DDL for creating everything from scratch. The main thing missing is DDL for ALTER and DROP which would need to be added. But that too should be in the plugin, not in the DDL side .

I am trying to understand your idea. If we are trying to deparse from
an actual system table using a snapshot, why don't we just use the
WAL? I mean, the WAL should contain the actual catalog modifications
it has made.  Although converting the catalog changes into a deparse
representation of the DDL could be complex no?  Another question is
what we would do with those deparsed representations: will we convert
them to SQL on the subscriber and execute, or do something else?

-- 
Regards,
Dilip Kumar
Google

---

From: Hannu Krosing <[email-masked]>
Date: Wed, 29 Apr 2026 10:07:04 +0200

On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <[email-masked]> wrote:

> it has made.

We have the full data in the catalog and we would likely need catalog
queries for any change, even when de-parsing the tree.

And we should not add the extra load on the original DDL side, just as
we don't for DML.

At most we could just serialize the statement tree into the WAL,
though even that may be an overkill if we can get the change from
existing records.

- insert new row in pg_class --> extract the CREATE TABLE (or INDEX, or ...)
- update row in pg_class or insert, update or delete a row in
pg_attribute --> extract ALTER TABLE
  - except when it just updates relfilenod --> extract TRUNCATE
- delete row in pg_class --> DROP TABLE
- dml on pg_constraint --> ALTER TABLE

... etc

> Although converting the catalog changes into a deparse
> representation of the DDL could be complex no?

Both de-parsing the tree and converting the catalog change could be complex.
The advantage of using the catalog is that we already have decades of
experience doing this via pg_dump.

> Another question is

Current pg_dump approach is logically equivalent to "doing it on the
subscriber", pg_dump is designed to dump schemas from all older
database versions in format that is compatible with the version the
pg_dump is written for.

This brings us back to the uncomfortable discussion of needing to
back-port some changes to older versions contrary to general
PostgreSQL development principles of adding new features to only the
latest version.

Or we could enable exporting the catalog snapshot from logical
replication stream so that subscriber could use that snapshot in a
"callback connection: to extract the catalog state at that snapshot

---

From: Dilip Kumar <[email-masked]>
Date: Wed, 29 Apr 2026 16:59:26 +0530

> database versions in format that is compatible with the version the

IIUC, you're suggesting a pg_dump-style mechanism for SQL generation
from the catalog. My concern is that pg_dump is snapshot-based, while
decoding is incremental.  So how to we generate SQL from the catalog
for incremental changes(like an ALTER TABLE...SET DATA TYPE)?

-- 
Regards,
Dilip Kumar
Google

---

From: Andres Freund <[email-masked]>
Date: Wed, 29 Apr 2026 08:10:17 -0400

Hi,

> And we should not add the extra load on the original DDL side, just as

That can't be a relevant cost compared to everything else.

> - dml on pg_constraint --> ALTER TABLE

That doesn't work in the general case, think of
ALTER TABLE ... ALTER COLUMN ... TYPE foo USING (...)

There's a big difference between USING(foo::int8) and USING (pg_size_bytes(foo))
but it's nowhere visible in the WAL.

Greetings,

Andres Freund

---

From: Amit Kapila <[email-masked]>
Date: Thu, 30 Apr 2026 10:14:14 +0530

On Wed, Apr 29, 2026 at 3:19 AM Masahiko Sawada <[email-masked]> wrote:
> representation of DDLs that are deparsed from parse nodes, no?
>

As per my understanding, we built deparsing stuff with a goal of
supporting DDL replication and Alvaro was the original author of that
work, see [1]. The benefit it provides flexibility in terms of
filtering by decoding plugin, if any, or changing the DDL (like
schema-mapping) during apply. It is not clear to me if we can achive
similar level of flexibility with other approach.

> logical decoding by default.

Yes, there could probably be additional steps for plugins but they
must be doing a few things already which are defined at publication
level like column lists, row filtering, something related to RI, etc.
To reduce the plugin work, one naive idea is to let the event triggers
be registered at first logical slot creation or may be at init time of
plugin.

[1]: https://www.postgresql.org/message-id/202203162206.7spggyktx63e%40alvherre.pgsql

-- 
With Regards,
Amit Kapila.

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 30 Apr 2026 16:02:56 +0200

> That can't be a relevant cost compared to everything else.

Probably not. But unless we somehow encode "everything" at that point
we will make building different DDL decoders harder down the line.

So why not just save the normally serialised parse tree at this point
and let the decoders decide to do whatever they need.

> but it's nowhere visible in the WAL.

It can't be a big difference if it is not visible in the WAL.

Currently, we do treat DML exactly this way (or arguably worse).

In the following all the updates are decoded exactly the same

CREATE TABLE t(id int primary key, data text);

INSERT INTO t VALUES(1, 'one');

UPDATE t SET data='one' where id=1;
UPDATE t SET id=id;
UPDATE t SET id=10-9;
UPDATE t SET data='one';

ALL of the above get decoded as "UPDATE t SET data='one' where id=1;"

That is, we do not care how the values got there, as long as the end
result is the same.

And we do not track which fields were actually changed

The only reasons I see why we could not do the same for DDL are
1. it would be significantly more expensive to do so
or
2. we plan to fix some of that for DML as well and to start tracking
more of the intent in DML by extracting that from the statement trees.

--
Hannu

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 30 Apr 2026 13:40:47 -0700

> similar level of flexibility with other approach.

I think we can generate the same JSON-string representation of DDLs
from catalog information, it would also require a lot of code, though.
It would be independent from parse nodes and if we implement it as an
option for pg_get_xxx_ddl() functionality it would be able to be
reused by other tools too.

> level like column lists, row filtering, something related to RI, etc.

I think those publication-level features operate at a somewhat
different layer than the fundamental mechanism of capturing DDL
events. Plugins filter rows or columns based on configuration, but the
logical decoding itself guarantees that the DML events are reliably
passed to them. Given that the TRUNCATE in logical replication already
works so, I guess DDL should have the same fundamental guarantee.

it's unclear to me how plugins could reliably manage these event
triggers. While a plugin might create an event trigger during the
startup callback if it doesn't exist, it cannot drop it during the
shutdown callback. We also cannot establish a dependency between an
event trigger and a logical replication slot. We would likely need to
invent a new plugin callback specifically invoked at slot drop time
just to clean it up. Also, if different plugins want to capture DDL
events, they could end up registering different event triggers,
emitting multiple DDL WAL records for the same DDL event.

> be registered at first logical slot creation or may be at init time of

I think we need to note that replication slot creation and drop are
non-transactional operations. We need to make sure that both logical
slots and the event trigger are not orphaned in error or server crash
cases.

Regards,

---

From: Amit Kapila <[email-masked]>
Date: Mon, 4 May 2026 17:53:04 +0530

> reused by other tools too.

IIRC, this was discussed previously as well but we were not sure if we
can build all (especially some complex ones) without parsetree. See
discussion/emails around [1][2].

> emitting multiple DDL WAL records for the same DDL event.

Why would different plugins end up registering different event
triggers? I mean if they are already registered by the first plugin
what is the need to re-register.

[1] - https://www.postgresql.org/message-id/[email-masked]
[2] - https://www.postgresql.org/message-id/[email-masked]

-- 
With Regards,
Amit Kapila.

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 5 May 2026 10:36:24 -0700

> discussion/emails around [1][2].

Right. We would need parsetree somewhat. I think we're able to
generate CREATE and DROP TABLE statements for the particular table
without parsetree. But as for generating CREATE TABLE for the table,
it's going to be a combination of CREATE/ALTER TABLE/INDEX/SEQUENCE
statements, like pg_dump does. For instance, if a user executes
"CREATE TABLE foo (id serial primary key)", we create table, sequence,
and index, but searching system catalogs doesn't tell us these objects
are created in one statement. So we would generate multiple DDLs as
follow:

CREATE TABLE public.foo (id integer NOT NULL);
CREATE SEQUENCE public.foo_id_seq AS integer ...;
ALTER SEQUENCE public.foo OWNED BY foo;
ALTER TABLE public.foo ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

While these queries create the same table as the one on the publisher,
we need to consider whether it's okay to replicate these queries
instead of the oen statement originally executed on the publisher. If
we can use something like pg_get_table_ddl() in DDL replication, that
function would be able to be used also by the initial schema
synchronization.

As for ALTER TABLE, we would need parsetree of ALTER TABLE subcommands.

> what is the need to re-register.

Since you mentioned column lists and row filtering as examples of what
individual plugins already do in a reply to my point that registering
event triggers could be an additional step for other plugins, I
thought you meant that each plugin registering event triggers is not a
huge cumbersome. I think different plugins don't need to register
different event triggers. We can have the common event triggers to
write logical-DDL WAL and register them when the first logical slot is
created. But as I mentioned, we need to be careful about both the
concurrent slot creation/drop and the fact that slot creation/drop
operations are not transactional. Also, we cannot create event
triggers on the replicas even if a logical slot is created there. If a
failover happens before applying the WAL of creating event triggers,
we would need to somehow make sure that even triggers are created on
the new primary if it has logical slots.

Regards,

---

From: Masahiko Sawada <[email-masked]>
Date: Thu, 11 Jun 2026 00:07:26 -0700

> the new primary if it has logical slots.

I've reviewed and researched the last proposed DDL deparse patch[1],
and found that it can generate a set of commands for a single command.
For example, deparsing "create table test_serial (a serial)"
generates:

- CREATE SEQUENCE public.test_serial_a_seq CACHE 1 NO CYCLE INCREMENT
BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 RESTART 1 AS
pg_catalog.int4;
- CREATE TABLE public.test_serial (a pg_catalog.int4 STORAGE PLAIN NOT
NULL DEFAULT pg_catalog.nextval('public.test_serial_a_seq'::pg_catalog.regclass));
- ALTER SEQUENCE public.test_serial_a_seq OWNED BY public.test_serial.a;

And deparsing "create table test_b (a int, b int references test_a
(a))" generates:

- CREATE TABLE public.test_b (a pg_catalog.int4 STORAGE PLAIN, b
pg_catalog.int4 STORAGE PLAIN);
- ALTER TABLE public.test_b ADD CONSTRAINT test_b_b_fkey FOREIGN KEY
(b) REFERENCES public.test_a(a);

The reason is that the patch deparses after the command runs (via an
event trigger at ddl_command_end) and rebuilds the DDL from the
catalog. By that time, the server has already expanded one CREATE
TABLE into several internal sub-commands: transformCreateStmt() turns
a serial column into a CREATE SEQUENCE plus ALTER SEQUENCE ... OWNED
BY, a foreign key into an ALTER TABLE ADD CONSTRAINT, a primary key
into an index, and so on. So the output looks much like pg_dump or the
proposed pg_get_table_ddl() [2], because it also rebuilds from the
catalog, not from the original parse tree.

For a general DDL deparse feature, is it useful to turn one command
into several, or to emit a command that is different from what the
user ran? Since DDL deparsing can be used also for schema-qualifying
DDL commands, I considered it's useful for audit purposes or CDC use
cases. But if the generated DDL command could be different or even
multiple DDL commands are generated from one DDL command, I'm not sure
it's useful for other use cases than DDL replication.

Even in DDL replication use cases, I'm concerned that it might be
confusing users. For example, what if DDL replication has DDL command
filter and users specify it to replicate only 'CREATE TABLE' and not
for 'ALTER TABLE'? Users might expect all replicated DDL commands are
'CREATE TABLE' but it would not be able to replicate some form of
CREATE TABLE without ALTER TABLE or CREATE/ALTER SEQUENCE.

Also, given that the proposed pg_get_table_ddl()[2] has approximately
1800 lines to support generating CREATE TABLE commands for the given
table, does it really make sense to have additional 3000 lines to
support DDL deparsing that works in mostly the same way but based on
parse trees? While the JSON blob idea is flexible and preferable,
considering that two features seem to work in mostly the same way,
I'm not sure it can justify the implementation costs.

I think that DDL deparse should keep the intention and the form of the
original command as much as possible: do not turn one command into
several commands, and do not add options that the user did not write.
I see two benefits:

First, as a feature on its own, this makes DDL deparse usable for pure
schema-qualification. That is useful outside DDL replication too, for
example for audit or CDC. (Some audit cases may instead want the fully
expanded form. We could offer that as an option, but I think the
default should stay faithful to the original command.)

Second, as a building block for DDL replication, keeping the original
command lets the subscriber benefit from improvements in its own
version. Some commands may take a lighter lock, or use better new
defaults. For example, in v19 the default TOAST compression became
lz4, while it was pglz before. If we had DDL replication in v18 and a
user set up a logical replication v18 -> v19, I think they would not
want new tables on v19 to be forced to pglz just because that was the
publisher's default. If a user does want exactly the same options on
the subscriber, we can add an option to include the options that were
not explicitly specified too. But even then I do not think we should
produce multiple commands from one command.

Another point I think we should discuss is how to capture DDL events.
The last patch creates event triggers automatically at CREATE
PUBLICATION. I am not sure this is the best approach:

Third-party logical decoding output plugins do not use PUBLICATION, so
they cannot easily capture DDL events this way.
We could instead create a common event trigger when the first logical
slot is created. But it is not easy to make sure the trigger is
reliably created and dropped, including on replicas (for example, a
slot can exist only on a replica that is later promoted).

I think we can decouple the DDL capture infrastructure from event
triggers, and use it as common infrastructure for both event triggers
and WAL-logging of DDL for replication.  That way, I think we can
trigger DDL deparse while not relying on event triggers and minimizing
the code duplication. I'm drafting the patch for this idea and
feedback is very welcome.

Regards,

[1] https://www.postgresql.org/message-id/OS0PR01MB57163E6487EFF7378CB8E17C9438A%40OS0PR01MB5716.jpnprd01.prod.outlook.com
[2] https://www.postgresql.org/message-id/CANxoLDfjQnhM%3DE6JSyYo9s9OdjqoN8s_3wE5yL%3DkaDu_X8j-dA%40mail.gmail.com