Re: Server error and deadlocks

On Tuesday 14 January 2003 15:11, Stephan Szabo wrote:
> 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.

What I meant is: is the PostgreSQL development team aware of the problem? Is
there a proposed fix that's not implemented yet?

BTW, where in the Postgres sources can I find the code responsible for locking
foreign keys during and INSERT/UPDATE?

> > 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.

Do you know of any way to decrease the lock strength without modifying
Postgres' sources?

Why should the weaker lock exist at the SQL statement level? Why can't you
have some kind of internal read-write lock so that you can lock a foreign key
for writing when inserting/updating a row that references it, but still allow
reading this foreign key from other transactions?

--
Juan Jose Comellas
(juanjo(at)comellas(dot)com(dot)ar)