On 10/30/07, Achilleas Mantzios <[EMAIL PROTECTED]> wrote: > Στις Τρίτη 30 Οκτώβριος 2007 10:45, ο/η Patrick De Zlio έγραψε: > > 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? > > Some connection pools issue a BEGIN on the connection just > after the previous user of the connection issues COMMIT and > then closes it (Jboss does it). So, <IDLE> in transaction is not > apriori bad.
You can turn off that behaviour, IIRC. It is broken behaviour as far as pgsql is concerned, I believe. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster