DDL deparse

Hi,

On Thu, Jun 18, 2026 at 3:24 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Hi all,
(CC'ing people who worked on DDL deparse)

Thanks for working on this feature!

Please note that I haven't spent enough time reading all the older
threads in this area, so I'm still building my understanding. Kindly
bear with me

DDL Deparse has been developed[1][2] for quite a long time and was
originally proposed as a building block of DDL replication. Reading
the related discussion threads, there is an agreement on implementing
DDL replication on top of DDL deparse, and some hackers prefer the
format of its output.

Could you please summarize the other approaches for DDL replication,
their pros and cons, and why the deparsing approach is preferred? I
have a vague understanding, but summarizing it here would help
reviewers a lot. Thank you!

I've reviewed the last proposed approach and
researched DDL deparse/DDL replication, and there are some points that
are unclear to me, and things have changed since it was actively
developed. So I've started this thread separately from the DDL
replication thread in order to discuss DDL deparse itself while
working toward DDL replication development.

+1 to splitting things for better discussion.

Quick summary of the last developed DDL deparse feature[3]: the basic
functionality is that it takes a parse tree as an input and constructs
DDLs by retrieving the information from system catalogs based on the
parse tree.

The parse tree is what gets generated when the DDL command is being
executed, right? I mean, we can't generate the DDL statement as an
after-the-fact operation? I haven't studied the pg_get_table_ddl patch
yet.

The output format is self-documenting JSON, enabling us to
easily do table name mapping or schema name mapping while
reconstructing a DDL command.

+1 to starting with JSON. It can later be extended with more optimized
formats (binary) for sending over the network.

For instance, deparsing "create table
test (a int)" produces:

{
"fmt": "CREATE TABLE %{identity}D (%{table_elements:, }s)",
"identity": {
"objname": "test",
"schemaname": "public"
},
"table_elements": [
{
"fmt": "%{name}I %{coltype}T STORAGE %{colstorage}s",
"name": "a",
"type": "column",
"coltype": {
"typmod": "",
"typarray": false,
"typename": "int4",
"schemaname": "pg_catalog"
},
"colstorage": "PLAIN"
}
]
}

Do we need an LSN+timeline, creation time, or some sort of ordering ID
field for establishing the order of operations (e.g., DDL command1
executed before command2)? I understand that when used for DDL
replication it does have an LSN field, but for other DDL deparsing
use-cases it would also be helpful.

The main point that I want to discuss is what output we expect from
DDL deparse, especially CREATE DDLs. I originally thought that DDL
deparse converts the parse tree back into the DDL command originally
executed. However, what DDL deparse for CREATE TABLE actually does is
to generate possibly multiple DDLs to achieve the exact same catalog
state. That is, the deparsed command doesn't necessarily preserve the
user intent in the original DDL command, and possibly generates
multiple commands for the one command. For instance:

I haven't seen any discussion on whether it's architecturally correct
for a DDL deparser not to preserve the user intent.

I briefly played with MySQL binlog replication and noticed that it
doesn't decompose DDLs. I haven't read their documentation, but I
believe there's a concern with decomposing a single DDL into multiple
DDLs - how would the consumer make it atomic and crash-safe alongside
other concurrent DDLs? Say the consumer is another PostgreSQL database
and it wants to create a table along with indexes, FKs, constraints,
etc. If we decompose the single DDL into multiple DDLs, how can the
consumer replay them together atomically and be crash-safe?

If it's okay for DDL deparse to expand one DDL command into multiple
ones, it's very similar to what pg_get_table_ddl()[4] does. The only
difference between the two is the output format. I guess we could add
an option to the SQL function to output DDLs as a JSON blob. I don't
think we want to maintain two features if they provide very similar
functionality. Also, I'm not if it could be useful other than DDL
replication use cases.

I personally think that DDL deparse should preserve (and possibly
normalize) the user intent, producing the following query, for
example:

My initial thought is that it should preserve user intent, but I could be wrong.

Feedback is very welcome.

I did a quick pass over the patch. The code looks a bit lengthy - is
it possible to split it up? For example, JSON-related helpers first,
then basic CREATE TABLE, then sequences, then constraints, then ALTER
TABLE, etc.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com

On Tue, Jun 23, 2026 at 4:54 PM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

On Thu, Jun 18, 2026 at 3:24 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

Hi all,
(CC'ing people who worked on DDL deparse)

Thanks for working on this feature!

Please note that I haven't spent enough time reading all the older
threads in this area, so I'm still building my understanding. Kindly
bear with me

DDL Deparse has been developed[1][2] for quite a long time and was
originally proposed as a building block of DDL replication. Reading
the related discussion threads, there is an agreement on implementing
DDL replication on top of DDL deparse, and some hackers prefer the
format of its output.

Could you please summarize the other approaches for DDL replication,
their pros and cons, and why the deparsing approach is preferred? I
have a vague understanding, but summarizing it here would help
reviewers a lot. Thank you!

To summarize the alternatives that have been discussed for carrying
DDL to the subscriber, and why deparse is preferred.

1. Replicate the raw SQL text with the search_path value. While it is
simple, the statement is ambiguous even with the search_path, as the
publisher and the subscriber might have a different set of schemas.

2. Reconstruct the DDL from catalog state using pg_get_XXX_ddl().
While it is always normalized and schema-qualified and can reuse
ruleutils.c and ddlutils.c, it loses the original intent. It also
still requires ALTER TABLE support in some form.

3. Infer the DDL from the stream of catalog changes decoded from WAL.
It yields a delta rather than a post-image, and rides on logical
decoding naturally, since the catalog changes are already in the WAL.
However, some information needed to reproduce a command exists only in
the parse tree and is never persisted in the catalog or the WAL; the
USING expression of ALTER TABLE ... ALTER COLUMN ... TYPE ... USING
(expr) is the canonical example.

Deparsing the parse tree avoids these problems: it captures the
operation itself (so ALTER stays ALTER and replicates incrementally),
schema-qualifies names, and has access to parse-tree-only information
such as the USING expression that (3) cannot recover. DDL deparse
could also be implemented as a feature in its own right, beyond DDL
replication. For a Postgres-to-Postgres solution it is natural to emit
a deparsed SQL statement as the output; as for the output format, some
senior hackers prefer the flexibility of a self-documenting JSON blob.

I've reviewed the last proposed approach and
researched DDL deparse/DDL replication, and there are some points that
are unclear to me, and things have changed since it was actively
developed. So I've started this thread separately from the DDL
replication thread in order to discuss DDL deparse itself while
working toward DDL replication development.

+1 to splitting things for better discussion.

Quick summary of the last developed DDL deparse feature[3]: the basic
functionality is that it takes a parse tree as an input and constructs
DDLs by retrieving the information from system catalogs based on the
parse tree.

The parse tree is what gets generated when the DDL command is being
executed, right? I mean, we can't generate the DDL statement as an
after-the-fact operation? I haven't studied the pg_get_table_ddl patch
yet.

I think that capture should happen at the execution time, since the
parse tree is transient. But deparsing can be deferred; if we write
the parse tree to WAL at executiontime, the deparse can run later at
decode time. So generation can be done after the fact; only the
capture cannot.

pg_get_table_ddl() differs: its input is the catalog state, not a
parse tree, so it can run any time after the fact -- but it yields the
resulting state, not the original operation.

For instance, deparsing "create table
test (a int)" produces:

{
"fmt": "CREATE TABLE %{identity}D (%{table_elements:, }s)",
"identity": {
"objname": "test",
"schemaname": "public"
},
"table_elements": [
{
"fmt": "%{name}I %{coltype}T STORAGE %{colstorage}s",
"name": "a",
"type": "column",
"coltype": {
"typmod": "",
"typarray": false,
"typename": "int4",
"schemaname": "pg_catalog"
},
"colstorage": "PLAIN"
}
]
}

Do we need an LSN+timeline, creation time, or some sort of ordering ID
field for establishing the order of operations (e.g., DDL command1
executed before command2)? I understand that when used for DDL
replication it does have an LSN field, but for other DDL deparsing
use-cases it would also be helpful.

I think it depends on in which case DDL deparse is used. For DDL
replication the ordering is already established by the LSN, so a
separate field isn't needed there. I can't immediately think of
another use case that would require an explicit ordering ID -- do you
have a specific one in mind?

The main point that I want to discuss is what output we expect from
DDL deparse, especially CREATE DDLs. I originally thought that DDL
deparse converts the parse tree back into the DDL command originally
executed. However, what DDL deparse for CREATE TABLE actually does is
to generate possibly multiple DDLs to achieve the exact same catalog
state. That is, the deparsed command doesn't necessarily preserve the
user intent in the original DDL command, and possibly generates
multiple commands for the one command. For instance:

I haven't seen any discussion on whether it's architecturally correct
for a DDL deparser not to preserve the user intent.

I briefly played with MySQL binlog replication and noticed that it
doesn't decompose DDLs. I haven't read their documentation, but I
believe there's a concern with decomposing a single DDL into multiple
DDLs - how would the consumer make it atomic and crash-safe alongside
other concurrent DDLs? Say the consumer is another PostgreSQL database
and it wants to create a table along with indexes, FKs, constraints,
etc. If we decompose the single DDL into multiple DDLs, how can the
consumer replay them together atomically and be crash-safe?

I think the decomposed DDLs should be run inside a single transaction.
This is less of a problem in PostgreSQL than in MySQL because
PostgreSQL DDL is transactional, so wrapping the commands gives
all-or-nothing application and crash-safety follows from the normal
apply mechanism.

Feedback is very welcome.

I did a quick pass over the patch. The code looks a bit lengthy - is
it possible to split it up? For example, JSON-related helpers first,
then basic CREATE TABLE, then sequences, then constraints, then ALTER
TABLE, etc.

The patch was just based on the current HEAD and submitted just for
anyone interested in this feature to check the behavior. I don't think
it's ready for the review. We can refer the pre-rebased patches[1]/messages/by-id/OS0PR01MB57163E6487EFF7378CB8E17C9438A@OS0PR01MB5716.jpnprd01.prod.outlook.com
that are split to multiple patches.

Regards,

[1]: /messages/by-id/OS0PR01MB57163E6487EFF7378CB8E17C9438A@OS0PR01MB5716.jpnprd01.prod.outlook.com

--
Masahiko Sawada

Amazon Web Services: https://aws.amazon.com

Dear Hackers,

So my question is: should DDL deparse preserve user intent, or is it
acceptable to materialize the catalog state (in which case, should we
just extend pg_get_table_ddl instead)?

I think, it depends on the usage scenarios. There are some use cases of:
major upgrade, replication to other databases, replication to other
cliends, DDL mining from WAL, logging and audit. In case of replication to
other databases, splitting a complex command to a number of simple commands
that materialize the same schema looks ok. In case of DDL mining from WAL
or logging and audit scenarios it would be preferable to keep user intent.

Sorry for off-topic, but I'm still not sure that deparsing the original DDL
command from the parse tree is the best approach. Below are some problem
queries for deparsing original DDL from the parse tree.

Query 1: CTAS with function

CREATE OR REPLACE FUNCTION f1()
RETURNS integer
AS $$
CREATE TABLE t2(c INT);
SELECT 10;
$$
LANGUAGE SQL;

CREATE TABLE t AS SELECT f1();

Query 2: CTAS with a temp table

CREATE TABLE t AS SELECT * FROM temptable;

Query 3: CTAS with a function parameter

CREATE TABLE t AS SELECT @mypar

Decoding final changes in the WAL seems to be the more suitable approach
for such cases.

Adding a new data into the WAL like a new record type with original DDL for
for logical decoding seems redundant. Decoding system catalog changes from
the WAL with some new hints may help to decode the commands like
ALTER TABLE ALTER COLUMN TYPE USING. Defining user command boundaries in the
WAL may help to decode the original command as well.

With best regards,
Vitaly

On Thu, Jun 25, 2026 at 1:12 AM Vitaly Davydov <v.davydov@postgrespro.ru> wrote:

Dear Hackers,

So my question is: should DDL deparse preserve user intent, or is it
acceptable to materialize the catalog state (in which case, should we
just extend pg_get_table_ddl instead)?

I think, it depends on the usage scenarios. There are some use cases of:
major upgrade, replication to other databases, replication to other
cliends, DDL mining from WAL, logging and audit. In case of replication to
other databases, splitting a complex command to a number of simple commands
that materialize the same schema looks ok. In case of DDL mining from WAL
or logging and audit scenarios it would be preferable to keep user intent.

Agreed. I think that generating DDL while keeping the user intent
would cover both cases.

ISTM that it's a balanced DDL deparse policy that generates DDLs from
parse tree + system catalogs while normalizing the query and
preserving user intent but not expand one command into multiple
commands. It might need special handlings for CTAS and LIKE clauses in
CREATE TABLE, though.

Sorry for off-topic, but I'm still not sure that deparsing the original DDL
command from the parse tree is the best approach. Below are some problem
queries for deparsing original DDL from the parse tree.

Query 1: CTAS with function

CREATE OR REPLACE FUNCTION f1()
RETURNS integer
AS $$
CREATE TABLE t2(c INT);
SELECT 10;
$$
LANGUAGE SQL;

CREATE TABLE t AS SELECT f1();

Query 2: CTAS with a temp table

CREATE TABLE t AS SELECT * FROM temptable;

I think CTAS needs special handling for DDL replication purposes; it
can be replicated as CREATE TABLE + INSERT. However, if DDL deparse
expands CTAS into CREATE TABLE + INSERT, it might be against the
policy that DDL deparse would preserve user intent. So an idea is that
DDL deparse has two output modes for CTAS: one generates CREATE TABLE
... AS to preserve the user intent (for audit cases etc.) and another
one generates CREATE TABLE. DDL replication would use the latter mode
to capture the CREATE TABLE.

Query 3: CTAS with a function parameter

CREATE TABLE t AS SELECT @mypar

This is not supported in PostgreSQL.

Decoding final changes in the WAL seems to be the more suitable approach
for such cases.

Adding a new data into the WAL like a new record type with original DDL for
for logical decoding seems redundant. Decoding system catalog changes from
the WAL with some new hints may help to decode the commands like
ALTER TABLE ALTER COLUMN TYPE USING. Defining user command boundaries in the
WAL may help to decode the original command as well.

Are you referring to the idea of replicating system catalog changes as
DML to the subscriber? I'm not sure it works in cross-version
replication setup without some form of intermediate representation for
object definitions.

Or you might mean generating DDL from the WAL records of system
catalog changes. IIUC it reconstructs the system catalog state as DDL
and so loses the user intent. For instance, 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. but if they
deliberately chose pglz, they'd want it preserved. We cannot know
whether a user explicitly specified pglz when creating the table by
just looking at the system catalogs.

Regards,

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

Dear Masahiko Sawada, All

Agreed. I think that generating DDL while keeping the user intent
would cover both cases.

I think CTAS needs special handling for DDL replication purposes; it
can be replicated as CREATE TABLE + INSERT. However, if DDL deparse
expands CTAS into CREATE TABLE + INSERT, it might be against the
policy that DDL deparse would preserve user intent. So an idea is that
DDL deparse has two output modes for CTAS: one generates CREATE TABLE
... AS to preserve the user intent (for audit cases etc.) and another
one generates CREATE TABLE. DDL replication would use the latter mode
to capture the CREATE TABLE.

Some more thoughts...

Scenarios, that require to keep the original query, may relate to other
types of solutions, than DDL replication. For example, logging and auditing
solutions do not require DDL to be applied. Solutions may just log the
original query unchanged. There is no need to support such solutions
in the core. Utility hook seems to be the right choice.

Solutions, like WAL miners, may require the original query if a DBA
wants to look closely on some WAL changes, but it is not a strict
requirement if such solutions are used for CDC purposes.

For replication purpose we must guarantee that the replica (or an
external client) gets the right set of commands, that reproduce the
schema on the master. A number of simple commands instead a single
command looks ok. I guess, for external clients it would be better
to receive small simple commands instead of a single complex command.

I guess, we may optionally keep both the original query and the final
set of simple DDL commands suitable for replication purposes.

Furthermore, an original user query like SELECT f() may be finally
represented in the form of CREATE TABLE, if f() creates a new table.
Which query should we get for logging or auditing purposes?

Query 3: CTAS with a function parameter

CREATE TABLE t AS SELECT @mypar

This is not supported in PostgreSQL.

Sorry, I mean a query like below, where a function variable is used:

CREATE OR REPLACE FUNCTION temp_x () RETURNS void
AS $$
DECLARE
_x integer;
BEGIN
CREATE TABLE t_b AS SELECT _x;
END;
$$ LANGUAGE plpgsql;

Or you might mean generating DDL from the WAL records of system
catalog changes. IIUC it reconstructs the system catalog state as DDL
and so loses the user intent. For instance, 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. but if they
deliberately chose pglz, they'd want it preserved. We cannot know
whether a user explicitly specified pglz when creating the table by
just looking at the system catalogs.

I mean to decode DDL changes from the WAL on the master and convert it
into a suitable form (sql or json) before sending to a replica or a
client.

Thank you for mentioning such example. I agree, it may be hard to define,
whether a specific setting was explicitly set by user or not. There are
some possible solutions may be like: (1) ignore the setting if it has
a default value, (2) do not replicate (or optionally replicate) some
specific settings like toast compression method.

With best regards,
Vitaly

On Fri, Jun 26, 2026 at 3:59 AM Vitaly Davydov <v.davydov@postgrespro.ru> wrote:

Dear Masahiko Sawada, All

Agreed. I think that generating DDL while keeping the user intent
would cover both cases.

I think CTAS needs special handling for DDL replication purposes; it
can be replicated as CREATE TABLE + INSERT. However, if DDL deparse
expands CTAS into CREATE TABLE + INSERT, it might be against the
policy that DDL deparse would preserve user intent. So an idea is that
DDL deparse has two output modes for CTAS: one generates CREATE TABLE
... AS to preserve the user intent (for audit cases etc.) and another
one generates CREATE TABLE. DDL replication would use the latter mode
to capture the CREATE TABLE.

Some more thoughts...

Scenarios, that require to keep the original query, may relate to other
types of solutions, than DDL replication. For example, logging and auditing
solutions do not require DDL to be applied. Solutions may just log the
original query unchanged. There is no need to support such solutions
in the core. Utility hook seems to be the right choice.

Solutions, like WAL miners, may require the original query if a DBA
wants to look closely on some WAL changes, but it is not a strict
requirement if such solutions are used for CDC purposes.

Fair point.

For replication purpose we must guarantee that the replica (or an
external client) gets the right set of commands, that reproduce the
schema on the master. A number of simple commands instead a single
command looks ok.

Right.

I guess, for external clients it would be better
to receive small simple commands instead of a single complex command.

Could you elaborate on the reason for this point?

I guess, we may optionally keep both the original query and the final
set of simple DDL commands suitable for replication purposes.

Logging, auditing, and WAL-mining are fine with the original query and
can be handled outside the core -- none of them need an
intent-preserving deparse in core. That leaves DDL replication as the
single use case for an intent-preserving DDL deparse. Expanding one
command into several is fine for DDL replication, but knowing what the
user specified versus what was left to the default is something
replication does need. And that is what pg_get_table_ddl() cannot
provide. I thought DDL deparse should not expand one command into
several, partly because that property would make it useful beyond
replication and help justify building it. But that might be
optimistic. Other use cases don't really need it, and expanding or not
doesn't matter much for DDL replication.

Furthermore, an original user query like SELECT f() may be finally
represented in the form of CREATE TABLE, if f() creates a new table.
Which query should we get for logging or auditing purposes?

I'm thinking of logging only the top-level query.

Query 3: CTAS with a function parameter

CREATE TABLE t AS SELECT @mypar

This is not supported in PostgreSQL.

Sorry, I mean a query like below, where a function variable is used:

CREATE OR REPLACE FUNCTION temp_x () RETURNS void
AS $$
DECLARE
_x integer;
BEGIN
CREATE TABLE t_b AS SELECT _x;
END;
$$ LANGUAGE plpgsql;

Thank you for the clarification. If we replicate only the top-level
DDL, the CREATE TABLE inside the function temp_x() won't be
replicated.

Or you might mean generating DDL from the WAL records of system
catalog changes. IIUC it reconstructs the system catalog state as DDL
and so loses the user intent. For instance, 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. but if they
deliberately chose pglz, they'd want it preserved. We cannot know
whether a user explicitly specified pglz when creating the table by
just looking at the system catalogs.

I mean to decode DDL changes from the WAL on the master and convert it
into a suitable form (sql or json) before sending to a replica or a
client.

Thank you for mentioning such example. I agree, it may be hard to define,
whether a specific setting was explicitly set by user or not. There are
some possible solutions may be like: (1) ignore the setting if it has
a default value,

I guess it's hard to determine where the default setting came from; it
might be because it was not specified in the query or the user
explicitly specified the default value.

(2) do not replicate (or optionally replicate) some
specific settings like toast compression method.

How can we draw a line for settings that are replicated or not replicated?

Regards,

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

On 6/27/26 09:10, Masahiko Sawada wrote:

I guess, for external clients it would be better
to receive small simple commands instead of a single complex command.

Could you elaborate on the reason for this point?

When we speak about logical replication clients that are not relational
databases, it would be much easier for developers to implement processing
of received DDL data if it is in a simple and well defined form. If we
replicate an original query like CTAS, it is hard to process such query on
the non rdbms client. Another example - SELECT f(), where f() creates a
number of tables. It is why I assumed that simple DDL queries may be better
for external clients. I'm sorry, I have no other descriptive examples at
the moment.

I'm still not sure, that splitting a single CREATE COMMAND into a number of
simpler commands (like CREATE TABLE, ALTER TABLE ADD COLUMN, ADD INDEX, ADD
CONSTRAINT) may help to developers of non-rdbms clients, but we should not
send original queries to such non rdbms clients in general.

To be honest, I haven't seen any non RDBMS clients that require DDL. It most
cases, "Relation" (R) message from logical replication protocol may be enough.
Not sure, my assumptions are real. It is why I think, that the main use case
for DDL replication is the major upgrage or migration to another RDBMS.

CREATE OR REPLACE FUNCTION temp_x () RETURNS void
AS $$
DECLARE
_x integer;
BEGIN
CREATE TABLE t_b AS SELECT _x;
END;
$$ LANGUAGE plpgsql;

Thank you for the clarification. If we replicate only the top-level
DDL, the CREATE TABLE inside the function temp_x() won't be
replicated.

I guess, it is ok for another postgresql instance, but it doesn't not
work for other types of clients in general, because they do not know
how to process SELECT temp_x().

(2) do not replicate (or optionally replicate) some
specific settings like toast compression method.

How can we draw a line for settings that are replicated or not replicated?

It is a good question, I can't answer to it completely right now. But, we may
disable replication of settings that belong to the physical storage
configuration, not logical changes in the schema. These settings may be
specific for a particular PostgreSQL major version and will not be applicable
for non RDBMS clients. We may enable replication of such settings optionally,
when needed.

With best regards,
Vitaly

On Mon, Jun 29, 2026 at 11:23 PM Vitaly Davydov
<v.davydov@postgrespro.ru> wrote:

On 6/27/26 09:10, Masahiko Sawada wrote:

I guess, for external clients it would be better
to receive small simple commands instead of a single complex command.

Could you elaborate on the reason for this point?

When we speak about logical replication clients that are not relational
databases, it would be much easier for developers to implement processing
of received DDL data if it is in a simple and well defined form. If we
replicate an original query like CTAS, it is hard to process such query on
the non rdbms client. Another example - SELECT f(), where f() creates a
number of tables. It is why I assumed that simple DDL queries may be better
for external clients. I'm sorry, I have no other descriptive examples at
the moment.

Thank you for the explanation.

I'm still not sure, that splitting a single CREATE COMMAND into a number of
simpler commands (like CREATE TABLE, ALTER TABLE ADD COLUMN, ADD INDEX, ADD
CONSTRAINT) may help to developers of non-rdbms clients, but we should not
send original queries to such non rdbms clients in general.

I personally think we should not split a single CREATE command into
multiple commands. IIUC the reason why PostgreSQL itself splits a
CREATE TABLE into multiple commands is that a consequence of the
object model (a sequence, an index etc.s are independent catalog
objects), not an attempt to give the user a simpler decomposed form.
So I don't think it implies deparse should mirror that decomposition.

For ALTER TABLE specifically, splitting is actively harmful, not just
unnecessary: splitting multiple sub-commands of one ALTER TABLE into
separate statements can turn one rewrite into several. So ALTER TABLE
sub-commands must stay together in one statement.

To be honest, I haven't seen any non RDBMS clients that require DDL. It most
cases, "Relation" (R) message from logical replication protocol may be enough.
Not sure, my assumptions are real. It is why I think, that the main use case
for DDL replication is the major upgrage or migration to another RDBMS.

The Relation message might work as an alternative of CREATE TABLE but
DDL replication would need to support ALTER TABLE as well.

CREATE OR REPLACE FUNCTION temp_x () RETURNS void
AS $$
DECLARE
_x integer;
BEGIN
CREATE TABLE t_b AS SELECT _x;
END;
$$ LANGUAGE plpgsql;

Thank you for the clarification. If we replicate only the top-level
DDL, the CREATE TABLE inside the function temp_x() won't be
replicated.

I guess, it is ok for another postgresql instance, but it doesn't not
work for other types of clients in general, because they do not know
how to process SELECT temp_x().

The built-in logical replication is designed for postgres-to-postgres
replication and it doesn't replicate the SELECT statement. I'm not
sure that the built-in logical replication needs to care about a
heterogeneous environment. As for replicating to another DBMS, event
trigger + DDL deparse would be the right solution.

Having said that, given that it's common to create/alter/drop table
within a SQL function (e.g., extensions managing partitioned tables
etc.), it would be better to replicate DDL commands too that are
executed within a SQL function. But I don't think that we should
replicate DDL commands that are internally generated while executing
another DDL command.

Also, CTAS is the one case that needs special handling, and I'd keep
it consistent with the "don't split" principle rather than as an
exception to it. The key point: deparse still emits a single CREATE
statement and does not generate INSERTs itself. For replication we
want CREATE TABLE + the rows, but the rows come through the normal DML
decoding path, not from deparse -- so deparse isn't splitting
anything. Concretely I'd give two modes: one emits "CREATE TABLE ...
AS ..." faithfully, the other emits just "CREATE TABLE ..." (schema
only); replication uses the latter and the data arrives as DML.

(2) do not replicate (or optionally replicate) some
specific settings like toast compression method.

How can we draw a line for settings that are replicated or not replicated?

It is a good question, I can't answer to it completely right now. But, we may
disable replication of settings that belong to the physical storage
configuration, not logical changes in the schema. These settings may be
specific for a particular PostgreSQL major version and will not be applicable
for non RDBMS clients. We may enable replication of such settings optionally,
when needed.

True, but I think it's better to somehow preserve the user intent
rather than replicating only the pre-selected settings.

Regards,

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

On Wed, Jun 24, 2026 at 10:09 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:

The patch was just based on the current HEAD and submitted just for
anyone interested in this feature to check the behavior. I don't think
it's ready for the review. We can refer the pre-rebased patches[1]
that are split to multiple patches.

I've updated the DDL deparse patch for CREATE TABLE and ALTER TABLE.
There are three major changes from the previous DDL deparse code;

1. The new design explicitly mentions that DDL deparse should preserve
user intent (e.g., a clause user didn't write is not emitted) while
allowing to normalize and schema-qualify the original DDL. That makes
things what we should follow in future changes.

2. Support the latest CREATE/ALTER TABLE syntaxes and refactored the
deparse code while fixing a lot of bugs. I've refactored the DDL
deparse code and introduced some helper functions to make JSON blob
construction easy. Also, the previous patch could not deparse some
CREATE/ALTER TABLE syntax properly. For instance,

- "create table t (a int, unique (a) using index tablespace ts
deferrable);" produces "CREATE TABLE public.t (a pg_catalog.int4
STORAGE PLAIN, CONSTRAINT t_a_key UNIQUE (a) DEFERRABLE USING INDEX
TABLESPACE ts)", which is not executable syntax.

- "create table t (a int, unique (a) with (fillfactor = 90));"
produces "CREATE TABLE public.t2 (a pg_catalog.int4 STORAGE PLAIN,
CONSTRAINT t2_a_key UNIQUE (a))", which lacks the fillfactor setting.

Also, I found out that we need to differently treat the USING clause
in ALTER TABLE .. ALTER TYPE command from other claude especially when
DROP COLUMN subcommand is also executed in the same ALTER TABLE
command. The following ALTER TABLE cannot be deparsed properly:

create table test (a int, b int);
alter table test drop column a, alter column b type numeric using (a +
b)::numeric;

old design: ALTER TABLE public.test DROP COLUMN a, ALTER COLUMN b SET
DATA TYPE pg_catalog."numeric" USING (("?dropped?column?" +
b))::numeric

new design: ALTER TABLE public.test DROP COLUMN a, ALTER COLUMN b SET
DATA TYPE pg_catalog."numeric" USING ((a + b))::numeri

I've confirmed these are fixed and verified that DDL depasse can
properly deparse all CREATE/ALTER TABLE commands written in the
regression tests by using the new regression tests mentioned below.

3. Regression tests now require only one regression test run.
Previously it required running regression tests twice in order to
prove that deparsed DDLs result in the same effect. A new
001_deparse_regress.pl test reliably detects the problem if newly
added clause/syntaxes miss DDL deparse support.

In the new tests, we prepare an event trigger function, and in the
process utility hook function we execute the CREATE TABLE command in a
subtransaction. In the event trigger function we perform DDL deparse
and save the generated JSON blob in TopTransactionContext. Then,
rollback the subtransaction and re-execute the deparsed DDL again.

