X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B67F8D1B436 for ; Mon, 29 Dec 2003 20:34:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 75028-10 for ; Mon, 29 Dec 2003 16:33:59 -0400 (AST) Received: from lorax.kcilink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id B42E4D1B43C for ; Mon, 29 Dec 2003 16:33:57 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id 39A9F3E42 for ; Mon, 29 Dec 2003 15:33:58 -0500 (EST) Received: from lorax.kcilink.com ([127.0.0.1]) by localhost (lorax.kcilink.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 02693-01-2 for ; Mon, 29 Dec 2003 15:33:57 -0500 (EST) Received: from lorax.kcilink.com (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id 9606F3E33 for ; Mon, 29 Dec 2003 15:33:57 -0500 (EST) Received: (from news@localhost) by lorax.kcilink.com (8.12.9p2/8.12.9/Submit) id hBTKXvGA024420 for pgsql-performance@postgresql.org; Mon, 29 Dec 2003 15:33:57 -0500 (EST) (envelope-from news) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: deferred foreign keys Date: Mon, 29 Dec 2003 15:33:57 -0500 Organization: Khera Communications, Inc., Rockville, MD Lines: 27 Message-ID: NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kcilink.com 1072730037 59545 65.205.34.180 (29 Dec 2003 20:33:57 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Mon, 29 Dec 2003 20:33:57 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, berkeley-unix) Cancel-Lock: sha1:9TYKO5cEojn18uHttgg36sHTv1E= X-Virus-Scanned: by amavisd-new at kciLink.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/312 X-Sequence-Number: 5172 I'm observing that when I have many processes doing some work on my system that the transactions run along almost in lockstep. It appears from messages posted here that the foreign keys are acquiring and holding locks during the transactions, which seems like it would cause this behavior. I'd like to experiment with deferred foreign key checks so that the lock is only held during the commit when the checks are done. My questions are: 1) can I, and if so, how do I convert my existing FK's to deferrable without drop/create of the keys. Some of the keys take a long time to create and I'd like to avoid the hit. 2) do I increase the liklihood of deadlocks when the FK locks are being acquired or is it just as likeley as with the current non-deferred checking? I'm running 7.4 (soon to be 7.4.1) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/