Horton

Re: Allow NOT VALID foreign key constraints on partitioned tables.

Alexander Law <exclusion@gmail.com>

View thread View on postgr.es
From: Alexander Lakhin <exclusion@gmail.com>
To: Álvaro Herrera <alvherre@alvh.no-ip.org>, Amul Sul <sulamul@gmail.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2025-01-25T05:00:01Z
Lists: pgsql-hackers 
Hello Álvaro,

23.01.2025 17:04, Álvaro Herrera wrote:
> OK thanks, looks good, I have pushed it now with some trivial
> amendments.

Please look at the script that produces an error starting from b663b9436:
CREATE TABLE st (a int, primary key (a));
CREATE TABLE pt (a int,
   FOREIGN KEY (a) REFERENCES st ON DELETE SET NULL ON UPDATE SET NULL,
   FOREIGN KEY (a) REFERENCES st ON DELETE SET NULL ON UPDATE SET NULL
) PARTITION BY LIST (a);
CREATE TABLE tp1 PARTITION OF pt FOR VALUES IN (1, 2);
ALTER TABLE pt DETACH PARTITION tp1;
ALTER TABLE pt ATTACH PARTITION tp1 FOR VALUES IN (1, 2);

ERROR:  XX000: tuple already updated by self
LOCATION:  simple_heap_update, heapam.c:4374

Best regards,
Alexander Lakhin
Neon (https://neon.tech)

Commits

  1. Add missing CommandCounterIncrement

  2. Fix instability in recently added regression tests

  3. Allow NOT VALID foreign key constraints on partitioned tables

  4. Split ATExecValidateConstraint into reusable pieces