On 10/12/16, Thomas Munro <thomas.munro@enterprisedb.com> wrote:
> On Wed, Oct 12, 2016 at 8:50 PM, Albe Laurenz <laurenz.albe@wien.gv.at>
> wrote:
>> Kevin Grittner wrote:
>>> On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek <jason.dusek@gmail.com>
>>> wrote:
>>>> I notice the following oddity:
>>>
>>>> =# CREATE TABLE with_pk (i integer PRIMARY KEY);
>>>> CREATE TABLE
>>>
>>>> =# BEGIN;
>>>> BEGIN
>>>> =# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING;
>>>> ERROR: could not serialize access due to concurrent update
>>>> =# END;
>>>> ROLLBACK
>>>
>>> I don't see that on development HEAD. What version are you
>>> running? What is your setting for default_transaction_isolation?
>>
>> The subject says SERIALIZABLE, and I can see it on my 9.5.4 database:
>>
>> test=> CREATE TABLE with_pk (i integer PRIMARY KEY);
>> CREATE TABLE
>> test=> START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>> START TRANSACTION
>> test=> INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING;
>> ERROR: could not serialize access due to concurrent update
>
> This happens in both SERIALIZABLE and REPEATABLE READ when a single
> command inserts conflicting rows with an ON CONFLICT cause, and it
> comes from the check in ExecCheckHeapTupleVisible whose comment says:
>
> /*
> * ExecCheckHeapTupleVisible -- verify heap tuple is visible
> *
> * It would not be consistent with guarantees of the higher isolation levels
> to
> * proceed with avoiding insertion (taking speculative insertion's
> alternative
> * path) on the basis of another tuple that is not visible to MVCC
> snapshot.
> * Check for the need to raise a serialization failure, and do so as
> necessary.
> */
>
> So it seems to be working as designed. Perhaps someone could argue
> that you should make an exception for tuples inserted by the current
> command.
I disagree. It is designed to prevent updating a tuple which was
updated in a parallel transaction which has been just committed.
This case is a little bit different: this tuple has been inserted in
the same transaction in the same command.
I think it is an obvious bug because there is no "concurrent update".
There is no update at all.
ON CONFLICT handling just does not cover all possible ways which can happen.
Normally (without "ON CONFLICT" clause) INSERT raises "duplicate key
value violates unique constraint" and doesn't run to
"ExecCheckHeapTupleVisible" check.
The "ExecInsert" handles constraint checks but not later checks like
ExecCheckHeapTupleVisible.
--
Best regards,
Vitaly Burovoy