Re: BUG #14526: no unique or exclusion constraint matching the ON CONFLICT

On Wed, Feb 8, 2017 at 9:09 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Having said all that, I think this is a fine example of why relying on
> planner inferences for semantic decisions (rather than just optimization)
> is damn-fool design. If I'd been paying closer attention I would have
> objected loudly to the use of WHERE in ON CONFLICT for this purpose.
> I wonder whether it's too late to deprecate that feature.

I never imagined that somebody would want to parameterize constants in
the WHERE clause, because that's contrary to the idea that the
semantics are expressed explicitly from SQL. There may have been a
failure of imagination on my part about how that interacts with
certain driver libraries, but that doesn't invalidate the idea of
inference of partial indexes.

I do think that it's very valuable that inference supports partial
indexes. Back when I was an application developer, I extensively used
partial unique indexes to support "logical deletion" of records. There
were many unique indexes "WHERE NOT is_deleted" or similar. *Most*
unique constraints ended up being implemented this way. Since partial
indexes are necessarily never constraints as such, users cannot spell
out their name directly, and so inference of some form must be used.
ON CONFLICT would be totally closed off to an app that did that were
it not for inference of partial indexes, so it does seem very
important to me.

If you want to talk about making this less surprising, I think that
that would definitely need to be more nuanced than simply not
accepting a WHERE clause in the inference specification. I would
strongly object to that.

--
Peter Geoghegan