Re: Server error and deadlocks

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

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

Yes and not entirely. There almost certainly is a complete
solution through the use of dirty reads and a bit of magic to prevent
deadlocks, but it's that bit of magic that I haven't managed to completely
wrap my brain around. If you look through the archives, you'll find an
incomplete patch for one of my tests for new code (doesn't entirely work).
I've started a couple more various attempts that haven't entirely worked
either, but as I said, I certainly haven't been spending alot of time on
it.

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

IIRC, backend/utils/adt/ri_triggers.c.

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

The triggers currently work by using SPI to run statements
(select/update/delete), so anything that the triggers want to do would
have to work within that framework unless the triggers were completely
rewritten.