On 15.09.2011 06:55, Eduardo Piombino wrote:
Problem came when I tried to update the child table a, which holds a fk to
b, after doing two updates to the same record in a. The other session will
fail to obtain the lock after the second update (but won't fail immediately
after the first one).
Tom Lane suggested that it may have something to do to some optimization in
the management of the foreign keys after two updates to the same row in the
child table, but I got no concrete answer as if this behavior is what is
expected from the engine or not.
...
Following is a test case with which you can very easily reproduce the
issue.
Open two pg sessions to the database.
Statements should be executed in the following order.
-- session T1 means that the following lines should be executed from pg
session 1.
-- session T2 means that the following lines should be executed from pg
session 2.
create table b (
id bigint not null,
x double precision,
constraint pk_b primary key (id));
create table a (
id bigint not null,
id_b bigint,
x double precision,
constraint pk_a primary key (id),
constraint fk_b foreign key (id_b) references b (id));
insert into b (id, x) values (1, 0);
insert into a (id, id_b, x) values (1, 1, 0);
[updates using two sessions]
Those first updates on the tables are unnecessary, this test case can be
simplified into just:
-- session T1
begin transaction;
update a set x = x + 1 where id = 1;
update a set x = x + 1 where id = 1;
(now switch to the second terminal)
-- session T2
select * from b where id = 1 for update nowait;
As Tom suggested, this an artifact of the way foreign key triggers are
queued. The first update takes advantage of this optimization (in
AfterTriggerSaveEvent function):
/*
* If this is an UPDATE of a PK table or FK table that does not change
* the PK or FK respectively, we can skip queuing the event: there is
* no need to fire the trigger.
*/
The second UPDATE, however, falls into this exception later in that
function:
/*
* If this is an UPDATE of a PK table or FK table that does not change
* the PK or FK respectively, we can skip queuing the event: there is
* no need to fire the trigger.
*/
...
/*
* Update on FK table
*
* There is one exception when updating FK tables: if the
* updated row was inserted by our own transaction and the
* FK is deferred, we still need to fire the trigger. This
* is because our UPDATE will invalidate the INSERT so the
* end-of-transaction INSERT RI trigger will not do
* anything, so we have to do the check for the UPDATE
* anyway.
*/
We can't distinguish a row that was UPDATEd earlier in the same
transaction from a row that was INSERTed earlier in the same
transaction. So on the second UPDATE, the foreign-key trigger is fired,
and the trigger locks the tuple in table b.
That's not ideal, of course, but I don't see any easy way to fix that.
Perhaps we could add some extra checks there, like whether any
INSERT-triggers have actually been queued. But it would just narrow the
issue, I don't see a way to completely eliminate it.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
