I'm sorry Tom. This happens (at least) both in 8.4 and 9.0. I've just tested it in 2 different databases with the same results. I have workarounds to this, but I was wondering what could be the actual root of the problem, in order to (try to) achieve the best solution possible.
Following is a test case with which you can reproduces the issue. 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); -- session T1 begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- Query returned successfully: 1 row affected, 47 ms execution time. rollback; -- session T1 rollback; begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- ERROR: could not obtain lock on row in relation "b". SQL state: 55P03 Regarding my original question, I would like to know if this is a known issue/feature/bug/unwanted optimization consequence/or is it just a normal behavior that I should've had predicted. Best regards, Eduardo. On Wed, Sep 7, 2011 at 9:29 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Eduardo Piombino <drak...@gmail.com> writes: > > I don't see how a new update to the same record in A, makes the > difference > > to allow or deny the lock on a row on table B; > > I think it's probably explained by this: > > > PS: The only relation between A and B is that A has a two FKs to B, but > none > > of them are even included in the updates. > > IIRC there are some optimizations in the FK stuff that don't apply once > a single transaction has updated a relevant row more than once. You > haven't given enough details (not even a PG version) to be sure about > it, but that's what I'd bet on. > > regards, tom lane >
