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@sss.pgh.pa.us> wrote:
> I'd say the answer is "don't do that".

This isn't the first complaint about this exact behavior (including
the detail about it sometimes working at random), FWIW:

https://www.postgresql.org/message-id/20170202140701.1401.31196%40wrigleys.postgresql.org

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

I think that it depends on what you expect. Offhand I can't think of
any problem scenarios that cannot be simplified to a test case that
doesn't involve a partial unique index. (Except for this one, that
is.)

> It certainly
> doesn't seem like something that's useful enough to expose this
> sort of implementation detail for.

Back when I was an application developer, I used partial unique
indexes quite a bit. My sense is that supporting them in ON CONFLICT
has real value, even if the syntax for that needs to be messy.

That being said, I *don't* think that it makes sense to support
paramaters in conflict_target's WHERE clause -- that should be static,
not dynamic. ISTM that it would be somewhat useful to have that case
throw an error, in an immediate and obvious way, with an accompanying
HINT.

-- 
Peter Geoghegan