From: Tom Lane <tgl@sss.pgh.pa.us>
To: yaser.amiri95@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded
Date: Mon, 21 Mar 2022 18:23:57 -0400
Message-ID: <2405214.1647901437@sss.pgh.pa.us> (raw)
In-Reply-To: <17445-fb74db6348391e85@postgresql.org>
References: <17445-fb74db6348391e85@postgresql.org>
PG Bug reporting form <noreply@postgresql.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, yaser.amiri95@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17445: "ON CONFLICT" has different behaviors when its param is passed with prepared stmt or hard coded
In-Reply-To: <2405214.1647901437@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox