"idle in transaction" postgres connection

--20cf3040e9c6973fab04aac4510b
Content-Type: text/plain; charset=ISO-8859-1

Hi All

I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used for
replication.
Recently , I saw a "idle in transaction" postgres process as below.

postgres 13052 14742 0 May13 ? 00:00:00 postgres: slon abc 172.16.1.1(49017)
idle in transaction

I wonder what could have lead to that hung postgres process . I googled
about it a lot and they say that it could be
because of abrupt netwotk issue between slony and postgres . But in my case
slon was connected
to its local postgres database. So, network wont be an issue in this case .
Restarting the slon/postgres resolves
the issue. But, there are other processes also in my application which
connect to postgres using psqlODBC . Those
processes can also cause "idle in transaction" postgres connection . I can't
afford restarting any of the processes
to resolve this issue. So, I was wondering if I can make use of
tcp_keepalives_idle , tcp_keepalives_interval and
tcp_keepalives_count configurations in postgresql.conf to get rid of this
"idle in transaction" processes
after a certain amount of time . Will this help or not ??


Thanks...
Tamanna

--20cf3040e9c6973fab04aac4510b
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi All

I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used=
for replication.
Recently , I saw a "idle in transaction" po=
stgres process as below.
=A0
postgres 13052 14742 0 May13 ? 00:00:00 =
postgres: slon abc 172.16.1.1(49017) idle in transaction

I wonder what could have lead to that hung postgres process . I googled =
about it a lot and they say that it could be
because of abrupt netwotk =
issue between slony and postgres . But in my case slon was connected

to its local postgres database. So, network wont be an issue in this case .=
Restarting the slon/postgres resolves
the issue. But, there are other =
processes also in my application=A0 which connect to postgres using psqlODB=
C . Those

processes can also cause "idle in transaction" postgres connectio=
n . I can't afford restarting any of the processes
to resolve this i=
ssue. So, I was wondering if I can make use of tcp_keepalives_idle , tcp_ke=
epalives_interval and

tcp_keepalives_count configurations in postgresql.conf to get rid of this &=
quot;idle in transaction" processes
after a certain amount of time=
. Will this help or not ??

Thanks...
Tamanna

=A0

Re: "idle in transaction" postgres connection

--20cf303dd7868ae3b404ab10d435
Content-Type: text/plain; charset=ISO-8859-1

Can anyone please suggest me on this .


Thanks..
Tamanna

On Thu, Aug 18, 2011 at 3:04 PM, tamanna madaan <
tamanna.madaan@globallogic.com> wrote:

>
> Hi All
>
> I am using postgres-8.4.0 on a cluster setup with slony-2.0.4 being used
> for replication.
> Recently , I saw a "idle in transaction" postgres process as below.
>
> postgres 13052 14742 0 May13 ? 00:00:00 postgres: slon abc
> 172.16.1.1(49017) idle in transaction
>
> I wonder what could have lead to that hung postgres process . I googled
> about it a lot and they say that it could be
> because of abrupt netwotk issue between slony and postgres . But in my case
> slon was connected
> to its local postgres database. So, network wont be an issue in this case .
> Restarting the slon/postgres resolves
> the issue. But, there are other processes also in my application which
> connect to postgres using psqlODBC . Those
> processes can also cause "idle in transaction" postgres connection . I
> can't afford restarting any of the processes
> to resolve this issue. So, I was wondering if I can make use of
> tcp_keepalives_idle , tcp_keepalives_interval and
> tcp_keepalives_count configurations in postgresql.conf to get rid of this
> "idle in transaction" processes
> after a certain amount of time . Will this help or not ??
>
>
> Thanks...
> Tamanna
>
>
>



--
Tamanna Madaan | Associate Consultant | GlobalLogic Inc.
Leaders in Software R&D Services
ARGENTINA | CHILE | CHINA | GERMANY | INDIA | ISRAEL | UKRAINE | UK | USA

Office: +0-120-406-2000 x 2971

www.globallogic.com

--20cf303dd7868ae3b404ab10d435
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Can anyone please suggest me on this .


Thanks..
Tamanna
r>