Hi, I found that pg_restore --clean still reports errors when restoring a partitioned table with a PRIMARY KEY or UNIQUE constraint.
This appears to be the constraint-backed counterpart of the partitioned
index issue fixed by commit 1fc3403626d4:
Fix pg_dump --clean with partitioned indexes.
That commit suppresses the DROP statement in dumpIndex() when the index
is attached to a parent partitioned index. However, constraint-backed
indexes are handled through dumpConstraint(), which still generates DROP
CONSTRAINT unconditionally.
Here is a minimal reproducer:
createdb clean_src
psql -X -v ON_ERROR_STOP=1 clean_src <<'SQL'
CREATE TABLE p
(
id integer NOT NULL,
k integer NOT NULL,
payload text,
PRIMARY KEY (id, k)
) PARTITION BY LIST (k);
CREATE TABLE p1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE p2 PARTITION OF p FOR VALUES IN (2);
INSERT INTO p VALUES
(1, 1, 'one'),
(2, 2, 'two');
SQL
pg_dump -Fc -f partition.dump clean_src
createdb clean_dst
pg_restore -d clean_dst partition.dump
pg_restore --clean --if-exists --exit-on-error \
-d clean_dst partition.dump
The second restore fails with:
ERROR: cannot drop inherited constraint "p2_pkey" of relation "p2"
The failing command is:
ALTER TABLE IF EXISTS ONLY public.p2
DROP CONSTRAINT IF EXISTS p2_pkey;
Without --exit-on-error, the restore normally continues and rebuilds the
objects, but reports errors and exits nonzero. With --single-transaction
or --transaction-size, both of which imply --exit-on-error, the restore
is aborted.
The proposed fix applies the existing dumpIndex() rule to the
index-related constraint path in dumpConstraint():
if (indxinfo->parentidx == 0)
{
appendPQExpBuffer(delq, "ALTER %sTABLE ONLY %s ", foreign,
fmtQualifiedDumpable(tbinfo));
appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
fmtId(coninfo->dobj.name));
}
An inherited partition constraint cannot be dropped independently. It
will disappear when the parent constraint or the partition's table is
dropped, so its separate DROP command is unnecessary.
Checking parentidx, rather than tbinfo->ispartition, preserves DROP
statements for local constraints on partitions whose backing indexes are
not attached to a parent index. Constraint creation and index attachment
remain unchanged.
Earlier discussions of these cleanup failures include:
https://postgr.es/m/[email protected]
https://postgr.es/m/[email protected]
https://postgr.es/m/[email protected]
The discussion leading to the partitioned index fix is here:
https://postgr.es/m/CACJufxF0QSdkjFKF4di-JGWN6CSdQYEAhGPmQJJCdkSZtd=o...@mail.gmail.com
While testing this change, I also found a related failure involving a
local constraint whose backing index is attached to a partitioned index
without a corresponding parent constraint. In that case, dropping the
parent index fails because of the child constraint's dependency. This
failure also occurs without the proposed patch.
I plan to report that case in a separate thread, where we can discuss
its cleanup behavior and a follow-up fix.
Comments welcome.
--
Best Regards,
Teletele
v1-0001-Fix-pg_dump-clean-with-inherited-partition-constr.patch
Description: Binary data
