pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Patrick De Zlio <pchemla01@gmail.com>
To: pgsql-admin@postgresql.org
Subject: How to kill process "idle in transaction"
Date: Tue, 30 Oct 2007 10:45:16 +0200
Message-ID: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> (raw)

Hi,

We are running PostgreSQL 8.2.4 with quite highly updated millions rows
tables.

It comes sometimes to get some "idle in transaction" processes, which
doesn't have first really viewable effect out of giving very bad estimated
numbers for pgclass.reltuples and, if we let them run as it is, block the
autovacuum from releasing rows, and after few days some worse problems.

Now we are able to detect these "idle in transaction" processes, but what to
do with them ?

What to do to avoid such situation? How to find the bad request which put
the process in this state? From the time it is idle in transaction, we can't
see the query in the pg_lock table anymore.

How to deal with such process when it is already idle in transaction? can we
simply kill it? It is a backend from pgpool. Can we simply restart it (how)
without restarting all the database?

Thanks for help

view thread (6+ messages)  latest in thread

Message-ID: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com>
Permalink:  ../58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com/
Also on:    postgresql.org/message-id/58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com

 · 

reply

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: pchemla01@gmail.com
  Subject: Re: How to kill process "idle in transaction"
  In-Reply-To: <58729a240710300145m4457f63euc15007893e8a805c@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