Re: BUG #6207: fali to get lock on parent table after two consecutive updates to the same row in child table - Mailing list pgsql-bugs

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