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