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