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

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.

On 28/04/2015 2:16 PM, John McKown wrote:
> On Mon, Apr 27, 2015 at 7:45 AM, Evan Martin
> <postgresql(at)realityexists(dot)net <mailto: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