More information on how to understand table pg_locks.

Hi All,

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.

I have two transactions (let's say A & B). These two transactions are
initiated by the same thread and I'm alone on the database and the
problem can be reproduced at will on postgresql. So this is not a random
problem.

I try to understand what I see in the pg_locks table but it looks like
what is found there should never occur according to the manual...

At deadlock time:

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.

-> Is there more information available somewhere in postgres system
tables ?

-> Could these foreign keys be the cause of this RowShareLock ?

For transaction B, I have a AccessShareLock, ExclusiveLock and
RowExclusiveLock on table X.

-> According to the manual, ExclusiveLock occurs only on system tables.

-> Is there a way to find why this lock is acquired ?

Do you know tools to help in debugging this ?

Any help is appreciated...

Thanks in advance