On Sun, Nov 13, 2022 at 1:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> A WHERE placed there is an index_predicate attachment to the ON CONFLICT
> clause. It doesn't have any run-time effect other than to allow partial
> indexes to be chosen as arbiter indexes. TFM explains
>
> index_predicate
>
> Used to allow inference of partial unique indexes. Any indexes
> that satisfy the predicate (which need not actually be partial
> indexes) can be inferred.
>
> This strikes me as a bit of a foot-gun. I wonder if we should make
> it safer by insisting that the resolved index be partial when there's
> a WHERE clause here.
I don't think that it would be safer.
Adrian has asked why it's possible to attach an arbitrary
index_predicate type WHERE clause to an ON CONFLICT query, without
that really changing the behavior of the statement. That *is* a little
odd, so it's certainly a fair question (I can recall perhaps as many
as 5 similar questions over the years). But it's not the end of the
world, either -- there are far worse things.
I think that it would be a lot worse (just for example) to have your
ON CONFLICT query suddenly start throwing an ERROR in production, just
because you replaced a partial unique index with a unique constraint.
If we have a suitable unique index or constraint, why wouldn't we use
it in ON CONFLICT? Maybe it won't work out that way (maybe there won't
be any suitable unique index or constraint), but why not do our utmost
to insulate the user from what might be a serious production issue?
That was the guiding principle.
Overall I'm quite happy with the amount of foot-guns ON CONFLICT has,
especially compared to other comparable features in other DB systems
(which had plenty). There are one or two ostensibly odd things about
the syntax that are downstream consequences of trying to make the
constraint/unique index inference process maximally forgiving. I'm
pretty happy with that trade-off.
> (This documentation text is about as clear as
> mud, too. What does "inferred" mean here? I think it means "chosen as
> arbiter index", but maybe I misunderstand.)
Unique index/constraint inference is the process by which we choose an
arbiter index. See the second paragraph of the "ON CONFLICT Clause"
section of the INSERT docs.
--
Peter Geoghegan