Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B7814632178 for ; Mon, 1 Mar 2010 06:24:18 -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 81223-10 for ; Mon, 1 Mar 2010 10:24:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com [209.85.219.226]) by mail.postgresql.org (Postfix) with ESMTP id 790E362DBD2 for ; Mon, 1 Mar 2010 06:24:07 -0400 (AST) Received: by ewy26 with SMTP id 26so1168988ewy.3 for ; Mon, 01 Mar 2010 02:24:06 -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=xm1P1iLxe9IdiZofoy+uQcAPIQc1Fr0U52ZBuufXs7A=; b=UwOvXaLfXK9J36DXb8csvSenWGfLNhjX6D8mIqytIMa6eviQA2uHZf3WIAIfhij70/ 5UBlcXkkPgg2nTvOkCqdmubFNbj0iPR8ePAydJ4xsWLnp+Dk44Jm8ZhGlJ+IhTVdw4gp C2u+rGYH0FOty7ZPdNVvOXirLQbYAzdwyov1o= 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=IfZpy7cN9BeD0nz5uezsiXoENOFc+6PZo2JHuQn66xNQck3W5TMltDdWRABZEDvJVu PJLOoy50XF4SgkrI5wKKHv3uLwbmRmRPjYS+eKpNhSnV7yibCKr3MFtQR9dAU2HHaXyu VN2gFM0gRz4fU6hPa9e7DE0OikTtfCjiBrTHs= MIME-Version: 1.0 Received: by 10.213.109.219 with SMTP id k27mr2705847ebp.37.1267439045936; Mon, 01 Mar 2010 02:24:05 -0800 (PST) In-Reply-To: <14784.1265436696@sss.pgh.pa.us> References: <530068a1002050143n26ee05c6hda0de69f5ed97a9d@mail.gmail.com> <14784.1265436696@sss.pgh.pa.us> Date: Mon, 1 Mar 2010 12:24:05 +0200 Message-ID: <530068a1003010224i4a56c44dxb3ab4066de1ccc5a@mail.gmail.com> Subject: Re: vacuum deadlock From: Ibrahim Harrani To: Tom Lane Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=000e0ce0af6e1b89180480baa6d8 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: 201003/1 X-Sequence-Number: 33800 --000e0ce0af6e1b89180480baa6d8 Content-Type: text/plain; charset=ISO-8859-1 Hi, Yes, I am trying to full vacuum. I think, I found the problem. There were more then 10 million records which my scripts trying to delete at night from a table while other processes were adding new entries to same table. Delete can't finish in the day, and script tries to delete the table again in next night via crontab then this cause to dead lock? SQL query: "delete from mytable where createtime < now - '10 days'; We added 1K limit option to delete query in a for loop. Now I can delete all entries but slowly DELETE FROM mytable WHERE id = any (array(SELECT id FROM mytable WHERE createtime < NOW() - INTERVAL '10 day' LIMIT 1000)) I am planning to partion(day based) this large table, then I will simply drop the tables after certain days. Do you have any other suggestion? Thanks. On Sat, Feb 6, 2010 at 8:11 AM, Tom Lane wrote: > Ibrahim Harrani writes: > > 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. > > vacuumdb -f you mean? An ordinary vacuum wouldn't be trying to take > AccessExclusiveLock. > > It might be that you have an instance of a failure that was identified > just a couple weeks ago: > http://archives.postgresql.org/pgsql-committers/2010-01/msg00199.php > > regards, tom lane > --000e0ce0af6e1b89180480baa6d8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi,

Yes,=A0 I am trying to full vacuum.

I think, I=A0 found t= he problem. There were more then 10 million records which my scripts trying= to delete at night from a table while other processes were adding new entr= ies to same table.
Delete can't finish in the day, and script tries to delete the table ag= ain in next night via crontab
then this cause to dead lock?

SQL q= uery: "delete from mytable where createtime < now - '10 days= 9;;


We added 1K limit option to delete query in a for loop. Now I can d= elete all entries but slowly=A0

DELETE FROM mytable WHERE id =3D an= y (array(SELECT id FROM mytable WHERE createtime < NOW() - INTERVAL '= ;10 day'=A0 LIMIT 1000))

I am planning to partion(day based) this large table, then I will simpl= y drop the tables after certain days.

Do you have any other suggesti= on?

Thanks.

On Sat, Feb 6, 2010 at= 8:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ibrahim Harrani <ibrahim.h= arrani@gmail.com> writes:
> I got =A0a deadlock while vacuuming all databases with vacuumdb comman= d.

> vacuumdb: vacuuming of database "mydb" failed: ERROR: =A0dea= dlock detected
> DETAIL: =A0Process 1294 waits for AccessExclusiveLock on relation 2662= of
> database =A0 =A0 ; blocked by process 1807.
> Process 1807 waits for AccessShareLock on relation 1259 of database 16= 389;
> blocked by process 1294.

vacuumdb -f you mean? =A0An ordinary vacuum wouldn't be trying to= take
AccessExclusiveLock.

It might be that you have an instance of a failure that was identified
just a couple weeks ago:
http://archives.postgresql.org/pgsql-committers/201= 0-01/msg00199.php

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane

--000e0ce0af6e1b89180480baa6d8--