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

Tiago Babo <tiago(dot)babo(at)gmail(dot)com> writes:
> Ups, I used the wrong database. I was experimenting with it a bit and didn’t noticed I was copying from the wrong one. Where is the correct version (and the one that is giving me those “random” errors):

> Indexes:
> "accounts_pkey" PRIMARY KEY, btree (id)
> "index_accounts_on_type_and_identifier" UNIQUE, btree (type, identifier)
> "uniq_bank_accounts" UNIQUE, btree (type, identifier) WHERE type::text = 'BankAccount'::text
> "uniq_business_accounts" UNIQUE, btree (type, business_id) WHERE type::text = 'BusinessAccount'::text
> "uniq_person_accounts" UNIQUE, btree (type, person_id) WHERE type::text = 'PersonAccount'::text
> "index_accounts_on_business_id" btree (business_id)
> "index_accounts_on_person_id" btree (person_id)

Hm. I looked at infer_arbiter_indexes, which is the place where this
particular error is thrown, and realized that my previous assertion
was wrong: it *does* try to prove applicability of partial indexes
based on the ON CONFLICT WHERE clause. So actually it should be
deciding that uniq_person_accounts is a usable unique index --- at
least, if you always have "WHERE type = 'PersonAccount'" in the
ON CONFLICT clause. Maybe you're sometimes leaving that out?

(BTW, I would say that uniq_bank_accounts is absolutely not worth its keep
given that you have a non-partial unique index on the same two columns.
But that seems not very relevant to the current complaint.)

regards, tom lane