Re: [ADMIN] how to find transaction associated with a lock

Si Chen <schen(at)graciousstyle(dot)com> writes:
> Do you think I'm looking down the wrong path?

I'd bet that the problem is contention for a row referenced by a foreign
key. When you insert a row that has a foreign key reference to another
table, we need to lock the referenced row to ensure it doesn't get
deleted until the inserting transaction commits. (Once you commit, your
inserted row is visible and it's then the responsibility of a deleting
transaction to notice the foreign-key violation. But until then, a
deleting transaction can't even *see* your row so we need another way.)

The real problem here is that we only have exclusive locks at the row
level, so the only way to do this is to take an exclusive lock, and that
means that transactions inserting references to the same referenced row
block each other. So I think your INSERT is waiting on commit of some
other transaction's INSERT that references the same foreign key row.

Fixing this is on the TODO list but I don't expect any solution in the
near future :-(

regards, tom lane