Thread: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table

The following bug has been logged on the website:

Bug reference:      13148
Logged by:          Evan Martin
Email address:      postgresql2@realityexists.net
PostgreSQL version: 9.3.6
Operating system:   Windows 7 x64 SP1
Description:

I have a deferred EXCLUDE constraint on a derived table. Inside a
transaction I insert a new row that conflicts with an existing one (so the
constraint would fail if it was immediate), delete the old row and run an
unrelated UPDATE on the new row, then try to commit. I would expect the
commit to succeed, since there is now no conflict, but it fails with

    ERROR: conflicting key value violates exclusion constraint
"uq_derived_timeslice_dup_time_ex"
    SQL state: 23P01
    Detail: Key (feature_id, valid_time_begin, interpretation,
(COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1) conflicts
with existing key (feature_id, valid_time_begin, interpretation,
(COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1).

If I run the delete statement first it works. If I remove the (seemingly
unrelated) update statement it also works. Reproducible under PostgreSQL
9.3.6 64-bit on Windows 7 and Postgresql 9.2.10 32-bit on Ubuntu using the
script below.


-- **** One-off set-up ****
/*
-- DROP TABLE IF EXISTS base_timeslice CASCADE;

CREATE TABLE base_timeslice
(
  timeslice_id integer NOT NULL,
  feature_id integer NOT NULL,
  valid_time_begin timestamp NOT NULL,
  interpretation text NOT NULL,
  sequence_number integer,
  CONSTRAINT pk_base_timeslice PRIMARY KEY (timeslice_id)
);

CREATE TABLE derived_timeslice
(
  timeslice_id integer NOT NULL,
  feature_id integer NOT NULL,
  name text NOT NULL,
  CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id),
  CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE
    USING btree (feature_id WITH =, valid_time_begin WITH =, interpretation
WITH =, (COALESCE(sequence_number::integer, (-1))) WITH =)
    DEFERRABLE INITIALLY DEFERRED
)
INHERITS (base_timeslice);

INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin,
interpretation, name)
VALUES (51, 1, '2015-01-01', 'X', 'Test');
*/

-- **** Repro ****

BEGIN;

-- Insert row that violates deferred constraint
INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin,
interpretation, name)
VALUES (52, 1, '2015-01-01', 'X', 'Test');

-- Delete the old row - now there should be no more conflict
DELETE FROM derived_timeslice WHERE timeslice_id = 51;

-- Problem doesn't occur without an UPDATE statement
UPDATE derived_timeslice SET name = 'Updated' WHERE timeslice_id = 52;

-- This confirms there is only 1 row - no conflict
SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE
feature_id = 1;

--COMMIT;
SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs here

ROLLBACK;

On Fri, Apr 24, 2015 at 9:57 AM, <postgresql2@realityexists.net> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13148
> Logged by:          Evan Martin
> Email address:      postgresql2@realityexists.net
> PostgreSQL version: 9.3.6
> Operating system:   Windows 7 x64 SP1
> Description:
>
> I have a deferred EXCLUDE constraint on a derived table. Inside a
> transaction I insert a new row that conflicts with an existing one (so th=
e
> constraint would fail if it was immediate), delete the old row and run an
> unrelated UPDATE on the new row, then try to commit. I would expect the
> commit to succeed, since there is now no conflict, but it fails with
>
>         ERROR: conflicting key value violates exclusion constraint
> "uq_derived_timeslice_dup_time_ex"
>         SQL state: 23P01
>         Detail: Key (feature_id, valid_time_begin, interpretation,
> (COALESCE(sequence_number, (-1))))=3D(1, 2015-01-01 00:00:00, X, -1)
> conflicts
> with existing key (feature_id, valid_time_begin, interpretation,
> (COALESCE(sequence_number, (-1))))=3D(1, 2015-01-01 00:00:00, X, -1).
>
> If I run the delete statement first it works. If I remove the (seemingly
> unrelated) update statement it also works. Reproducible under PostgreSQL
> 9.3.6 64-bit on Windows 7 and Postgresql 9.2.10 32-bit on Ubuntu using th=
e
> script below.
>
>
> -- **** One-off set-up ****
> /*
> -- DROP TABLE IF EXISTS base_timeslice CASCADE;
>
> CREATE TABLE base_timeslice
> (
>   timeslice_id integer NOT NULL,
>   feature_id integer NOT NULL,
>   valid_time_begin timestamp NOT NULL,
>   interpretation text NOT NULL,
>   sequence_number integer,
>   CONSTRAINT pk_base_timeslice PRIMARY KEY (timeslice_id)
> );
>
> CREATE TABLE derived_timeslice
> (
>   timeslice_id integer NOT NULL,
>   feature_id integer NOT NULL,
>   name text NOT NULL,
>   CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id),
>   CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE
>         USING btree (feature_id WITH =3D, valid_time_begin WITH =3D,
> interpretation
> WITH =3D, (COALESCE(sequence_number::integer, (-1))) WITH =3D)
>         DEFERRABLE INITIALLY DEFERRED
> )
> INHERITS (base_timeslice);
>
> INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin=
,
> interpretation, name)
> VALUES (51, 1, '2015-01-01', 'X', 'Test');
> */
>
> -- **** Repro ****
>
> BEGIN;
>
> -- Insert row that violates deferred constraint
> INSERT INTO derived_timeslice (timeslice_id, feature_id, valid_time_begin=
,
> interpretation, name)
> VALUES (52, 1, '2015-01-01', 'X', 'Test');
>
> -- Delete the old row - now there should be no more conflict
> DELETE FROM derived_timeslice WHERE timeslice_id =3D 51;
>
> -- Problem doesn't occur without an UPDATE statement
> UPDATE derived_timeslice SET name =3D 'Updated' WHERE timeslice_id =3D 52=
;
>
> -- This confirms there is only 1 row - no conflict
> SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE
> feature_id =3D 1;
>
> --COMMIT;
> SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs here
>
> ROLLBACK;
>
>
=E2=80=8BI can provide a limited affirmation that the above example is prob=
lematic
on 9.3

Changing "SET CONSTRAINTS ALL IMMEDIATE" to "COMMIT;" results in a warning
that there is no currently active transaction and the transaction itself
appears to have been rolled back.

David J.=E2=80=8B

Please don't top-post.

On Sun, May 10, 2015 at 11:19 AM, <postgresql2@realityexists.net> wrote:

>  That's odd! I just confirmed again that I get the exact same error with
> COMMIT (and no warnings). Are you able to run all the commands before the
> COMMIT successfully and get 1 row back from the SELECT?
>
>
=E2=80=8BI am.  I am not using psql so I may be getting different errors co=
mpared
to what psql would eventually emit.  I suspect that the actual error is
getting swallowed somewhere and then the processing on the COMMIT happens
without the transaction being open.

David J.=E2=80=8B

On Sun, May 10, 2015 at 11:28 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

> Please don't top-post.
>
> On Sun, May 10, 2015 at 11:19 AM, <postgresql2@realityexists.net> wrote:
>
>>  That's odd! I just confirmed again that I get the exact same error with
>> COMMIT (and no warnings). Are you able to run all the commands before th=
e
>> COMMIT successfully and get 1 row back from the SELECT?
>>
>>
> =E2=80=8BI am.  I am not using psql so I may be getting different errors =
compared
> to what psql would eventually emit.  I suspect that the actual error is
> getting swallowed somewhere and then the processing on the COMMIT happens
> without the transaction being open.
>

=E2=80=8BTo clarify - the transaction still errors out; but to me, not usin=
g psql,
it seems to do so prior =E2=80=8Bto the COMMIT itself being processed and s=
o the
commit emits a warning about not being in a tranasction.

Ubuntu apt.postgresql.org 9.3.5

David J.

postgresql2@realityexists.net writes:
> I have a deferred EXCLUDE constraint on a derived table. Inside a
> transaction I insert a new row that conflicts with an existing one (so the
> constraint would fail if it was immediate), delete the old row and run an
> unrelated UPDATE on the new row, then try to commit. I would expect the
> commit to succeed, since there is now no conflict, but it fails with
>     ERROR: conflicting key value violates exclusion constraint
> "uq_derived_timeslice_dup_time_ex"

Hm.  The given test case is overcomplicated; in point of fact it will fail
on any deferred exclusion constraint, eg


DROP TABLE IF EXISTS derived_timeslice CASCADE;

CREATE TABLE derived_timeslice
(
  timeslice_id integer NOT NULL,
  feature_id integer NOT NULL,
  name text,
  CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id),
  CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE
    USING btree (feature_id WITH =)
    DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO derived_timeslice (timeslice_id, feature_id) VALUES (51, 1);

BEGIN;

-- Insert row that violates deferred constraint
INSERT INTO derived_timeslice (timeslice_id, feature_id) VALUES (52, 1);

-- Delete the old row - now there should be no more conflict
DELETE FROM derived_timeslice WHERE timeslice_id = 51;

-- Problem doesn't occur without an UPDATE statement
UPDATE derived_timeslice SET name = 'Updated' WHERE timeslice_id = 52;

-- This confirms there is only 1 row - no conflict
SELECT * FROM derived_timeslice;

COMMIT; -- Enforce constraint - error occurs here


The cause of the problem seems to be that the UPDATE performs a HOT update
of the new tuple, leaving in this case a dead tuple at (0,2) that is HOT
updated by (0,3).  When unique_key_recheck() is invoked for (0,2), it
believes, correctly, that it has to perform the recheck anyway ... but it
tells check_exclusion_constraint that the check is being performed for
(0,2).  So the index search inside check_exclusion_constraint finds the
live tuple at (0,3) and thinks that is a conflict.

This is reproducible clear back to 9.0 where exclusion constraints were
added.

The easiest fix seems to be to pass the HOT child's TID instead of the
TID we were called for.  (Note that the other path, for a regular unique
constraint, is correct as-is because the original TID is what the index
will know about.)

The attached patch seems to fix the problem without breaking any existing
regression tests, but I wonder if anyone can see a hole in it.

            regards, tom lane

diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c
index e49affb..28fccaf 100644
*** a/src/backend/commands/constraint.c
--- b/src/backend/commands/constraint.c
*************** unique_key_recheck(PG_FUNCTION_ARGS)
*** 89,97 ****
       * because this trigger gets queued only in response to index insertions;
       * which means it does not get queued for HOT updates.  The row we are
       * called for might now be dead, but have a live HOT child, in which case
!      * we still need to make the check.  Therefore we have to use
!      * heap_hot_search, not just HeapTupleSatisfiesVisibility as is done in
!      * the comparable test in RI_FKey_check.
       *
       * This might look like just an optimization, because the index AM will
       * make this identical test before throwing an error.  But it's actually
--- 89,98 ----
       * because this trigger gets queued only in response to index insertions;
       * which means it does not get queued for HOT updates.  The row we are
       * called for might now be dead, but have a live HOT child, in which case
!      * we still need to make the check --- effectively, we're applying the
!      * check against the live child row, although we can use the values from
!      * this row since by definition all columns of interest to us are the
!      * same.
       *
       * This might look like just an optimization, because the index AM will
       * make this identical test before throwing an error.  But it's actually
*************** unique_key_recheck(PG_FUNCTION_ARGS)
*** 159,165 ****
      {
          /*
           * Note: this is not a real insert; it is a check that the index entry
!          * that has already been inserted is unique.
           */
          index_insert(indexRel, values, isnull, &(new_row->t_self),
                       trigdata->tg_relation, UNIQUE_CHECK_EXISTING);
--- 160,168 ----
      {
          /*
           * Note: this is not a real insert; it is a check that the index entry
!          * that has already been inserted is unique.  Passing t_self is
!          * correct even if t_self is now dead, because that is the TID the
!          * index will know about.
           */
          index_insert(indexRel, values, isnull, &(new_row->t_self),
                       trigdata->tg_relation, UNIQUE_CHECK_EXISTING);
*************** unique_key_recheck(PG_FUNCTION_ARGS)
*** 168,177 ****
      {
          /*
           * For exclusion constraints we just do the normal check, but now it's
!          * okay to throw error.
           */
          check_exclusion_constraint(trigdata->tg_relation, indexRel, indexInfo,
!                                    &(new_row->t_self), values, isnull,
                                     estate, false);
      }

--- 171,182 ----
      {
          /*
           * For exclusion constraints we just do the normal check, but now it's
!          * okay to throw error.  In the HOT-update case, we must use the live
!          * HOT child's TID here, else check_exclusion_constraint will think
!          * the child is a conflict.
           */
          check_exclusion_constraint(trigdata->tg_relation, indexRel, indexInfo,
!                                    &tmptid, values, isnull,
                                     estate, false);
      }

That's odd! I just confirmed again that I get the exact same error with
COMMIT (and no warnings). Are you able to run all the commands before
the COMMIT successfully and get 1 row back from the SELECT?

On 10/05/2015 7:47 PM, David G. Johnston wrote:
> On Fri, Apr 24, 2015 at 9:57 AM, <postgresql2@realityexists.net
> <mailto:postgresql2@realityexists.net>>wrote:
>
>     The following bug has been logged on the website:
>
>     Bug reference:      13148
>     Logged by:          Evan Martin
>     Email address: postgresql2@realityexists.net
>     <mailto:postgresql2@realityexists.net>
>     PostgreSQL version: 9.3.6
>     Operating system:   Windows 7 x64 SP1
>     Description:
>
>     I have a deferred EXCLUDE constraint on a derived table. Inside a
>     transaction I insert a new row that conflicts with an existing one
>     (so the
>     constraint would fail if it was immediate), delete the old row and
>     run an
>     unrelated UPDATE on the new row, then try to commit. I would
>     expect the
>     commit to succeed, since there is now no conflict, but it fails with
>
>             ERROR: conflicting key value violates exclusion constraint
>     "uq_derived_timeslice_dup_time_ex"
>             SQL state: 23P01
>             Detail: Key (feature_id, valid_time_begin, interpretation,
>     (COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1)
>     conflicts
>     with existing key (feature_id, valid_time_begin, interpretation,
>     (COALESCE(sequence_number, (-1))))=(1, 2015-01-01 00:00:00, X, -1).
>
>     If I run the delete statement first it works. If I remove the
>     (seemingly
>     unrelated) update statement it also works. Reproducible under
>     PostgreSQL
>     9.3.6 64-bit on Windows 7 and Postgresql 9.2.10 32-bit on Ubuntu
>     using the
>     script below.
>
>
>     -- **** One-off set-up ****
>     /*
>     -- DROP TABLE IF EXISTS base_timeslice CASCADE;
>
>     CREATE TABLE base_timeslice
>     (
>       timeslice_id integer NOT NULL,
>       feature_id integer NOT NULL,
>       valid_time_begin timestamp NOT NULL,
>       interpretation text NOT NULL,
>       sequence_number integer,
>       CONSTRAINT pk_base_timeslice PRIMARY KEY (timeslice_id)
>     );
>
>     CREATE TABLE derived_timeslice
>     (
>       timeslice_id integer NOT NULL,
>       feature_id integer NOT NULL,
>       name text NOT NULL,
>       CONSTRAINT pk_derived_timeslice PRIMARY KEY (timeslice_id),
>       CONSTRAINT uq_derived_timeslice_dup_time_ex EXCLUDE
>             USING btree (feature_id WITH =, valid_time_begin WITH =,
>     interpretation
>     WITH =, (COALESCE(sequence_number::integer, (-1))) WITH =)
>             DEFERRABLE INITIALLY DEFERRED
>     )
>     INHERITS (base_timeslice);
>
>     INSERT INTO derived_timeslice (timeslice_id, feature_id,
>     valid_time_begin,
>     interpretation, name)
>     VALUES (51, 1, '2015-01-01', 'X', 'Test');
>     */
>
>     -- **** Repro ****
>
>     BEGIN;
>
>     -- Insert row that violates deferred constraint
>     INSERT INTO derived_timeslice (timeslice_id, feature_id,
>     valid_time_begin,
>     interpretation, name)
>     VALUES (52, 1, '2015-01-01', 'X', 'Test');
>
>     -- Delete the old row - now there should be no more conflict
>     DELETE FROM derived_timeslice WHERE timeslice_id = 51;
>
>     -- Problem doesn't occur without an UPDATE statement
>     UPDATE derived_timeslice SET name = 'Updated' WHERE timeslice_id = 52;
>
>     -- This confirms there is only 1 row - no conflict
>     SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE
>     feature_id = 1;
>
>     --COMMIT;
>     SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs
>     here
>
>     ROLLBACK;
>
>
> ​ I can provide a limited affirmation that the above example is
> problematic on 9.3
>
> Changing "SET CONSTRAINTS ALL IMMEDIATE" to "COMMIT;" results in a
> warning that there is no currently active transaction and the
> transaction itself appears to have been rolled back.
>
> David J.​
>

Hi,

On 2015-05-10 16:01:53 -0400, Tom Lane wrote:
> The cause of the problem seems to be that the UPDATE performs a HOT update
> of the new tuple, leaving in this case a dead tuple at (0,2) that is HOT
> updated by (0,3).  When unique_key_recheck() is invoked for (0,2), it
> believes, correctly, that it has to perform the recheck anyway ... but it
> tells check_exclusion_constraint that the check is being performed for
> (0,2).  So the index search inside check_exclusion_constraint finds the
> live tuple at (0,3) and thinks that is a conflict.

Heh, it's curious that this wasn't found up until now. I also wonder if
this might be related to the spurious violations Peter G. has been
observing...

> The attached patch seems to fix the problem without breaking any existing
> regression tests, but I wonder if anyone can see a hole in it.

Looks good to me.

Greetings,

Andres Freund

On Mon, May 11, 2015 at 9:47 AM, Andres Freund <andres@anarazel.de> wrote:
> On 2015-05-10 16:01:53 -0400, Tom Lane wrote:
>> The cause of the problem seems to be that the UPDATE performs a HOT update
>> of the new tuple, leaving in this case a dead tuple at (0,2) that is HOT
>> updated by (0,3).  When unique_key_recheck() is invoked for (0,2), it
>> believes, correctly, that it has to perform the recheck anyway ... but it
>> tells check_exclusion_constraint that the check is being performed for
>> (0,2).  So the index search inside check_exclusion_constraint finds the
>> live tuple at (0,3) and thinks that is a conflict.
>
> Heh, it's curious that this wasn't found up until now. I also wonder if
> this might be related to the spurious violations Peter G. has been
> observing...

I don't think so. Speculative insertion relies on the assumption that
the speculatively inserted tuple isn't MVCC visible to other sessions.
I actually prototyped an implementation that avoided the historic
"unprincipled deadlocks" of exclusion constraints (a known limitation
since they were added), by making *UPDATE* also do a speculative
insertion, and by making even non-UPSERT INSERTs insert speculatively.

This almost worked, but when time came to back out of a speculative
insertion on an UPDATE due to a conflict from a concurrent session,
the implementation couldn't handle it - it was just a mess to try and
figure out how that was supposed to work with heap_update(), and so
that prototype was scrapped.

For the benefit of those not closely involved in the ON CONFLICT
project, I should point out that ON CONFLICT will not accept a
deferred index as an arbiter index.
-- 
Peter Geoghegan

On 10/05/2015 10:01 PM, Tom Lane wrote:
> The cause of the problem seems to be that the UPDATE performs a HOT update
> of the new tuple, leaving in this case a dead tuple at (0,2) that is HOT
> updated by (0,3).  When unique_key_recheck() is invoked for (0,2), it
> believes, correctly, that it has to perform the recheck anyway ... but it
> tells check_exclusion_constraint that the check is being performed for
> (0,2).  So the index search inside check_exclusion_constraint finds the
> live tuple at (0,3) and thinks that is a conflict.
>
> This is reproducible clear back to 9.0 where exclusion constraints were
> added.
>
> The easiest fix seems to be to pass the HOT child's TID instead of the
> TID we were called for.  (Note that the other path, for a regular unique
> constraint, is correct as-is because the original TID is what the index
> will know about.)
>
> The attached patch seems to fix the problem without breaking any existing
> regression tests, but I wonder if anyone can see a hole in it.
>
>             regards, tom lane
>
Thanks very much for fixing this! Now that you know the cause, could you
suggest any workaround?

Also, any idea when the fix will be released? Will it be in 9.3.7 or
9.4.2 or only in 9.5.0?

On Sun, May 17, 2015 at 7:30 PM, Evan Martin wrote:
> Also, any idea when the fix will be released? Will it be in 9.3.7 or 9.4.2
> or only in 9.5.0?

It will be released next week with 9.4.2 and 9.3.7.
--
Michael