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

The following bug has been logged on the website:

Bug reference:      14526
Logged by:          Tiago Babo
Email address:      tiago.babo@gmail.com
PostgreSQL version: 9.5.5
Operating system:   Ubuntu 14.04.2 LTS
Description:

I'm getting the following error when doing the following type of insert:

Errpr:
SQL execution failed (Reason: ERROR: there is no unique or exclusion
constraint matching the ON CONFLICT specification)

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

The thing is that sometimes it works, but not every time. I "randomly" get
that exception, which is really strange. It seems that it can't access that
INDEX or it doesn't know it exists.

Any suggestion?


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

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.
Whereis 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

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

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

On Tue, Feb 7, 2017 at 5:41 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> It won't work with deferrable constraints (even when immediate
> enforcement is in effect, so obscure reasons). Enforcement occurs in
> the executor -- see ExecCheckIndexConstraints().

Note also that it needs to happen in the executor, because
infer_arbiter_indexes() may return immediately when ON CONFLICT DO
NOTHING is used without the user specifying which particular
constraint to use as an arbiter.  (This is forbidden with ON CONFLICT
DO UPDATE, since it doesn't make sense to not have an arbiter in mind
there.)

This is actually noted directly within infer_arbiter_indexes(), about
half way down:

/*
 * Extract info from the relation descriptor for the index.  We know
 * that this is a target, so get lock type it is known will ultimately
 * be required by the executor.
 *
 * Let executor complain about !indimmediate case directly, because
 * enforcement needs to occur there anyway when an inference clause is
 * omitted.
 */

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

Hi, again. After some testing, I was able to reproduce this error with the following code:

CREATE TABLE test (
  type character varying,
  id integer
);

CREATE UNIQUE INDEX uniq_id_test ON test USING btree (type, id) WHERE (type = 'Test');

PREPARE test (text, int, text) AS
  INSERT INTO test (type, id)
  VALUES ($1, $2)
  ON CONFLICT (type, id) WHERE type = $3 DO UPDATE SET id = EXCLUDED.id;


EXECUTE test('Test', 1, 'Test');
EXECUTE test('Test', 2, 'Test');
EXECUTE test('Test', 3, 'Test');
EXECUTE test('Test', 4, 'Test');
EXECUTE test('Test', 5, 'Test');
EXECUTE test('Test', 6, 'Test’);

It gives the error when trying to execute the last statement.

> On 8 Feb 2017, at 01:48, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Feb 7, 2017 at 5:41 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> It won't work with deferrable constraints (even when immediate
>> enforcement is in effect, so obscure reasons). Enforcement occurs in
>> the executor -- see ExecCheckIndexConstraints().
>
> Note also that it needs to happen in the executor, because
> infer_arbiter_indexes() may return immediately when ON CONFLICT DO
> NOTHING is used without the user specifying which particular
> constraint to use as an arbiter.  (This is forbidden with ON CONFLICT
> DO UPDATE, since it doesn't make sense to not have an arbiter in mind
> there.)
>
> This is actually noted directly within infer_arbiter_indexes(), about
> half way down:
>
> /*
> * Extract info from the relation descriptor for the index.  We know
> * that this is a target, so get lock type it is known will ultimately
> * be required by the executor.
> *
> * Let executor complain about !indimmediate case directly, because
> * enforcement needs to occur there anyway when an inference clause is
> * omitted.
> */
>
> --
> 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:
> Hi, again. After some testing, I was able to reproduce this error with the following code:

> CREATE UNIQUE INDEX uniq_id_test ON test USING btree (type, id) WHERE (type = 'Test');

> PREPARE test (text, int, text) AS
>   INSERT INTO test (type, id)
>   VALUES ($1, $2)
>   ON CONFLICT (type, id) WHERE type = $3 DO UPDATE SET id = EXCLUDED.id;

> EXECUTE test('Test', 1, 'Test');
> EXECUTE test('Test', 2, 'Test');
> EXECUTE test('Test', 3, 'Test');
> EXECUTE test('Test', 4, 'Test');
> EXECUTE test('Test', 5, 'Test');
> EXECUTE test('Test', 6, 'Test');

> It gives the error when trying to execute the last statement.

Hm.  So the problem here is that the prepared statement only matches the
partial index as long as the actual parameter is substituted literally
into the statement.  As soon as the plancache tries to consider a generic
plan, in which it's not apparent at plan time what $3 is, we can't prove
the partial index to be matched so you get the error.

If this is representative of what your application is actually doing,
rather than what you were saying it does, then the answer is that you
have to match the partial index clause exactly, not rely on substitution/
simplification to produce a match.

Or don't use a partial index.  That schema seems pretty bizarre to me
anyway.

Having said all that, I think this is a fine example of why relying on
planner inferences for semantic decisions (rather than just optimization)
is damn-fool design.  If I'd been paying closer attention I would have
objected loudly to the use of WHERE in ON CONFLICT for this purpose.
I wonder whether it's too late to deprecate that feature.

            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

This is not how my application is doing the insert (like I showed you before), but it was the only way I could
continuouslyget the error. I'm using a Scala library called ScalikeJDBC to access the database. So maybe the problem is
onhow it handles the execution.  

> On 8 Feb 2017, at 17:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Tiago Babo <tiago.babo@gmail.com> writes:
>> Hi, again. After some testing, I was able to reproduce this error with the following code:
>
>> CREATE UNIQUE INDEX uniq_id_test ON test USING btree (type, id) WHERE (type = 'Test');
>
>> PREPARE test (text, int, text) AS
>>  INSERT INTO test (type, id)
>>  VALUES ($1, $2)
>>  ON CONFLICT (type, id) WHERE type = $3 DO UPDATE SET id = EXCLUDED.id;
>
>> EXECUTE test('Test', 1, 'Test');
>> EXECUTE test('Test', 2, 'Test');
>> EXECUTE test('Test', 3, 'Test');
>> EXECUTE test('Test', 4, 'Test');
>> EXECUTE test('Test', 5, 'Test');
>> EXECUTE test('Test', 6, 'Test');
>
>> It gives the error when trying to execute the last statement.
>
> Hm.  So the problem here is that the prepared statement only matches the
> partial index as long as the actual parameter is substituted literally
> into the statement.  As soon as the plancache tries to consider a generic
> plan, in which it's not apparent at plan time what $3 is, we can't prove
> the partial index to be matched so you get the error.
>
> If this is representative of what your application is actually doing,
> rather than what you were saying it does, then the answer is that you
> have to match the partial index clause exactly, not rely on substitution/
> simplification to produce a match.
>
> Or don't use a partial index.  That schema seems pretty bizarre to me
> anyway.
>
> Having said all that, I think this is a fine example of why relying on
> planner inferences for semantic decisions (rather than just optimization)
> is damn-fool design.  If I'd been paying closer attention I would have
> objected loudly to the use of WHERE in ON CONFLICT for this purpose.
> I wonder whether it's too late to deprecate that feature.
>
>            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:
> This is not how my application is doing the insert (like I showed you before), but it was the only way I could
continuouslyget the error. I'm using a Scala library called ScalikeJDBC to access the database. So maybe the problem is
onhow it handles the execution.  

You should turn on log_statements and look to see what's actually being
sent to the server.

            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 Wed, Feb 8, 2017 at 9:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Having said all that, I think this is a fine example of why relying on
> planner inferences for semantic decisions (rather than just optimization)
> is damn-fool design.  If I'd been paying closer attention I would have
> objected loudly to the use of WHERE in ON CONFLICT for this purpose.
> I wonder whether it's too late to deprecate that feature.

I never imagined that somebody would want to parameterize constants in
the WHERE clause, because that's contrary to the idea that the
semantics are expressed explicitly from SQL. There may have been a
failure of imagination on my part about how that interacts with
certain driver libraries, but that doesn't invalidate the idea of
inference of partial indexes.

I do think that it's very valuable that inference supports partial
indexes. Back when I was an application developer, I extensively used
partial unique indexes to support "logical deletion" of records. There
were many unique indexes "WHERE NOT is_deleted" or similar. *Most*
unique constraints ended up being implemented this way. Since partial
indexes are necessarily never constraints as such, users cannot spell
out their name directly, and so inference of some form must be used.
ON CONFLICT would be totally closed off to an app that did that were
it not for inference of partial indexes, so it does seem very
important to me.

If you want to talk about making this less surprising, I think that
that would definitely need to be more nuanced than simply not
accepting a WHERE clause in the inference specification. I would
strongly object to that.

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

Peter Geoghegan <pg@bowt.ie> writes:
> On Tue, Feb 7, 2017 at 5:41 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>> It won't work with deferrable constraints (even when immediate
>> enforcement is in effect, so obscure reasons). Enforcement occurs in
>> the executor -- see ExecCheckIndexConstraints().

> This is actually noted directly within infer_arbiter_indexes(), about
> half way down:
>  * Let executor complain about !indimmediate case directly, because
>  * enforcement needs to occur there anyway when an inference clause is
>  * omitted.

I'm not following.  If the executor needs to check too, that's fine,
but why is it okay for the planner not to check?  Assume that for some
weird reason the user has both indimmediate and !indimmediate indexes
on the same column set.  If the planner chooses the wrong one, don't
bad things happen?  If it doesn't matter which index the planner
picks, why are we doing this at plan time at all?

            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 Wed, Feb 8, 2017 at 10:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm not following.  If the executor needs to check too, that's fine,
> but why is it okay for the planner not to check?  Assume that for some
> weird reason the user has both indimmediate and !indimmediate indexes
> on the same column set.  If the planner chooses the wrong one, don't
> bad things happen?

No, because the planner isn't limited to picking just one. It is
generally very likely that only one will be chosen, but edge cases
like this are considered. infer_arbiter_indexes() returns a list of
Oids of indexes.

-- 
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 Wed, 08 Feb 2017 12:31:58 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Tiago Babo <tiago.babo@gmail.com> writes:
> > This is not how my application is doing the insert (like I showed you before), but it was the only way I could
continuouslyget the error. I'm using a Scala library called ScalikeJDBC to access the database. So maybe the problem is
onhow it handles the execution. 
 
> 
> You should turn on log_statements and look to see what's actually being
> sent to the server.

Last time I looked, the JDBC driver always uses prepared statements.

-dg

-- 
David Gould              510 282 0869         daveg@sonic.net
If simplicity worked, the world would be overrun with insects.


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

David Gould <daveg@sonic.net> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You should turn on log_statements and look to see what's actually being
>> sent to the server.

> Last time I looked, the JDBC driver always uses prepared statements.

Yeah, but does JDBC actually pull literal constants out of the query
string and send them as separate parameter values?  That seems like a
pretty dumb idea.

            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 Wed, 08 Feb 2017 18:57:16 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> David Gould <daveg@sonic.net> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> You should turn on log_statements and look to see what's actually being
> >> sent to the server.
> 
> > Last time I looked, the JDBC driver always uses prepared statements.
> 
> Yeah, but does JDBC actually pull literal constants out of the query
> string and send them as separate parameter values?  That seems like a
> pretty dumb idea.

I'm guessing that the actual call from Scala uses parameters for this
part instead of duplicating the same query with only the qualification
changing.

Tiago, can you show us the actual code that runs this statement?

-dg

-- 
David Gould              510 282 0869         daveg@sonic.net
If simplicity worked, the world would be overrun with insects.


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

Yes, you're right. I'm using parameters for that part of the query.

> On 9 Feb 2017, at 02:00, David Gould <daveg@sonic.net> wrote:
> 
> On Wed, 08 Feb 2017 18:57:16 -0500
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
>> David Gould <daveg@sonic.net> writes:
>>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> You should turn on log_statements and look to see what's actually being
>>>> sent to the server.
>> 
>>> Last time I looked, the JDBC driver always uses prepared statements.
>> 
>> Yeah, but does JDBC actually pull literal constants out of the query
>> string and send them as separate parameter values?  That seems like a
>> pretty dumb idea.
> 
> I'm guessing that the actual call from Scala uses parameters for this
> part instead of duplicating the same query with only the qualification
> changing.
> 
> Tiago, can you show us the actual code that runs this statement?
> 
> -dg
> 
> -- 
> David Gould              510 282 0869         daveg@sonic.net
> If simplicity worked, the world would be overrun with insects.


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