Peter Geoghegan <pg@bowt.ie> escreveu no dia terça, 7/02/2017 às 21:27:
On Tue, Feb 7, 2017 at 12:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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.
I must have misunderstood. ON CONFLICT accepts a WHERE clause as part of the inference specification itself entirely because that is sometimes useful.
> 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?
That's the simplest answer. Tiago?
There is only one place where I create accounts, so the query is always the same.
Here is an example where it gives the error (I excluded the created_at and updated_at columns in the last e-mail, but they do exist in the table):
INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 69559, '2017-02-03 12:09:27.259', '2017-02-03 12:09:27.259') ON CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *
SQL execution failed (Reason: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification)
