Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

On Tue, Jul 14, 2026 at 4:31 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
wrote:

> Hello!
>
> I did another big sweep/testing round. These are the issues found during
> it:
>
> 1. CLUSTER ON is emitted before ALTER TABLE ADD CONSTRAINT, and fails:
>
> CREATE TABLE clu (a int, b int);
> ALTER TABLE clu ADD CONSTRAINT clu_pk PRIMARY KEY (a);
> CLUSTER clu USING clu_pk;
>
> SELECT stmt FROM pg_get_table_ddl('clu', owner => false) stmt;
>
> 2. CONSTRAINT using index / tablespace dropped:
>
> CREATE TABLE pko (a int, b int);
> ALTER TABLE pko ADD CONSTRAINT pko_pk PRIMARY KEY (a) WITH
> (fillfactor=50) USING INDEX TABLESPACE ts1;
> ALTER TABLE pko ADD CONSTRAINT pko_u UNIQUE (b) WITH (fillfactor=60);
>
> SELECT stmt FROM pg_get_table_ddl('pko', owner => false) stmt;
> -- ALTER TABLE public.pko ADD CONSTRAINT pko_pk PRIMARY KEY (a);
>
> 3. SET STATISTICS is skipped:
>
> CREATE TABLE cst (a int, b text);
> ALTER TABLE cst ALTER COLUMN a SET STATISTICS 500;
> ALTER TABLE cst ALTER COLUMN b SET STATISTICS 0;
>
> SELECT stmt FROM pg_get_table_ddl('cst', owner => false) stmt;
>
> and
>
> CREATE TABLE stx (a int, b int);
> CREATE STATISTICS stx_s (dependencies) ON a, b FROM stx;
> ALTER STATISTICS stx_s SET STATISTICS 750;
>
> SELECT stmt FROM pg_get_table_ddl('stx', owner => false) stmt;
>
> 4. toast.* parameteres also:
>
> CREATE TABLE tst (a int, b text)
> WITH (toast.autovacuum_enabled = false, fillfactor = 70);
>
> SELECT stmt FROM pg_get_table_ddl('tst', owner => false) stmt;
>
> 5. STORAGE / COMPRESSION overrides on inherited columns are skipped:
>
> CREATE TABLE ps (a int, b text) PARTITION BY RANGE (a);
> CREATE TABLE ps_1 PARTITION OF ps FOR VALUES FROM (0) TO (10);
> ALTER TABLE ps_1 ALTER COLUMN b SET STORAGE EXTERNAL;
> ALTER TABLE ps_1 ALTER COLUMN b SET COMPRESSION lz4;
>
> SELECT stmt FROM pg_get_table_ddl('ps', owner => false) stmt;
>
> 6. Is it okay to error out on foreign table partitions, wouldn't a
> warning and skipping it be better?
>
> CREATE EXTENSION IF NOT EXISTS postgres_fdw;
> CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw;
> CREATE TABLE fpar (a int) PARTITION BY RANGE (a);
> CREATE TABLE fpar_1 PARTITION OF fpar FOR VALUES FROM (0) TO (10);
> CREATE FOREIGN TABLE fpar_2 PARTITION OF fpar FOR VALUES FROM (10) TO
> (20) SERVER loopback;
>
> SELECT stmt FROM pg_get_table_ddl('fpar') stmt;
>
> It works with except_only partitions, but then it won't show any
> partitions at all.
>
> 7. NOT NULL NOT VALID loses NOT VALID:
>
> CREATE TABLE nnv (a int, b int);
> ALTER TABLE nnv ADD CONSTRAINT nnv_a_nn NOT NULL a NOT VALID;
>
> SELECT stmt FROM pg_get_table_ddl('nnv', owner => false) stmt;
>
> 8. custom index names are skipped:
>
> CREATE TABLE cc (a int) PARTITION BY RANGE (a);
> CREATE TABLE cc_1 PARTITION OF cc FOR VALUES FROM (0) TO (10);
> CREATE INDEX ON ONLY cc (a);
> CREATE INDEX cc_custom ON cc_1 (a);
> ALTER INDEX cc_a_idx ATTACH PARTITION cc_custom;
> ALTER TABLE cc_1 CLUSTER ON cc_custom;
> SELECT stmt FROM pg_get_table_ddl('cc', owner => false) stmt;
>
> And because of that replay fails, as CLUSTER ON refers to it.
>
> 9. explicit access method on partitioned parent is skipped:
>
> CREATE TABLE pam (a int) PARTITION BY RANGE (a);
> ALTER TABLE pam SET ACCESS METHOD heap;
>
> SELECT stmt FROM pg_get_table_ddl('pam', owner => false) stmt;
>
> 10. tablespace => false doesn't remove tablespace from CREATE INDEX:
>
> CREATE TABLE tsp (a int, b point) TABLESPACE ts1;
> CREATE INDEX tsp_gist ON tsp USING gist (b) TABLESPACE ts1;
>
> SELECT stmt FROM pg_get_table_ddl('tsp', owner => false, tablespace =>
> false) stmt;
>
> 11. ALTER SEQUENCE is skipped:
>
> CREATE TABLE idt (a int GENERATED BY DEFAULT AS IDENTITY);
> ALTER SEQUENCE idt_a_seq AS smallint;
>
> SELECT stmt FROM pg_get_table_ddl('idt', owner => false) stmt;
>
> and
>
> CREATE TABLE idl (a int GENERATED ALWAYS AS IDENTITY);
> ALTER SEQUENCE idl_a_seq SET UNLOGGED;
>
> SELECT stmt FROM pg_get_table_ddl('idl', owner => false) stmt;
>

Fixed all the preceding issues.

>
> 12. ALTER INDEX .. SET STATISTICS on indexes is skipped:
>
> CREATE EXTENSION IF NOT EXISTS btree_gist;
> CREATE TABLE exs (a int, b int);
> ALTER TABLE exs ADD CONSTRAINT exs_ex EXCLUDE USING gist ((a + b) WITH =);
> ALTER INDEX exs_ex ALTER COLUMN 1 SET STATISTICS 900;
>
> SELECT stmt FROM pg_get_table_ddl('exs', owner => false) stmt;
>
> Interestingly pg_dump also skips this, that looks like a pg_dump bug?
>
The behavior was indeed inconsistent with what emit_indexes() already
did for non-constraint indexes. emit_local_constraints() now emits ALTER
INDEX ... ALTER COLUMN n SET STATISTICS for PK/UNIQUE/EXCLUSION constraint
backing indexes.

>
> 13. SECURITY LABEL skipped, this probably only needs a documentation
> mention?
>
> LOAD 'dummy_seclabel';
> CREATE TABLE secl (a int);
> SECURITY LABEL FOR dummy ON TABLE secl IS 'classified';
> SECURITY LABEL FOR dummy ON COLUMN secl.a IS 'unclassified';
>
> SELECT stmt FROM pg_get_table_ddl('secl', owner => false) stmt;
>

*W**on't fix, doc-only*: SECURITY LABEL requires a specific security-label
provider (extension) to be loaded before the label can be applied.
pg_get_table_ddl() has no way to know which provider will be present at
replay time, and emitting DDL that references an unloaded provider would
cause replay errors. This is analogous to the existing trigger and policy
limitations. It should be documented as a known limitation: the generated
DDL is complete for everything the server can reconstruct without
provider-specific knowledge, and security labels should be noted as manually
required.

The *v21* patch is ready for review.