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

On Fri, Apr 24, 2015 at 9:57 AM, <postgresql2(at)realityexists(dot)net> wrote:

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