Re: Postgresql & Deadlocks

On 10 September 2010 08:03, Machiel Richards <machielr(at)rdc(dot)co(dot)za> wrote:
> Good day all
>
>
>            I just wanted to follow up again on a previous post by asking the
> questions differently after doing a lot of research into the matter.
>
>            A previous post was related to table locking on postgresql and
> current locks monitoring providing counts including those for access share
> locks as well.
>
>
>             What we are more interested in monitoring though is more
> specifically deadlocks on the databases, however from some research into the
> matter it seems that Postgresql is supposed to manage deadlocks on its own
> as it is detected by killing one of the processes.
>
>
>              Thus, from what I understand it is not something that should
> really require monitoring (even though we are still looking for methods to
> monitor the amount detected during the last 24 hours.).
>
>               I would however now like to know the following, in terms of
> server performance and availability, is there any aspects of table/row
> locking that can be monitored, which can perhaps cause problems?
>
>            Any other suggestions for something specific to postgresql
> databases that needs to be kept an eye on for the same reason would also be
> appreciated as I am fairly new to postgresql.

Are you aware of the pg_locks system catalog?
http://www.postgresql.org/docs/8.4/static/view-pg-locks.html

SELECT * FROM pg_catalog.pg_locks;

You can use that for monitoring locks.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935