Re: BUG #14526: no unique or exclusion constraint matching the ON 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(at)bowt(dot)ie> wrote:
>
> On Tue, Feb 7, 2017 at 5:41 PM, Peter Geoghegan <pg(at)bowt(dot)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