I submitted the following bug report through the web form a few days
ago. It's causing problems in my application and I've been unable to
find a way to get around it. If someone here, familiar with PostgreSQL
internals, could suggest a workaround I'd really appreciate it!
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 and 9.4.1 64-bit on Windows 7 and Postgresql 9.2.10 32-bit on
Ubuntu using the attached script.
-- **** 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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
