how to avoid deadlocks? - Mailing list pgsql-novice

From Gabriele Messineo
Subject how to avoid deadlocks?
Date
Msg-id 200811060938.10653.gabriele.messineo@winext.eu
Whole thread Raw
List pgsql-novice
Hi,
I would like to ask you an advice because I have a database that seems to
deadlock on Exclusive locks every couple of days.

The issue comes at night when I reindex some tables, it seems unable to
perform the task (a reindex on a table of 75 records for 2 relpages after
several hours is still running), and next queries and next analyzes are
locked (and becomes everytime worst).

On that table there are lots of joins and selections (also quite heavy
queries), but I don't think it's the load that cause the issue (it doesn't
happen on heavier installations).

However I would expect a serialization of them that would slowly process all
the queries in the queue. Why is the DBMS instead blocking?
Am I doing something wrong? Given the load on the DBMS have I to stop querying
before reindexing the tables?

PS: I'm using debian package  postgresql-8.3 = 8.3.4-2 . If it can be useful I
collected some data from pg_stat_activity and pg_class.

Thanks and regards,

Gabriele Messineo

pgsql-novice by date:

Previous
From: Tim Bowden
Date:
Subject: Re: basic security
Next
From: "Leví Teodoro da Silva"
Date:
Subject: [DUMP] Make a dump with special characters