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

This is not how my application is doing the insert (like I showed you before), but it was the only way I could continuously get the error. I'm using a Scala library called ScalikeJDBC to access the database. So maybe the problem is on how it handles the execution.

> On 8 Feb 2017, at 17:09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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