Foreign key order evaluation

[Shawn Chisholm]

Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me...

Thanks,

Shawn

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Michael Fuhr]

On Tue, Sep 28, 2004 at 10:27:21PM +0000, Randy Yates wrote:

I see the light. You mean it would be nice to be able to have a "LOCK-FOR-UPDATE-ONLY"
lock as well as a "LOCK-FOR-UPDATE-OR-READ" lock, but all you have now is
"LOCK-FOR-UPDATE-OR-READ" and that gets applied even when you don't care if
someone else reads the record?

Right. The current implementation acquires an exclusive lock (FOR
UPDATE -- what you're referring to as LOCK-FOR-UPDATE-OR-READ); it
would be nice to have a lock that could be shared so multiple
transactions could acquire it at the same time without blocking.
Then all transactions could read the foreign key record, but no
transaction could modify it until the other transactions completed
and released their locks.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster