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

On Thu, Feb 2, 2017 at 6:07 AM, <tiago.babo@gmail.com> wrote:

INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON
CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET
updated_at = EXCLUDED.updated_at RETURNING *

I also have an unique INDEX:

CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type,
person_id) WHERE ((type)::text = 'PersonAccount'::text);

Can you show the table definition? From psql, "\d+ accounts"

--
Peter Geoghegan

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

Here it goes:

Table "public.accounts"
Column | Type | Modifiers | Storage | Stats target | Description
----------------------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('accounts_id_seq'::regclass) | plain | |
type | character varying | | extended | |
identifier | character varying | | extended | |
person_id | integer | | plain | |
business_id | integer | | plain | |
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 (person_id) WHERE type::text = 'PersonAccount'::text
"index_accounts_on_business_id" btree (business_id)
"index_accounts_on_person_id" btree (person_id)
Foreign-key constraints:
"fk_rails_156241f05a" FOREIGN KEY (business_id) REFERENCES businesses(id)
"fk_rails_777d10a224" FOREIGN KEY (person_id) REFERENCES persons(id)

On 7 Feb 2017, at 09:03, Peter Geoghegan <pg@bowt.ie> wrote:

On Thu, Feb 2, 2017 at 6:07 AM, <tiago.babo@gmail.com> wrote:

INSERT INTO accounts (type, person_id) VALUES ('PersonAccount', 1) ON
CONFLICT (type, person_id) WHERE type = 'PersonAccount' DO UPDATE SET
updated_at = EXCLUDED.updated_at RETURNING *

I also have an unique INDEX:

CREATE UNIQUE INDEX uniq_person_accounts ON accounts USING btree (type,
person_id) WHERE ((type)::text = 'PersonAccount'::text);

Can you show the table definition? From psql, "\d+ accounts"

--
Peter Geoghegan

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

Tiago Babo <tiago.babo@gmail.com> writes:

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 (person_id) WHERE type::text = 'PersonAccount'::text
"index_accounts_on_business_id" btree (business_id)
"index_accounts_on_person_id" btree (person_id)

So according to that, you *don't* have a unique index over (type, person_id).
(A sufficiently clever person might realize that the partial index on
person_id would serve in this instance, but I do not expect that Postgres
would figure that out.)

That makes the question less about why it fails and more about why it
seems to sometimes work. It shouldn't, at least not with this set of
indexes and this query.

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

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):

Column | Type | Modifiers | Storage | Stats target | Description
----------------------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('accounts_id_seq'::regclass) | plain | |
type | character varying | | extended | |
identifier | character varying | | extended | |
person_id | integer | | plain | |
business_id | integer | | plain | |
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)
Foreign-key constraints:
"fk_rails_156241f05a" FOREIGN KEY (business_id) REFERENCES businesses(id)
"fk_rails_777d10a224" FOREIGN KEY (person_id) REFERENCES persons(id)

On 7 Feb 2017, at 18:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Tiago Babo <tiago.babo@gmail.com> writes:

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 (person_id) WHERE type::text = 'PersonAccount'::text
"index_accounts_on_business_id" btree (business_id)
"index_accounts_on_person_id" btree (person_id)

So according to that, you *don't* have a unique index over (type, person_id).
(A sufficiently clever person might realize that the partial index on
person_id would serve in this instance, but I do not expect that Postgres
would figure that out.)

That makes the question less about why it fails and more about why it
seems to sometimes work. It shouldn't, at least not with this set of
indexes and this query.

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

On Tue, Feb 7, 2017 at 10:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

That makes the question less about why it fails and more about why it
seems to sometimes work. It shouldn't, at least not with this set of
indexes and this query.

Agreed.

Log output from Tiago's system, with debug_print_parse = on,
debug_print_plan = on, and debug_print_rewritten = on might tell us
some more. If Tiago can enable those at a time that catches the
successful execution of the query (where inference mysteriously
works), we'd have a good chance of understanding what's up. (This is
probably something to be done quite selectively in production.)

--
Peter Geoghegan

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

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

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?

--
Peter Geoghegan

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

Thanks for your replies.

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)

On Tue, Feb 7, 2017 at 2:15 PM, Tiago Babo <tiago.babo@gmail.com> wrote:

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 *

Can you give an example of where it does actually work? Is there any
discernible pattern to what each case looks like?

--
Peter Geoghegan

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

The only difference (so it seems), is that when it crashes, the person was created in the seconds before creating the account. When it previously exists in the persons table, it never gives the error.

Working example:

