Re: Deadlock and FK triggers

Bo Lorentsen wrote:
> HI ...
>
> I have been using PG for a large project at my work (soon in
> production), and I use PG and a lot for FK/RI checks. This works nice
> when using one or two threads (cuncurrent connections), but more than
> this, and I start getting deadlocks in my FK triggers.
>
> After this, I have been reading all the mail threads I can find in the
> PG mailing lists, on this subject and I found some some minor fixes. One
> have made a patch for PG, but the patch also remove some of the
> garanties. The other is the DEFERRABLE fix, that fix or postpone some of
> this. But no real solutions, as far as I can see.
>
> Now that we have version 8.0, and this have been a know problem for some
> time, I like to know what the status of this is ? Will there be another
> way for a trigger to lock the reffered table other that "FOR UPDATE",
> that is more like a RW lock, or will we get a way to control the
> sequence in which FK triggers are executed ? As a last solution, all FK
> triggers could be executed in a alnumeric sequence, based on the tabel name.
> So ... what is the plan ?
>
> When designing a somewhat complex DB this become a relativly large
> problem, and this don't sound nice to my boss if I have to tell his
> these thing :-(

We hope to have shared row locks in 8.1 for this usage.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073