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 2:11 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17445
> Logged by: Yaser Amiri
> Email address: yaser(dot)amiri95(at)gmail(dot)com
> PostgreSQL version: 14.2
> Operating system: openSUSE Leap 15.3 / Kernel: 5.3.18-150300.59.49-d
> Description:
>
> Hi,
> I think I found a bug or at least a confusing behavior that I can't find
> any
> documentation about.
>
> Summary:
> I have an INSERT query that has an "ON CONFLICT" and a "WHERE" clause which
> suppose to hit a partial multicolumn unique index.
> When I try to insert some records in a transaction (one insert per record)
> When I pass the parameter which is used in the "WHERE" condition in
> prepared
> statement params, the transaction fails, but if I hard code it, everything
> will be OK (no rollback or errors).
> Error: `ERROR: there is no unique or exclusion constraint matching the ON
> CONFLICT specification`
>

IIUC, and this could probably be spelled out a bit better in the
documentation, the inference of the arbiter index is chosen at planning
time. IOW, each row doesn't get to decide which index it is checked
against. This is a general behavior of the system and doesn't get called
out in individual sections like the INSERT command. I would suggest we
make an exception here and explicitly note that inference happens during
planning and so does not play well with parameters (which only matters in
the partial index case anyway). I think we could even go further and emit
a more useful error message but the technical details of that are outside
my skill set.

What that all means is the system doesn't really support your example -
though it doesn't reliably fail either due to planner optimizations.

For the partial index predicate: WHERE "time" > 'some known point in
time'::timestamp

The plan predicate:

WHERE "time" > $# (where $# is only known at execution time)

Is not a valid inference choice since the value of $# could very well be "<
'some known point in time'" thus making the partial index unsuitable.

> Here's another weird thing, in the situation in which transaction fails, If
> I reduce the inserts to less than 6, it doesn't fail! (It fails on 6th
> execution)
>

This is to be expected given how prepared statements and the planner
interact. Some queries perform better overall if the generic parameterized
plan is ignored and a hard-coded plan using the known parameter values is
used instead. In that case the plan predicate changes to:

WHERE "time" > 'the actual value of $# during this execution'

And so long as that actual value is ">= 'some known point in time'" the
partial index will be used. You will still get the observed failure if the
supplied value for $# is "< 'some known point in time'" though...

David J.