Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements - Mailing list pgsql-hackers
| From | Henson Choi |
|---|---|
| Subject | Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements |
| Date | |
| Msg-id | CAAAe_zD5LSqw29MLYcQrDD-9v5q7me1d5KXpxi3td0J6aXg7jg@mail.gmail.com Whole thread |
| In response to | Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements (Akshay Joshi <akshay.joshi@enterprisedb.com>) |
| List | pgsql-hackers |
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
> 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
pgsql-hackers by date: