| From: | Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com> |
|---|---|
| To: | assam258(at)gmail(dot)com |
| Cc: | Rui Zhao <zhaorui126(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements |
| Date: | 2026-11-18 08:32:40 |
| Message-ID: | CANxoLDevGz37u66aLjaJkC31F+Xi=kOeT==6GPuv=ni=GBqgzQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Thank you for testing v26 and for the detailed reproduction. You were right
on all points. The change I made to the shared
pg_get_constraintdef_worker() for pg_get_table_ddl() also changed the
output of pg_get_constraintdef(), and through it pg_dump, pg_restore, psql
\d and the ALTER TABLE ... ALTER COLUMN TYPE path.
I also found a related problem for PRIMARY KEY and UNIQUE constraints, WITH
(...) and USING INDEX TABLESPACE were emitted only when fullCommand was set
on master. I had made them unconditional, so pg_get_constraintdef() output
changed for those constraints too.
*What v27 changes*:
- The worker takes a new index_opts argument. Only
pg_get_constraintdef_body(), which pg_get_table_ddl() uses, sets it. It
still honours the tablespace => false option.
- pg_get_constraintdef() and pg_get_constraintdef_ext() now return what
master returns, for both EXCLUDE and PK/UNIQUE. That means pg_dump
(including --no-tablespaces), pg_restore and psql \d are back to master
behavior. psql shows the tablespace once, and a restore no longer fails
when the tablespace is missing.
- pg_get_constraintdef_command() also matches master. PK/UNIQUE keep WITH
and USING INDEX TABLESPACE, and EXCLUDE does not.
- That last point means an EXCLUDE index rebuilt by ALTER COLUMN TYPE goes
to the default tablespace, as on master. I restored master's behavior to
keep this patch focused. I think it is arguably a separate pre-existing
issue and could be handled as its own patch.
*Tests added*:
- tablespace.sql: an EXCLUDE and a UNIQUE ... WITH (fillfactor) constraint
in a non-default tablespace, checking:
- pg_get_constraintdef(oid) and pg_get_constraintdef(oid, true), with no
tablespace clause;
- \d, with the tablespace shown once;
- the index's tablespace after ALTER COLUMN TYPE.
- pg_get_table_ddl.sql: an EXCLUDE constraint in a tablespace, checking
that the clause is emitted by default and suppressed with tablespace =>
false.
The *v27* patch is ready for review/test.
On Fri, Oct 2, 2026 at 9:23 AM Henson Choi <assam258(at)gmail(dot)com> wrote:
> Hi Akshay,
>
> > The *v26* patch is ready for review/test.
>
> Thanks for the new version. While testing v26 I found a regression
> in pg_dump. pg_get_constraintdef() now returns "USING INDEX
> TABLESPACE ..." for EXCLUDE constraints, and pg_dump uses that output
> verbatim for EXCLUDE constraints, so "pg_dump --no-tablespaces" no
> longer omits the tablespace.
>
> Reproduction
>
> initdb -D data
> pg_ctl -D data -l data.log start
> mkdir /tmp/xts
>
> psql postgres
> CREATE TABLESPACE xts LOCATION '/tmp/xts';
> CREATE TABLE t (a int,
> CONSTRAINT ex_a EXCLUDE USING btree (a WITH =)
> USING INDEX TABLESPACE xts);
> SELECT pg_get_constraintdef(oid) FROM pg_constraint
> WHERE conname = 'ex_a';
>
> pg_dump --no-tablespaces postgres > dump.sql
> grep "ADD CONSTRAINT" dump.sql
>
> # drop the tablespace, then restore into a new database
> psql postgres -c "DROP TABLE t" -c "DROP TABLESPACE xts"
> createdb restored
> psql restored -f dump.sql
>
> Results
>
> pg_get_constraintdef():
> master: EXCLUDE USING btree (a WITH =)
> patched: EXCLUDE USING btree (a WITH =)
> USING INDEX TABLESPACE xts
>
> ADD CONSTRAINT line in dump.sql:
> master: ... EXCLUDE USING btree (a WITH =);
> patched: ... EXCLUDE USING btree (a WITH =)
> USING INDEX TABLESPACE xts;
>
> restoring dump.sql (after xts is gone):
> master: succeeds
> patched: psql:dump.sql:48: ERROR: tablespace "xts" does not
> exist
>
> A plain dump (without --no-tablespaces) emits both SET
> default_tablespace and the explicit clause. That is only redundant
> when xts exists on the target. When it does not, master fails just
> the SET and still creates the constraint, while v26 also fails the
> ADD CONSTRAINT and the constraint is lost. pg_get_table_ddl() with
> "tablespace => false" suppresses the clause correctly, so the new
> function behaves as intended. The cause is the change made in the
> shared helper pg_get_constraintdef_worker() for pg_get_table_ddl(),
> which also changes pg_get_constraintdef() and, through it, pg_dump.
>
> Callers
>
> The helper has several callers, so it may be worth going through
> each of them to decide what output it should get. The ones I found
> in the tree:
>
> - pg_dump: for EXCLUDE constraints it uses the output of
> pg_get_constraintdef(oid, false) verbatim in ADD CONSTRAINT. It
> passes the tablespace separately through SET default_tablespace,
> which --no-tablespaces suppresses (the problem above).
> - pg_restore: --no-tablespaces on an archive likewise skips only
> SET default_tablespace, so the clause inside the archived
> ADD CONSTRAINT still creates the index in xts.
> - psql \d: the Indexes section uses pg_get_constraintdef(oid, true)
> for EXCLUDE and WITHOUT OVERLAPS constraints and appends
> ', tablespace "xts"' itself, so those constraints now show the
> tablespace twice.
> - ALTER TABLE ... ALTER COLUMN TYPE: rebuilds the constraint with
> pg_get_constraintdef_command(). When the index is rebuilt (e.g.
> TYPE bigint), on master it moves to the default tablespace; with
> the patch it stays in xts.
> - pg_get_table_ddl() itself, through pg_get_constraintdef_body().
>
> Tests that exercise these callers with an EXCLUDE constraint in a
> non-default tablespace would help.
>
> I found this while investigating deparse defects, so I have not
> reviewed the whole patch.
>
> Best regards,
> Henson
>
| Attachment | Content-Type | Size |
|---|---|---|
| v27-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch | application/x-patch | 326.5 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | vignesh C | 2026-11-18 08:33:51 | Re: Hash index: stop registering pin-only bucket pages in WAL (remove REGBUF_NO_CHANGE) |
| Previous Message | Jim Jones | 2026-11-18 08:26:56 | Fix ISSN output truncation when the issue code is nonzero |