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

That's odd! I just confirmed again that I get the exact same error with
COMMIT (and no warnings). Are you able to run all the commands before
the COMMIT successfully and get 1 row back from the SELECT?

On 10/05/2015 7:47 PM, David G. Johnston wrote:
> On Fri, Apr 24, 2015 at 9:57 AM, <postgresql2@realityexists.net
> <mailto: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
>     <mailto: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;
>
>
> ​ 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.​
>