Re: [BUGS] 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
continuouslyget the error. I'm using a Scala library called ScalikeJDBC to access the database. So maybe the problem is
onhow it handles the execution.  

> On 8 Feb 2017, at 17:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Tiago Babo <tiago.babo@gmail.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


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