On Sun, Nov 13, 2022 at 1:07 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
