Thread: Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table
Re: BUG #13148: Unexpected deferred EXCLUDE constraint violation on derived table
From
Evan Martin
Date:
The bug below is also reproducible on 9.4.1 on Windows 7 (x64) and Linux (x86). I've been unable to find a workaround and it's causing a serious problem in my application, so I'd really appreciate if a developer could take a look at it! ----- Original message ----- 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;