Re: [BUGS] BUG #14526: no unique or exclusion constraint matching theON CONFLICT

Hi, again. After some testing, I was able to reproduce this error with the following code:

CREATE TABLE test (
  type character varying,
  id integer
);

CREATE UNIQUE INDEX uniq_id_test ON test USING btree (type, id) WHERE (type = 'Test');

PREPARE test (text, int, text) AS
  INSERT INTO test (type, id)
  VALUES ($1, $2)
  ON CONFLICT (type, id) WHERE type = $3 DO UPDATE SET id = EXCLUDED.id;


EXECUTE test('Test', 1, 'Test');
EXECUTE test('Test', 2, 'Test');
EXECUTE test('Test', 3, 'Test');
EXECUTE test('Test', 4, 'Test');
EXECUTE test('Test', 5, 'Test');
EXECUTE test('Test', 6, 'Test’);

It gives the error when trying to execute the last statement.

> On 8 Feb 2017, at 01:48, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Feb 7, 2017 at 5:41 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> It won't work with deferrable constraints (even when immediate
>> enforcement is in effect, so obscure reasons). Enforcement occurs in
>> the executor -- see ExecCheckIndexConstraints().
>
> Note also that it needs to happen in the executor, because
> infer_arbiter_indexes() may return immediately when ON CONFLICT DO
> NOTHING is used without the user specifying which particular
> constraint to use as an arbiter.  (This is forbidden with ON CONFLICT
> DO UPDATE, since it doesn't make sense to not have an arbiter in mind
> there.)
>
> This is actually noted directly within infer_arbiter_indexes(), about
> half way down:
>
> /*
> * Extract info from the relation descriptor for the index.  We know
> * that this is a target, so get lock type it is known will ultimately
> * be required by the executor.
> *
> * Let executor complain about !indimmediate case directly, because
> * enforcement needs to occur there anyway when an inference clause is
> * omitted.
> */
>
> --
> Peter Geoghegan



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs