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

Started by Nonameover 10 years ago10 messages

postgresql2@realityexists.net

over 10 years ago

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;

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

David G. Johnston

david.g.johnston@gmail.com

over 10 years ago

In reply to: Noname (#1)

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

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

Noname

postgresql2@realityexists.net

over 10 years ago

In reply to: David G. Johnston (#2)

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

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?

Show quoted text

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.

David G. Johnston

david.g.johnston@gmail.com

over 10 years ago

In reply to: Noname (#3)

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

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?

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

David J.

David G. Johnston

david.g.johnston@gmail.com

over 10 years ago

In reply to: David G. Johnston (#4)

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

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 the
COMMIT successfully and get 1 row back from the SELECT?

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

To clarify - the transaction still errors out; but to me, not using psql,
it seems to do so prior to the COMMIT itself being processed and so the
commit emits a warning about not being in a tranasction.

Ubuntu apt.postgresql.org 9.3.5

David J.

Tom Lane

tgl@sss.pgh.pa.us

over 10 years ago

In reply to: Noname (#1)

1 attachment(s)

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

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

Attachments:

exclusion-HOT-bug.patchtext/x-diff; charset=us-ascii; name=exclusion-HOT-bug.patchDownload

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);
  	}

Andres Freund

andres@anarazel.de

over 10 years ago

In reply to: Tom Lane (#6)

Re: [BUGS] BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table

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

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

Peter Geoghegan

pg@heroku.com

over 10 years ago

In reply to: Andres Freund (#7)

Re: [BUGS] BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table

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

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

Evan Martin

postgresql@realityexists.net

over 10 years ago

In reply to: Tom Lane (#6)

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

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?

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

#10

Michael Paquier

michael.paquier@gmail.com

over 10 years ago

In reply to: Evan Martin (#9)

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

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

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