Re: BUG #14526: no unique or exclusion constraint matching the ON CONFLICT

Tiago Babo <tiago(dot)babo(at)gmail(dot)com> writes:
> Hi, again. After some testing, I was able to reproduce this error with the following code:

> 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.

Hm. So the problem here is that the prepared statement only matches the
partial index as long as the actual parameter is substituted literally
into the statement. As soon as the plancache tries to consider a generic
plan, in which it's not apparent at plan time what $3 is, we can't prove
the partial index to be matched so you get the error.

If this is representative of what your application is actually doing,
rather than what you were saying it does, then the answer is that you
have to match the partial index clause exactly, not rely on substitution/
simplification to produce a match.

Or don't use a partial index. That schema seems pretty bizarre to me
anyway.

Having said all that, I think this is a fine example of why relying on
planner inferences for semantic decisions (rather than just optimization)
is damn-fool design. If I'd been paying closer attention I would have
objected loudly to the use of WHERE in ON CONFLICT for this purpose.
I wonder whether it's too late to deprecate that feature.

regards, tom lane