Re: 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