From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Olivier Dony <odo+pggen@odoo.com>
Cc: pgsql-general@postgresql.org
Cc: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
Date: Fri, 2 Oct 2015 14:13:09 -0500
Message-ID: <560ED745.3050009@BlueTreble.com> (raw)
In-Reply-To: <560EB487.7010400@odoo.com>
References: <560AA479.4080807@odoo.com>
<560DB385.2080904@BlueTreble.com>
<560EB487.7010400@odoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Jim.Nasby@BlueTreble.com, odo+pggen@odoo.com, kgrittn@ymail.com
Subject: Re: Serialization errors despite KEY SHARE/NO KEY UPDATE
In-Reply-To: <560ED745.3050009@BlueTreble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox