Database Administrators Asked on October 28, 2021
I have an updatable view pointing to an underlying table that has a partial index. It looks something like this
CREATE TABLE if not exists foo (
a INT,
b INT,
x INT,
y INT,
z BOOLEAN,
CONSTRAINT x_or_y CHECK (
(z and x is not null and y is null)
or
(not z and x is null and y is not null)
)
);
CREATE UNIQUE INDEX ux ON foo (x, a) WHERE z=TRUE;
CREATE UNIQUE INDEX uy ON foo (y, a) WHERE z=FALSE;
CREATE OR REPLACE VIEW foo_view AS
SELECT * FROM foo;
That is, for each row, y
must be null if z
is true; x
must be null if z
is false; and, only one of x
and y
may be not null. (x, a)
and (y, a)
must be unique. (Sorry if this is overly complicated. I’m translating from my real table that has a lot of other cruft.)
My problem comes when I want to update with ON CONFLICT
. I believe I ought to be able to do this.
INSERT INTO foo_view(x, y, a, b, z)
VALUES
(5, null, 1, 2, true),
(null, 5, 1, 2, false);
select * from foo_view;
INSERT INTO foo_view(x, y, a, b, z)
VALUES
(5, null, 1, 2, true)
ON CONFLICT (x, a) where z=true
DO UPDATE
set b = EXCLUDED.b;
But, I get the exception:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
I can insert into foo
instead of foo_view
with the same ON CONFLICT
without error.
Here is a fiddle: https://www.db-fiddle.com/f/cX2HXg91Q7yKoPeMBYzVLg/0
After debugging through the PostgreSQL code, I'd say that there is a problem in the infer_arbiter_indexes
function in src/backend/optimizer/util/plancat.c
. It compares the index predicate to the WHERE
condition in ON CONFLICT
, but seems to ignore that they are on different objects.
I'm not sure if PostgreSQL is willing to consider that as a bug, but you might report it.
Answered by Laurenz Albe on October 28, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP