public inbox for pgsql-general@postgresql.org
help / color / mirror / Atom feedRe: Support logical replication of DDLs
18+ messages / 6 participants
[nested] [flat]
* Re: Support logical replication of DDLs
@ 2026-02-09 10:42 Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Vitaly Davydov @ 2026-02-09 10:42 UTC (permalink / raw)
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
@ 2026-02-24 01:21 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-02-24 01:21 UTC (permalink / raw)
To: Vitaly Davydov <v.davydov@postgrespro.ru>; +Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; pgsql-hackers@lists.postgresql.org
On Mon, Feb 9, 2026 at 2:42 AM Vitaly Davydov <v.davydov@postgrespro.ru> wrote:
>
> 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.
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.
>
> 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.
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.
> 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.
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
@ 2026-04-20 23:14 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 2 replies; 18+ messages in thread
From: Masahiko Sawada @ 2026-04-20 23:14 UTC (permalink / raw)
To: Vitaly Davydov <v.davydov@postgrespro.ru>; +Cc: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi,
On Mon, Feb 23, 2026 at 5:21 PM Masahiko Sawada <sawada.mshk@gmail.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.
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
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
@ 2026-04-27 06:14 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:38 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
1 sibling, 2 replies; 18+ messages in thread
From: Dilip Kumar @ 2026-04-27 06:14 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Hi,
>
> On Mon, Feb 23, 2026 at 5:21 PM Masahiko Sawada <sawada.mshk@gmail.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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-04-28 06:57 ` Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
1 sibling, 1 reply; 18+ messages in thread
From: Amit Kapila @ 2026-04-28 06:57 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Apr 27, 2026 at 11:45 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > 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.
>
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.jpnprd0...
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
@ 2026-04-28 19:55 ` Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Hannu Krosing @ 2026-04-28 19:55 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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 <amit.kapila16@gmail.com> wrote:
> On Mon, Apr 27, 2026 at 11:45 AM Dilip Kumar <dilipbalaut@gmail.com>
> wrote:
> >
> > On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com>
> wrote:
> > >
> > > 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.
> >
>
> 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.jpnprd0...
>
> --
> With Regards,
> Amit Kapila.
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
@ 2026-04-29 03:39 ` Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 08:07 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
0 siblings, 1 reply; 18+ messages in thread
From: Dilip Kumar @ 2026-04-29 03:39 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 1:25 AM Hannu Krosing <hannuk@google.com> 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
>
> 2. What the callbacks themselves do in case of DDL
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-04-29 08:07 ` Hannu Krosing <hannuk@google.com>
2026-04-29 11:29 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 12:10 ` Re: Support logical replication of DDLs, take2 Andres Freund <andres@anarazel.de>
0 siblings, 2 replies; 18+ messages in thread
From: Hannu Krosing @ 2026-04-29 08:07 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> 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.
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
> what we would do with those deparsed representations: will we convert
> them to SQL on the subscriber and execute, or do something else?
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 08:07 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
@ 2026-04-29 11:29 ` Dilip Kumar <dilipbalaut@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: Dilip Kumar @ 2026-04-29 11:29 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Amit Kapila <amit.kapila16@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 1:37 PM Hannu Krosing <hannuk@google.com> wrote:
>
> On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> > 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?
>
> 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.
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 08:07 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
@ 2026-04-29 12:10 ` Andres Freund <andres@anarazel.de>
2026-04-30 14:02 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
1 sibling, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-04-29 12:10 UTC (permalink / raw)
To: Hannu Krosing <hannuk@google.com>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hi,
On 2026-04-29 10:07:04 +0200, Hannu Krosing wrote:
> On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> > 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.
>
> 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.
That can't be a relevant cost compared to everything else.
> 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
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 08:07 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 12:10 ` Re: Support logical replication of DDLs, take2 Andres Freund <andres@anarazel.de>
@ 2026-04-30 14:02 ` Hannu Krosing <hannuk@google.com>
0 siblings, 0 replies; 18+ messages in thread
From: Hannu Krosing @ 2026-04-30 14:02 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Dilip Kumar <dilipbalaut@gmail.com>; Amit Kapila <amit.kapila16@gmail.com>; Masahiko Sawada <sawada.mshk@gmail.com>; Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 2:10 PM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2026-04-29 10:07:04 +0200, Hannu Krosing wrote:
> > On Wed, Apr 29, 2026 at 5:39 AM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >
> > > 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.
> >
> > 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.
>
> 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.
> > 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
>
> 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.
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
@ 2026-04-28 21:38 ` Masahiko Sawada <sawada.mshk@gmail.com>
1 sibling, 0 replies; 18+ messages in thread
From: Masahiko Sawada @ 2026-04-28 21:38 UTC (permalink / raw)
To: Dilip Kumar <dilipbalaut@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sun, Apr 26, 2026 at 11:15 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
>
> > 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.
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.
>
> > 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?
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.
>
> > 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?
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.
>
> > 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?
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,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
@ 2026-04-28 06:32 ` Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
1 sibling, 1 reply; 18+ messages in thread
From: Amit Kapila @ 2026-04-28 06:32 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Feb 23, 2026 at 5:21 PM Masahiko Sawada <sawada.mshk@gmail.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.
>
> 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.
>
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
> 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.
>
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
> 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.
>
Possible but the point was flexibility and ease with which users can
implement mapping with JSON approach.
>
> 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.
>
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...
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
@ 2026-04-28 21:48 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-30 04:44 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-04-28 21:48 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Apr 27, 2026 at 11:32 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Tue, Apr 21, 2026 at 4:45 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Mon, Feb 23, 2026 at 5:21 PM Masahiko Sawada <sawada.mshk@gmail.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.
> >
> > 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.
> >
>
> 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,
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.
>
> 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.
> >
>
> 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?
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,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
@ 2026-04-30 04:44 ` Amit Kapila <amit.kapila16@gmail.com>
2026-04-30 20:40 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Amit Kapila @ 2026-04-30 04:44 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 3:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Apr 27, 2026 at 11:32 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > 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,
>
> 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?
>
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.
>
> >
> > 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.
> > >
> >
> > 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?
>
> 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.
>
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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-30 04:44 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
@ 2026-04-30 20:40 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-05-04 12:23 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-04-30 20:40 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Apr 29, 2026 at 9:44 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Wed, Apr 29, 2026 at 3:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Mon, Apr 27, 2026 at 11:32 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > 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,
> >
> > 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?
> >
>
> 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.
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.
>
> >
> > >
> > > 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.
> > > >
> > >
> > > 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?
> >
> > 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.
> >
>
> 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.
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.
> 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.
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,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-30 04:44 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-30 20:40 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
@ 2026-05-04 12:23 ` Amit Kapila <amit.kapila16@gmail.com>
2026-05-05 17:36 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 18+ messages in thread
From: Amit Kapila @ 2026-05-04 12:23 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Fri, May 1, 2026 at 2:11 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Wed, Apr 29, 2026 at 9:44 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Wed, Apr 29, 2026 at 3:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > >
> > > On Mon, Apr 27, 2026 at 11:32 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > > 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,
> > >
> > > 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?
> > >
> >
> > 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.
>
> 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.
>
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].
> >
> > >
> > > >
> > > > 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.
> > > > >
> > > >
> > > > 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?
> > >
> > > 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.
> > >
> >
> > 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.
>
> 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.
>
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/20150221212017.GD2037@awork2.anarazel.de
[2] - https://www.postgresql.org/message-id/20150224021018.GH30784@awork2.anarazel.de
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Support logical replication of DDLs, take2
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Re: Support logical replication of DDLs Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-30 04:44 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-30 20:40 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-05-04 12:23 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
@ 2026-05-05 17:36 ` Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 0 replies; 18+ messages in thread
From: Masahiko Sawada @ 2026-05-05 17:36 UTC (permalink / raw)
To: Amit Kapila <amit.kapila16@gmail.com>; +Cc: Vitaly Davydov <v.davydov@postgrespro.ru>; Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, May 4, 2026 at 5:23 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, May 1, 2026 at 2:11 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Wed, Apr 29, 2026 at 9:44 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > On Wed, Apr 29, 2026 at 3:19 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > >
> > > > On Mon, Apr 27, 2026 at 11:32 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > > >
> > > > > 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,
> > > >
> > > > 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?
> > > >
> > >
> > > 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.
> >
> > 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.
> >
>
> 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].
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.
>
> > >
> > > >
> > > > >
> > > > > 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.
> > > > > >
> > > > >
> > > > > 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?
> > > >
> > > > 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.
> > > >
> > >
> > > 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.
> >
> > 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.
> >
>
> 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.
>
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,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-05-05 17:36 UTC | newest]
Thread overview: 18+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-09 10:42 Re: Support logical replication of DDLs Vitaly Davydov <v.davydov@postgrespro.ru>
2026-02-24 01:21 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-20 23:14 ` Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-27 06:14 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-28 06:57 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 19:55 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 03:39 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 08:07 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-29 11:29 ` Re: Support logical replication of DDLs, take2 Dilip Kumar <dilipbalaut@gmail.com>
2026-04-29 12:10 ` Re: Support logical replication of DDLs, take2 Andres Freund <andres@anarazel.de>
2026-04-30 14:02 ` Re: Support logical replication of DDLs, take2 Hannu Krosing <hannuk@google.com>
2026-04-28 21:38 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-28 06:32 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-28 21:48 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-04-30 04:44 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-04-30 20:40 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
2026-05-04 12:23 ` Re: Support logical replication of DDLs, take2 Amit Kapila <amit.kapila16@gmail.com>
2026-05-05 17:36 ` Re: Support logical replication of DDLs, take2 Masahiko Sawada <sawada.mshk@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox