Re: More information on how to understand table pg_locks.

I'm running 'PostgreSQL 8.2.0 on i686pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)'.

My intuition about the lock acquired foreign keys seems right.

Here is a short description: (if it can help someone else one day...)
create table y
{
y_id bigint not null,
y_count int not null,
primary key (y_id)
}

create table x
{
x_id bigint not null,
x_y_id bigint not null,
primary key (x_id),
foreign key (x_id) references y(y_id)
};

Transaction A:
0: begin
1: insert into x (x_id, x_y_id) values (0, 0)

Transaction B:
2: begin
3: select * from y
4: update y set y_count=y_count+1 where y_id=0
5: commit

Transaction A:
6: commit

Transaction B is simply blocked by transaction A at step 4.
Since these two transactions are in the same thread, my application
freezes.
If I declare the foreign key as 'deferable initially defered' there is
no freeze because the lock on y is acquired just before the commit in
transaction A. So I found a solution to my problem.

Now I understand what happen.
Now I'm worried it doesn't happen on other rdbms. :-)

I will write a small/simplified test case for my application and try to
run it on every supported rdbms and validate what I say more precisely.
Other rdbms seems not to block in this scenario. But, once understood,
the behaviour of postgresql seems perfectly reasonable.

Thanks for your help !

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: mardi 13 mars 2007 16:57
To: Olivier Ceulemans
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] More information on how to understand table
pg_locks.

"Olivier Ceulemans" <Olivier(dot)Ceulemans(at)irislink(dot)com> writes:
> I wrote an application making 'advanced' use of transactions. The
> application is not tied to a specific kind of database. It runs fine
on
> oracle, db2, sql server but I run into a deadlock on postgresql.
> ...
> For transaction A, I have a RowShareLock on table X. I have no idea on
> how/why this RowShareLock is acquired by my application. In
transaction
> A, I never read/write 'directly' to the table X. However I create data
> in other tables that have foreign keys to table X.

What Postgres version is this? Before 8.1 we used exclusive row locks
to enforce that referenced rows didn't go away before a referencing
row could be committed, and this made it easy to hit unexpected
deadlocks.

regards, tom lane