Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 4E1F06347F1 for ; Fri, 5 Feb 2010 14:48:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 33217-01-9 for ; Fri, 5 Feb 2010 18:47:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 9368F634DD4 for ; Fri, 5 Feb 2010 14:47:35 -0400 (AST) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 05 Feb 2010 12:47:34 -0600 Message-Id: <4B6C1362020000250002F02E@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Fri, 05 Feb 2010 12:47:30 -0600 From: "Kevin Grittner" To: "Ibrahim Harrani" , "Yeb Havinga" Cc: Subject: Re: vacuum deadlock References: <530068a1002050143n26ee05c6hda0de69f5ed97a9d@mail.gmail.com> <4B6C10BA.8050003@gmail.com> <530068a1002051014r65c160aej81d267cf30cb600e@mail.gmail.com> In-Reply-To: <530068a1002051014r65c160aej81d267cf30cb600e@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.468 tagged_above=-10 required=5 tests=AWL=0.131, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/51 X-Sequence-Number: 33654 Ibrahim Harrani wrote: > Yeb Havinga wrote: >> Ibrahim Harrani wrote: >>> I am using PostgreSQL 8.3.7. >>> autovacuum is enabled in postgresql.conf >>> >>> I got a deadlock while vacuuming all databases with vacuumdb >>> command. >>> >>> vacuumdb: vacuuming of database "mydb" failed: ERROR: deadlock >>> detected >>> DETAIL: Process 1294 waits for AccessExclusiveLock on relation >>> 2662 of database ; blocked by process 1807. >>> Process 1807 waits for AccessShareLock on relation 1259 of >>> database 16389; blocked by process 1294. >>> >>> I think, because of this lock, deleting a large table (15 >>> million records) was taking too much time >>> How can I know the name of the relations 2662 and 1259? >>> >> postgres=# select oid,relname from pg_class where oid in >> (2662,1259); >> oid | relname ------+-------------------- >> 2662 | pg_class_oid_index >> 1259 | pg_class >> (2 rows) > As far as I know, pg_class_oid_index and pg_class are postgresql > internal relations. > > Normally, there is not application that directly using those > relations. > How should I map this lock issue on this relations to my > application side? > On which cases those are locked and used by applications? Are you creating temporary tables somehow during this process, like within functions (including trigger functions)? Can you test the delete with autovacuum disabled? When you say "deleting a large table" do you mean, dropping the table, deleting all rows from the table, or deleting some of the rows from the table? If you could show the actual delete statement and all information about the table(s) involved, including indexes, triggers, and foreign keys, that might help. -Kevin