From: Ibrahim Harrani <ibrahim.harrani@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuum deadlock
Date: Mon, 1 Mar 2010 12:24:05 +0200
Message-ID: <530068a1003010224i4a56c44dxb3ab4066de1ccc5a@mail.gmail.com> (raw)
In-Reply-To: <14784.1265436696@sss.pgh.pa.us>
References: <530068a1002050143n26ee05c6hda0de69f5ed97a9d@mail.gmail.com>
<14784.1265436696@sss.pgh.pa.us>
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 <tgl@sss.pgh.pa.us> wrote:
> Ibrahim Harrani <ibrahim.harrani@gmail.com> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ibrahim.harrani@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: vacuum deadlock
In-Reply-To: <530068a1003010224i4a56c44dxb3ab4066de1ccc5a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox