On 11/1/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> Στις Î¤ÎµÏ„Î¬Ï Ï„Î· 31 ÎŸÎºÏ„ÏŽÎ²Ï Î¹Î¿Ï‚ 2007 23:47, ο/η Scott Marlowe ÎÎ³Ï Î±ÏˆÎµ:
> > On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> 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.
>
> Note nice surely, but why broken?
Because you're not really IN a transaction. Plus, in some snapshot
systems, you could pick up an idle connection that is idle in
transaction that gives you a three day old snapshot. The time to
issue a begin is when you are beginning your transaction, not right
after finishing the last one.
Also, the connection that's idle in transaction will prevent
postgresql from being able to properly vacuum (in versions before 8.3)
meaning that it can cause your datastore to suffer from bloat.