Workaround for bug #13148 (deferred EXCLUDE constraint violation)

On Mon, Apr 27, 2015 at 7:45 AM, Evan Martin <postgresql@realityexists.net>
wrote:

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.

​I don't know if it is acceptable to you, but I did manage a work around. I
ran you script as is and got the same problem. I was able to run the script
to successful completion by adding in one statement just _before_ the BEGIN
command:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

http://www.postgresql.org/docs/9.4/static/sql-set-transaction.html
<quote>
SERIALIZABLE

All statements of the current transaction can only see rows committed
before the first query or data-modification statement was executed in this
transaction. If a pattern of reads and writes among concurrent serializable
transactions would create a situation which could not have occurred for any
serial (one-at-a-time) execution of those transactions, one of them will be
rolled back with a serialization_failure error.
</quote>

I do not know the internals, but I have a "gut feel" that the problem
somehow relates to the MVCC implementation in PostgreSQL.​

Sorry about delay but: (1) I was on Jury duty yesterday & (2) I was hoping
a more experienced person would speak up.

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Thanks for looking into this! I tried your workaround on both 9.3.6 and
9.4.1 on Windows (64-bit), but it made no difference for me. If I put
the SET TRANSACTION statement before BEGIN on 9.4.1 I get "WARNING: SET
TRANSACTION can only be used in transaction blocks" - but putting it
inside the transaction block doesn't prevent the constraint violation,
either.