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

From: postgresql2(at)realityexists(dot)net
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table
Date: 2015-04-24 16:57:22
Message-ID: 20150424165722.2548.62492@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 13148
Logged by: Evan Martin
Email address: postgresql2(at)realityexists(dot)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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2015-04-24 21:34:25 Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated)
Previous Message Robert Haas 2015-04-24 13:18:26 Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated)

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2015-04-24 17:05:03 Re: tablespaces inside $PGDATA considered harmful
Previous Message Pavel Stehule 2015-04-24 16:07:30 Re: PL/pgSQL, RAISE and error context