Postgres Pro
Downloads
Home   >   mailing lists

Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded - Mailing list pgsql-bugs

From David G. Johnston
Subject Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded
Date
Msg-id CAKFQuwY3xQpwHR=XRJ8ntiM7B-PpygmTdf8oV7b2K8XWo9iT-A@mail.gmail.com
Whole thread Raw
In response to Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
Tree view
On Mon, Mar 21, 2022 at 3:24 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> I have an INSERT query that has an "ON CONFLICT" and a "WHERE" clause which
> suppose to hit a partial multicolumn unique index.

I'd say the answer is "don't do that".


Partial unique indexes are useful, no reason to assume that leveraging them for ON CONFLICT purposes would impose any sort of problem, and isn't it generally the project policy that different features work together in a sane manner?  That's all I see happening here on the development side even if there isn't a clear-cut use case for the pairing.

But yes, combining the partial index feature with parameters is a "not supported" combination presently.  One can infer as much from the existing documentation (as a whole) though it isn't obvious (I had a good inkling of what was going on here and still took probably an hour and source code dive to get my head around it enough to say I really understood what was going on.  And that was after reading the docs for ON CONLICT a couple of times...).
 
But if the WHERE clause doesn't provably imply the index predicate,
you lose.  And that means that no generic plan is even possible
if there's a parameter in that WHERE clause.

Correct.  Making this more obvious in the docs, and ideally the error message, would help.  The main question is whether to break queries that work today because of the optimization - the benefit being a reliable failure for when parameters are used in the predicate, we can just report that they are not allowed.


If I were tasked with "fixing" this, I'd fix it by rejecting partial
indexes as ON CONFLICT arbiters outright.  I'm not totally convinced
that that's safe at all, even in the simplest case.  It certainly
doesn't seem like something that's useful enough to expose this
sort of implementation detail for.


It's not like this happened by accident, it has its own clause to make it work.

index_predicate
Used to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows CREATE INDEX format. SELECT privilege on any column appearing within index_predicate is required.

I don't feel there is anything to fix here today.  But it seems like there is room for improvement, likely with not too much effort, if someone wants to improve things.  This being the first report of this nature I can recall seeing, and the size of our ToDo list, my expectations are low.

David J.


pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded
Next
From: Peter Geoghegan
Date:
Subject: Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded