Hello,
While testing ON CONFLICT on partitioned tables on master, I found
that a deferrable unique constraint on a leaf partition breaks every
routed insert that takes the no-conflict path. This is a regression
from commit 90eae926abbb (Fix ON CONFLICT with REINDEX CONCURRENTLY
and partitions[1]), so it affects master and the 19 betas, but not 18.
This is one of the issues I found with cross-checking feature
interactions with Claude[2], and I thoght I'll submit this first since
this is a PG19 regression.
Reproducer:
CREATE TABLE d (a int, b text, PRIMARY KEY (a)) PARTITION BY RANGE (a);
CREATE TABLE d1 PARTITION OF d FOR VALUES FROM (0) TO (100);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
INSERT INTO d VALUES (1, 'one');
-- works: conflict found on d1_pkey
INSERT INTO d VALUES (1, 'ONE') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
-- ERROR: ON CONFLICT does not support deferrable unique
-- constraints/exclusion constraints as arbiters
INSERT INTO d VALUES (2, 'two') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
INSERT INTO d VALUES (3, 'three') ON CONFLICT (a) DO NOTHING;
All four inserts succeed on 18.
Since 90eae926abbb, ExecInitPartitionInfo matches every leaf index
that has no parent against the arbiters mapped from the root.
IsIndexCompatibleAsArbiter compares several properties, but not
indimmediate.
The loop returns early, so statements might work or fail based on how
the table/index was created, which suggests an unintended oversight,
not an intentional change.
For example if I just slightly modify the above repro, the previously
successful insert also fails:
CREATE TABLE d (a int, b text, PRIMARY KEY (a)) PARTITION BY RANGE (a);
CREATE TABLE d1 (a int NOT NULL, b text);
ALTER TABLE d1 ADD CONSTRAINT d1_a_def UNIQUE (a) DEFERRABLE;
ALTER TABLE d ATTACH PARTITION d1 FOR VALUES FROM (0) TO (100);
INSERT INTO d VALUES (1, 'one');
-- ERROR (worked in the first setup)
INSERT INTO d VALUES (1, 'ONE') ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b;
If instead I modify the first snippet to make the index also NULLS NOT
DISTINCT, all 4 inserts succeed.
The attached patch adds the missing indimmediate comparison and
restores the PG18 and earlier behavior.
[1]: https://postgr.es/m/CANtu0ojXmqjmEzp-=aJSxjsdE76iAsRgHBoK0QtYHimb_mEfsg@mail.gmail.com
[2]: https://postgr.es/m/CAN4CZFPBcRObk2sHJKidnuN7hJ_fG7QCdim%3DYnrN1sjSLFN68A%40mail.gmail.com
