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(at)realityexists(dot)net
> <mailto: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
> <mailto: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.
>