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

Tiago Babo <tiago.babo@gmail.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


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs