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 th=
e
> 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))))=3D(1, 2015-01-01 00:00:00, X, -1)
> conflicts
> with existing key (feature_id, valid_time_begin, interpretation,
> (COALESCE(sequence_number, (-1))))=3D(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 th=
e
> 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 =3D, valid_time_begin WITH =3D,
> interpretation
> WITH =3D, (COALESCE(sequence_number::integer, (-1))) WITH =3D)
>         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 =3D 51;
>
> -- Problem doesn't occur without an UPDATE statement
> UPDATE derived_timeslice SET name =3D 'Updated' WHERE timeslice_id =3D 52=
;
>
> -- This confirms there is only 1 row - no conflict
> SELECT timeslice_id, valid_time_begin FROM derived_timeslice WHERE
> feature_id =3D 1;
>
> --COMMIT;
> SET CONSTRAINTS ALL IMMEDIATE; -- Enfore constraint - error occurs here
>
> ROLLBACK;
>
>
=E2=80=8BI can provide a limited affirmation that the above example is prob=
lematic
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.=E2=80=8B