TransWikia.com

Cannot use "ON CONFLICT" with postgres updatable view and partial index

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

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP