Re: Workaround for bug #13148 (deferred EXCLUDE constraint violation)

On Mon, Apr 27, 2015 at 7:45 AM, Evan Martin <postgresql(at)realityexists(dot)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