BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table - Mailing list pgsql-bugs

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;