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

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;

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?

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;