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;
