Hi everyone!
A situation was discovered in which pg_upgrade fails when upgrading from
version
17 to 18 of PostgreSQL.
Reproduce:
1) Preparing the database for version 17:
--------------------------
DROP SCHEMA IF EXISTS xxi CASCADE;
CREATE SCHEMA xxi;
CREATE TABLE xxi.acc (
id int NOT NULL,
CONSTRAINT acc_pkey PRIMARY KEY(id)
);
CREATE TABLE xxi.trn (
acc_id int NOT NULL
) PARTITION BY RANGE (acc_id);
CREATE TABLE xxi.trn_part1 (
acc_id int NOT NULL
);
ALTER TABLE ONLY xxi.trn ATTACH PARTITION xxi.trn_part1
FOR VALUES FROM (1) TO (100);
ALTER TABLE ONLY xxi.trn_part1
ADD CONSTRAINT fk_trn_acc FOREIGN KEY (acc_id) REFERENCES xxi.acc(id)
NOT VALID;
ALTER TABLE xxi.trn
ADD CONSTRAINT fk_trn_acc FOREIGN KEY (acc_id) REFERENCES xxi.acc(id);
--------------------------
2) Run pg_upgrade from version 17 to 18. The restore step fails with:
--------------------------
...
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE "arseny_test"
pg_restore: connecting to new database "arseny_test"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE PROPERTIES "arseny_test"
pg_restore: connecting to new database "arseny_test"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating SCHEMA "xxi"
pg_restore: creating TABLE "xxi.acc"
pg_restore: creating TABLE "xxi.trn"
pg_restore: creating TABLE "xxi.trn_part1"
pg_restore: creating TABLE ATTACH "xxi.trn_part1"
pg_restore: creating CONSTRAINT "xxi.acc acc_pkey"
pg_restore: creating FK CONSTRAINT "xxi.trn fk_trn_acc"
pg_restore: creating FK CONSTRAINT "xxi.trn_part1 fk_trn_acc"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3277; 2606 16397 FK CONSTRAINT trn_part1
fk_trn_acc
arseny
pg_restore: error: could not execute query: ERROR: constraint "fk_trn_acc"
for relation "trn_part1" already exists
Command was: ALTER TABLE ONLY "xxi"."trn_part1" ADD CONSTRAINT "fk_trn_acc"
FOREIGN KEY ("acc_id") REFERENCES "xxi"."acc"("id") NOT VALID;
--------------------------
The issue is that pg_dump outputs both the FK on the parent table and
the FK on
the partition. During restore, the FK created on the parent is inherited
by the
partition, so the subsequent attempt to create the same-named FK on the
partition fails.
Is this a known issue or a bug in pg_dump?
Best regards,
Arseny Kositsin.
