Hello,
Deferred checks can greatly reduce the deadlock chance because of the timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign keys.
Sincerely,
Joshua D. Drake
Ben wrote:
Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer seems to be, "don't do this." On the other hand, pretty much every message on the subject is pre-7.4. There is some mention of using deferred foreign keys to reduce the chance for a deadlock, but nothing says doing that actually eliminates the chance.
Is this just a known limitation? In this particular instance, I probably could get rid of my foreign keys and if things go bad it wouldn't hurt anything.... but I make heavy use of foreign keys throughout the rest of my schema, which are useful for the programs that aren't doing data mining. I wouldn't want to get rid of those foreign keys.
On Jun 16, 2004, at 8:54 AM, Csaba Nagy wrote:
Hi Ben,
Check this mailing list for "foreign keys" and "deadlock". Short info: Postgres exclusively locks the referenced records of a foreign key relationship when the child record is updated, so multiple runs (in different transactions) of one insert query could cause deadlock if they update rows which reference the same parent keys in reverse order. Check your foreign keys...
HTH, Csaba.
On Wed, 2004-06-16 at 17:33, Ben wrote:
I'm doing a bunch of data mining against a postgres database and have run into an interesting problem with deadlocks. The problem is, postgres is detecting them and then wacking the offending process, and I can't figure out what's causing them. I have a ton of select queries (but none for update), and then a single query to insert into a table. Nothing selects from that table. So where could the deadlock be?
pg_stat_activity has a column named current_query, which would seem useful in tracking this down, but it's not being populated.
Oh, I'm running 7.4.2.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
