From: | "Thomas Chille" <thomas(dot)chille(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | autovacuum process blocks without reporting a deadlock |
Date: | 2007-11-23 12:25:29 |
Message-ID: | cad2de1c0711230425n712203a7nd7f5e9731db1565a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi anybody,
I step in just one of our identically customer databases in a kind of
a deadlock with Autovacuum involved.
One Autovacuum process stuck in the middle of the night and it seemed
that it compete with another Select process for an index:
[14398 / 2007-11-21 00:52:04 CET]CONTEXT: SQL statement "UPDATE
hst_timerecording SET id_timerecording_join = NULL WHERE
id_timerecording_join = -1"
PL/pgSQL function "set_id_timerecording_join" line 121 at SQL statement
SQL statement "UPDATE hst_timerecording SET sales_volume =
NULL, sales_volume_commission = NULL WHERE business_day = $1 AND
id_employee = $2 "
PL/pgSQL function "compress_salaries_day" line 168 at SQL statement
SQL statement "SELECT compress_salaries_day( $1 , NULL, NULL)"
PL/pgSQL function "compress" line 460 at perform
[14398 / 2007-11-21 00:52:04 CET]LOCATION: exec_stmt_raise, pl_exec.c:2110
[14391 / 2007-11-21 00:52:14 CET]DEBUG: 00000: index
"hst_timerecording_id_timerecording_idx" now contains 8537 row
versions in 61 pages
[14391 / 2007-11-21 00:52:14 CET]DETAIL: 4454 index row versions were removed.
12 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 3.47 sec.
[14391 / 2007-11-21 00:52:14 CET]LOCATION: lazy_vacuum_index, vacuumlazy.c:736
This are the last log entires for these both processes. Over 9 hours
later, i can see them allready running in the process list :
14391 ? S 0:00 postgres: autovacuum process
backoffice_db
14398 ? S 0:02 postgres: spoon backoffice_db office(39302)
SELECT waiting
This happens every night and a dump restore wont help.
I dont set any explicit locks and so i would expect that no deadlock
could occure? And when, then i would expect that it would be logged
together with the dead locked relations?
Can anyone give a tipp, please?
Version: PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 4.0.2 (Debian 4.0.2-2)
regards
thomas
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-11-23 12:32:58 | Re: autovacuum process blocks without reporting a deadlock |
Previous Message | Sam Mason | 2007-11-23 11:49:04 | Re: Primary Key |