public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alvaro Herrera <[email protected]>
To: Tom Lane <[email protected]>
Cc: Tender Wang <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Jehan-Guillaume de Rorthais <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Guillaume Lelarge <[email protected]>
Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
Date: Sun, 10 Nov 2024 14:28:40 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

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)

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox