Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BFAF6632365 for ; Tue, 13 Jan 2009 17:04:46 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32704-03 for ; Tue, 13 Jan 2009 17:04:38 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 9F85B632251 for ; Tue, 13 Jan 2009 17:04:37 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n0DL4HXu001308; Tue, 13 Jan 2009 16:04:17 -0500 (EST) To: "Scott Whitney" cc: pgsql-admin@postgresql.org Subject: Re: Strange deadlock error last night In-reply-to: <20090113173555.80E667E4FD8@mail.int.journyx.com> References: <20090113173555.80E667E4FD8@mail.int.journyx.com> Comments: In-reply-to "Scott Whitney" message dated "Tue, 13 Jan 2009 11:37:00 -0600" Date: Tue, 13 Jan 2009 16:04:17 -0500 Message-ID: <1307.1231880657@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/35 X-Sequence-Number: 31082 "Scott Whitney" writes: > Last night, I got this: > Jan 13 03:31:28 db01 postgres[23537]: [140-2] DETAIL: Process 23537 waits > for AccessShareLock on relation 1260 of database 0; blocked by process > 22228. > Jan 13 03:31:28 db01 postgres[23537]: [140-3] Process 22228 waits for > AccessShareLock on relation 1262 of database 0; blocked by process 6816. > Jan 13 03:31:28 db01 postgres[23537]: [140-4] Process 6816 waits for > AccessShareLock on relation 1260 of database 0; blocked by process 14624. > Jan 13 03:31:28 db01 postgres[23537]: [140-5] Process 14624 waits for > AccessExclusiveLock on relation 1260 of database 0; blocked by process > 23537. > Jan 13 03:31:28 db01 postgres[14624]: [243-1] ERROR: deadlock detected Well, the problem is evidently that guy trying to get exclusive lock on relation 1260, which is pg_authid (a quick way to check that is "select 1260::regclass"). (Note: the "database 0" just means it's a system catalog that's shared across all databases.) pg_authid is touched often enough that trying to ex-lock it in an active database is just asking for trouble. Is it possible that that process was trying to run VACUUM FULL or REINDEX on pg_authid? I can't think of any other likely-sounding explanation for something trying to take an exclusive lock on that catalog. The best solution would be "don't do that" ... > It ended up locking up about 250 customer databases until I restarted the > postmaster. This is version 8.1.4. Upgrading right now (even to a minor rev) > is not really an option. Not related to the immediate problem, but: you really need to schedule 5 minutes' downtime so you can update to 8.1.latest. Reasons can be found here: http://www.postgresql.org/docs/8.1/static/release.html regards, tom lane