Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

On 2024-Nov-08, Tom Lane wrote:

> Hmm ... interestingly, if I run this in HEAD's regression database,
> I get
>
> constrained table | constraint | references
> -------------------+---------------+-------------
> clstr_tst | clstr_tst_con | clstr_tst_s
> (1 row)

Eeek.

> So it looks like this query needs a guard to make it ignore
> constraints on traditional-inheritance tables.

Hmm, looks tricky, the only thing I found was to only consider rows in
pg_inherit if there's a corresponding one in pg_partitioned_table. This
should do it. I added the DROP/ADD commands. I also added some
pg_catalog schema quals, though that may be kinda useless. Anyway, this
reports empty in the regression database.

SELECT conrelid::pg_catalog.regclass AS "constrained table",
conname AS constraint,
confrelid::pg_catalog.regclass AS "references",
pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;', conrelid::regclass, conname),
pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;', conrelid::regclass, conname,
pg_catalog.pg_get_constraintdef(oid))
FROM pg_catalog.pg_constraint
WHERE contype = 'f' and conparentid = 0 AND
(SELECT count(*) FROM pg_catalog.pg_constraint p2 WHERE conparentid = pg_constraint.oid) <>
(SELECT count(*)
FROM pg_catalog.pg_inherits
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table WHERE partrelid = inhparent) AND
inhparent = pg_constraint.conrelid OR inhparent = pg_constraint.confrelid)
;

I would have loved to be able to add the constraint as NOT VALID
followed by a separate VALIDATE command, because if there are any RI
violations, the constraint would now be in place to prevent future ones.
However,

=# ALTER TABLE fk2367 ADD CONSTRAINT fk_a_fkey FOREIGN KEY (a) REFERENCES pk(a) NOT VALID;
ERROR: cannot add NOT VALID foreign key on partitioned table "fk2367" referencing relation "pk"
DETAIL: This feature is not yet supported on partitioned tables.

So it looks like we should suggest to save the output of the query,
execute each DROP followed by each ADD, and if the latter fails, fix the
violations and retry the ADD.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Java is clearly an example of money oriented programming" (A. Stepanov)