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.