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_me...@mail.gmail.com [2]: https://postgr.es/m/CAN4CZFPBcRObk2sHJKidnuN7hJ_fG7QCdim%3DYnrN1sjSLFN68A%40mail.gmail.com
v1-0001-Don-t-use-deferrable-indexes-as-additional-ON-CON.patch
Description: Binary data
