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
<postgre...@realityexists.net <mailto:postgre...@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
