Re: Serialization errors despite KEY SHARE/NO KEY UPDATE - Mailing list pgsql-general

On 10/2/15 11:44 AM, Olivier Dony wrote:
> On 10/02/2015 12:28 AM, Jim Nasby wrote:
>> On 9/29/15 9:47 AM, Olivier Dony wrote:
>>> My understanding of the KEY SHARE/NO KEY UPDATE locks introduced in 9.3
>>> was that they would avoid side-effects/blocking between transactions
>>> that are only linked via FK constraints, as long as the target PK was
>>> not touched. Isn't it the case here?
>>
>> Not quite. Any unique index that isn't partial and isn't a functional
>> index can satisfy a foreign key. That means that if you change a field
>> that is in ANY unique index that update becomes a FOR KEY UPDATE.
>
> Interesting, do you know if that is mentioned in the documentation
> somewhere? (I couldn't find it)

http://www.postgresql.org/docs/9.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
does say this:

"A foreign key must reference columns that either are a primary key or
form a unique constraint."

So you can kind of infer it. It could probably be more explicitly
mentioned somewhere though. Care to suggest a doc change?

>>> -- Setup tables
>>> CREATE TABLE users ( id serial PRIMARY KEY,
>>>                         name varchar,
>>>                         date timestamp );
>>> CREATE TABLE orders ( id serial PRIMARY KEY,
>>>                          name varchar,
>>>                          user_id int REFERENCES users (id) );
>>> INSERT INTO users (id, name) VALUES (1, 'foo');
>>> INSERT INTO orders (id, name) VALUES (1, 'order 1');
>>>
>>>
>>> -- Run 2 concurrent transactions: T1 and T2
>>>                T1                                T2
>>> |-----------------------------|----------------------------------|
>>>      BEGIN ISOLATION LEVEL
>>>            REPEATABLE READ;
>>>
>>>      UPDATE orders
>>>      SET name = 'order of foo',
>>>          user_id = 1
>>>      WHERE id = 1;
>>>
>>>                                        BEGIN ISOLATION LEVEL
>>>                                              REPEATABLE READ;
>>>
>>>                                        UPDATE users
>>>                                        SET date = now()
>>>                                        WHERE id = 1;
>>>
>>>                                        COMMIT;
>>>
>>>      UPDATE orders
>>>      SET name = 'order of foo (2)',
>>>          user_id = 1
>>>      WHERE id = 1;
>>>
>>> T1 fails with:
>>> ERROR:  could not serialize access due to concurrent update
>>> CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE
>>> "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
>>
>> This isn't a locking failure, it's a serialization failure. I'm not sure
>> why it's happening though... is there an index on date?
>
> I don't think so. I can reproduce the problem with the queries quoted
> above, and the only index that seems to be present is the PK (sorry for
> the wrapping):

I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can
shed some light.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com