Workaround for bug #13148 (deferred EXCLUDE constraint violation)

From: Evan Martin <postgresql(at)realityexists(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Workaround for bug #13148 (deferred EXCLUDE constraint violation)
Date: 2015-04-27 12:45:25
Message-ID: 553E2F65.3080508@realityexists.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Attachment Content-Type Size
repro.sql text/plain 1.6 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Roger Pack 2015-04-27 13:07:06 Re: Fwd: [GENERAL] 4B row limit for CLOB tables
Previous Message Jim Nasby 2015-04-27 06:49:44 Re: Fwd: [GENERAL] 4B row limit for CLOB tables