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

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

Personally, I'm quite surprised that even your base case works.
I thought that ON CONFLICT would resolve which index to use
long before considering any WHERE clauses. Apparently, that
happens late enough that the planner has determined which partial
indexes' predicates are provably true for the query, so the
partial unique index becomes a candidate to use in ON CONFLICT.
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.

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.

regards, tom lane