Re: Serialization errors despite KEY SHARE/NO KEY UPDATE

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)

>> -- 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):

9=# \d users
Table "public.users"
Column | Type | Modifiers

--------+-----------------------------+----------------------------------------------------
id | integer | not null default
nextval('users_id_seq'::regclass)
name | character varying |
date | timestamp without time zone |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "orders" CONSTRAINT "orders_user_id_fkey" FOREIGN KEY (user_id)
REFERENCES users(id)

9=# \d orders
Table "public.orders"
Column | Type | Modifiers

---------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('orders_id_seq'::regclass)
name | character varying |
user_id | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

--
Olivier