INSERT INTO accounts (type, person_id, created_at, updated_at)
VALUES (‘PersonAccount', 137842, '2017-02-07 23:17:37.61', '2017-02-07 23:17:37.61')
ON CONFLICT (type, person_id) WHERE type = ‘PersonAccount' DO UPDATE SET updated_at = EXCLUDED.updated_at RETURNING *

On 7 Feb 2017, at 22:17, Peter Geoghegan <pg@bowt.ie> wrote:

On Tue, Feb 7, 2017 at 2:15 PM, Tiago Babo <tiago.babo@gmail.com> wrote:

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 *

Can you give an example of where it does actually work? Is there any
discernible pattern to what each case looks like?

--
Peter Geoghegan

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

On Tue, Feb 7, 2017 at 3:21 PM, Tiago Babo <tiago.babo@gmail.com> wrote:

The only difference (so it seems), is that when it crashes, the person was created in the seconds before creating the account. When it previously exists in the persons table, it never gives the error.

It crashes?

Anyway, it's very hard to see how that could be, since the error in
question is thrown from within the planner. I don't recall the exact
details of how inference will do offhand, but I am suspicious of the
cast that appears in the partial index predicate.

--
Peter Geoghegan

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

Maybe it was not the best use of the word "crashes". It gives me the error
I showed you.

I don't really understand how PostgreSQL handles indexes, but would it be
possible that the INDEX is being used/updated at that moment and so the
INSERT doesn't know it exists? Can concurrency also be a problem?

Peter Geoghegan <pg@bowt.ie> escreveu no dia terça, 7/02/2017 às 23:36:

Peter Geoghegan <pg@bowt.ie> writes:

Anyway, it's very hard to see how that could be, since the error in
question is thrown from within the planner.

Yeah, there really shouldn't be any data dependency there.

I don't recall the exact
details of how inference will do offhand, but I am suspicious of the
cast that appears in the partial index predicate.

That's expected given that the column is declared varchar.

The whole thing is pretty strange. I could believe the test not
finding an index whose state is changing (ie, it's in process of
being built by CREATE INDEX CONCURRENTLY), but as long as the
pg_index entry is stable it seems like it should either work or not.

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

On Tue, Feb 7, 2017 at 3:46 PM, Tiago Babo <tiago.babo@gmail.com> wrote:

I don't really understand how PostgreSQL handles indexes, but would it be
possible that the INDEX is being used/updated at that moment and so the
INSERT doesn't know it exists? Can concurrency also be a problem?

Anything is possible, I suppose, but that seems very unlikely to be a
factor here. Any problem like this occurs in codepaths that only
consider metadata -- the definition of indexes themselves, underlying
types, and so on. This presumably never changes here; you aren't
creating and dropping indexes on the table in question around the time
you see problems.

--
Peter Geoghegan

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

btw ... not relevant to this particular complaint, but I notice that
infer_arbiter_indexes will accept an index that is indisvalid and
indisunique, but should it be checking indimmediate as well? That is,
does the ON CONFLICT code work if the uniqueness checks are deferred?
I could not find any regression tests exercising such a case.

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

Tiago Babo <tiago.babo@gmail.com> writes:

I don't really understand how PostgreSQL handles indexes, but would it be
possible that the INDEX is being used/updated at that moment and so the
INSERT doesn't know it exists? Can concurrency also be a problem?

Data activity shouldn't matter. If you're dropping and recreating the
whole index, that could matter.

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

No, I'm not doing that. The index was just created once.

On 8 Feb 2017, at 00:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Tiago Babo <tiago.babo@gmail.com> writes:

I don't really understand how PostgreSQL handles indexes, but would it be
possible that the INDEX is being used/updated at that moment and so the
INSERT doesn't know it exists? Can concurrency also be a problem?

Data activity shouldn't matter. If you're dropping and recreating the
whole index, that could matter.

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

Tiago Babo <tiago.babo@gmail.com> writes:

No, I'm not doing that. The index was just created once.

Whole thing is as weird as can be. Maybe you could work on creating
a reproducible test case?

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

On Tue, Feb 7, 2017 at 3:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

btw ... not relevant to this particular complaint, but I notice that
infer_arbiter_indexes will accept an index that is indisvalid and
indisunique, but should it be checking indimmediate as well? That is,
does the ON CONFLICT code work if the uniqueness checks are deferred?
I could not find any regression tests exercising such a case.

It won't work with deferrable constraints (even when immediate
enforcement is in effect, so obscure reasons). Enforcement occurs in
the executor -- see ExecCheckIndexConstraints().

You may recall that I wrote a refactoring patch that attempted to make
the situation clearer, which Heikki didn't like. Currently, the
constant UNIQUE_CHECK_PARTIAL is sort of overloaded to also be used
with speculative insertion, which, aside from being ugly, has various
minor practical disadvantages.

--
Peter Geoghegan

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