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

On Mon, Mar 21, 2022 at 3:24 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)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.