Re: Server error and deadlocks

On Tue, 14 Jan 2003, Juan Jose Comellas wrote:

> Does anybody know if there is a plan to improve the foreign key support in
> PostgreSQL?

Umm, define plan. Seriously, I've been looking at it, but I've only got
a couple of hours a week in general, so it's not going terribly
well/quickly.

> While working with PostgreSQL 7.2.1 (Debian Linux/testing) we found out that
> when a row is inserted in a table that has columns that are foreign keys,
> Postgres normally locks the rows corresponding to the foreign keys (in their
> original tables) both for reading and for writing. This is strange, because
> it seems to me that it should allow reading from these rows (at least Oracle
> 8i does this) from other transactions. According to Postgres' logs, a SELECT
> FOR UPDATE is executed on each of the foreign keys referenced in an INSERT,
> UPDATE. Isn't this a little bit excessive?

Yes, but there isn't a weaker lock that exists at the SQL statement level
currently that gives enough strength to actual guarantee the constraint.

Actually, getting the lock strength down is fairly easy with doing a
dirty read and some stuff with that so that you can say insert pointing
to the same row from concurrent transactions, it's dealing with the
created and existing deadlock conditions that's hard.