I've added more tests for CREATE/ALTER TABLE deparse to test_ddl_deparse.

FYI test_ddl_deparse can be used for interactive tests during the
development. It can be installed via shared_preload_libraries or LOAD
command, and we can set test_ddl_deparse.print_deparsed_ddl to
'json|text|both' to see how the CREATE TABLE DDL is deparsed:

=# create extension test_ddl_deparse;
CREATE EXTENSION
=# set test_ddl_deparse.print_deparsed_ddl to 'both';
SET
=# create table test_tbl (id serial, name text);
NOTICE: deparsed JSON: {"tag": "CREATE TABLE", "command": {"fmt":
"CREATE%{persistence}s TABLE%{if_not_exists}s
%{identity}D%{of_type}s%{partition_of}s%{table_elements}s%{inherits}s%{partition_bound}s%{partition_by}s%{access_method}s%{with_clause}s%{on_commit}s%{tablespace}s",
"of_type": null, "identity": {"objname" : "test_tbl", "schemaname":
"public"}, "inherits": null, "on_commit": null, "tablespace": null,
"persistence": null, "with_clause": null, "partition_by": null,
"partition_of": null, "access_method": null, "if_not_exists": null,
"table_elements": {"fmt": " (%{elements:, }s)", "elements": [{"fmt":
"%{name}I %{coltype}T%{storage}s%{compression}s%{collation}s%{not_null}s%{default}s%{identity_column}s%{generated_column}s",
"name": "id", "type": "column", "coltype": {"typmod": "", "typarray":
false, "typename": "serial", "schemaname": ""}, "default": null,
"storage": null, "not_null": null, "collation": null, "compression":
null, "identity_column": null, "generated_column": null}, {"fmt":
"%{name}I %{coltype}T%{storage}s%{compression}s%{collation}s%{not_null}s%{default}s%{identity_column}s%{generated_column}s",
"name": "name", "type": "column", "coltype": {"typmod": "",
"typarray": false, "typename": "text", "schemaname": "pg_catalog"},
"default": null, "storage": null, "not_null": null, "collation": null,
"compression": null, "identity_column": null, "generated_column":
null}]}, "partition_bound": null}}
NOTICE: deparsed DDL: CREATE TABLE public.test_tbl (id serial, name
pg_catalog.text)
CREATE TABLE

And setting test_ddl_deparse.execute_deparsed_ddl to on does the
round-trip test.

Regards,

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