Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C038463376D for ; Fri, 5 Feb 2010 14:14:29 -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 27002-06 for ; Fri, 5 Feb 2010 18:14:08 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f222.google.com (mail-ew0-f222.google.com [209.85.219.222]) by mail.postgresql.org (Postfix) with ESMTP id D5F3A632D07 for ; Fri, 5 Feb 2010 14:14:18 -0400 (AST) Received: by ewy22 with SMTP id 22so1097437ewy.34 for ; Fri, 05 Feb 2010 10:14:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=M06wrNy1ac+9+B5PqnkCm9p6Ymd5Rx8L+mRcAMeKI+w=; b=p+8pIrboWQt61lKchcIeaf6uoi/HcGru7LHEtvFN4g5xabUzJzmUwpk/qt4TEpYdJ0 kEyjQuXS7MhDCKLqn1CTg7ler9/spu9CU2ylzIMevvrZwgPFSTZ2CL8IUS6+gai0GvRT C8NFxqZupCy/ZnHc0/UD8FRzYHiPI2DUnMYNA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=SlNH6BaKEDITNwCnAnmIPS/uTx6RAz4Kt8+7Y5GaRdQ4UJC2Bg4z8ysDEZtlOSUcOB 8dhCdtiinLVFaBAziOZcYO55DdfEKS6mgrUNyEOW3Pdk6sPZaZPkIJfVzT2D+wzowQS1 FfNlZtNFe9d3QFcVsT6fFZDx+DCe/WUHJSJfc= MIME-Version: 1.0 Received: by 10.213.104.69 with SMTP id n5mr899780ebo.20.1265393657394; Fri, 05 Feb 2010 10:14:17 -0800 (PST) In-Reply-To: <4B6C10BA.8050003@gmail.com> References: <530068a1002050143n26ee05c6hda0de69f5ed97a9d@mail.gmail.com> <4B6C10BA.8050003@gmail.com> Date: Fri, 5 Feb 2010 20:14:17 +0200 Message-ID: <530068a1002051014r65c160aej81d267cf30cb600e@mail.gmail.com> Subject: Re: vacuum deadlock From: Ibrahim Harrani To: Yeb Havinga Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=001636c5a68373379d047ede6b02 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/50 X-Sequence-Number: 33653 --001636c5a68373379d047ede6b02 Content-Type: text/plain; charset=ISO-8859-1 Hi Yeb, Thanks for your reply. 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? Thanks On Fri, Feb 5, 2010 at 2:36 PM, Yeb Havinga wrote: > Ibrahim Harrani wrote: > >> Hi, >> >> 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) > > Do you have any ide about the problem and solution? >> > It would be interesting to know what are the processes 1807 and 1294. Once > that is known, try to avoid executing both processes concurrently. > > regards > Yeb Havinga > > --001636c5a68373379d047ede6b02 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi Yeb,

Thanks for your reply.
As far as I know, pg_class_oid_in= dex and pg_class are postgresql internal relations.

Normally, there= is not application that directly using those relations.
How should I ma= p this lock issue on this relations to my application side?
On which cases those are locked and used by applications?


Thanks=

On Fri, Feb 5, 2010 at 2:36 PM, Yeb Havi= nga <yebhaving= a@gmail.com> wrote:
Ibrahim Harrani wrote:
Hi,

I am using PostgreSQL 8.3.7.
autovacuum is enabled in postgresql.conf

I got =A0a deadlock while vacuuming all databases with vacuumdb command.
vacuumdb: vacuuming of database "mydb" failed: ERROR: =A0deadlock= detected
DETAIL: =A0Process 1294 waits for AccessExclusiveLock on relation 2662 of d= atabase =A0 =A0 ; 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=3D# select oid,relname from pg_class where oid in (2662,1259);
oid =A0| =A0 =A0 =A0relname =A0 =A0 =A0------+--------------------
2662 | pg_class_oid_index
1259 | pg_class
(2 rows)

Do you have any ide about the problem and solution?
It would be interesting to know what are the processes 1807 and 1294. Once = that is known, try to avoid executing both processes concurrently.

regards
Yeb Havinga


--001636c5a68373379d047ede6b02--