Обсуждение: How to kill process "idle in transaction"

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

Στις Τρίτη 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.

However if in your system you have lock problems,
and transactions that dont get rollbacked or commited,
then it is a problem with your application.

Have you done any monitoring on the size of the pool?
You should look at:
1) Connections get closed eventually
2) Transactions either have the autocommit flag on, are commited or are
rollbacked.

>
> Thanks for help

--
Achilleas Mantzios

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.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 10/30/07, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>> 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.

As of 8.3 it should be harmless, but in existing releases it's bad news.

            regards, tom lane

Στις Τετάρτη 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?

--
Achilleas Mantzios

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.