idle in transaction process

Best Regards,
Abbas

On Mon, Aug 15, 2011 at 11:14 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 .
What else could be the reason for
this hung process ? What should I do to come over this kind of issue in
future. I think this hung process would have
taken locks on various tables. I wonder if killing the "idle in
transaction" process would cause the locks on the tables
to be released or not. Can anyone please help me on that.

Of course it is a slon process if it is not due to a network issue, then
might be any of your scripts, if not you can try by restarting the slon
process on origin.

Abbas.

Hi

Yes , restarting the slon resolves the issue. But. there are other processes
also in my application which connect to postgres . Those processes can also
cause "idle in transaction" postgres connection . 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" process after a certain amount of time . Will this
help or not ??

Thanks...
Tamanna

On Tue, Aug 16, 2011 at 11:56 AM, Abbas <abbas.dba@gmail.com> wrote:

Best Regards,
Abbas

On Mon, Aug 15, 2011 at 11:14 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 . What else could be the reason for
this hung process ? What should I do to come over this kind of issue in
future. I think this hung process would have
taken locks on various tables. I wonder if killing the "idle in
transaction" process would cause the locks on the tables
to be released or not. Can anyone please help me on that.

Of course it is a slon process if it is not due to a network issue, then
might be any of your scripts, if not you can try by restarting the slon
process on origin.

Abbas.

Thanks in Advance .

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

Can anyone please suggest me on this .

Thanks..
Tamanna

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

Hi

Yes , restarting the slon resolves the issue. But. there are other
processes also in my application which connect to postgres . Those
processes can also cause "idle in transaction" postgres connection . 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" process after a
certain amount of time . Will this help or not ??

Thanks...
Tamanna

On Tue, Aug 16, 2011 at 11:56 AM, Abbas <abbas.dba@gmail.com> wrote:

Best Regards,
Abbas

On Mon, Aug 15, 2011 at 11:14 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 . What else could be the reason for
this hung process ? What should I do to come over this kind of issue in
future. I think this hung process would have
taken locks on various tables. I wonder if killing the "idle in
transaction" process would cause the locks on the tables
to be released or not. Can anyone please help me on that.

Of course it is a slon process if it is not due to a network issue, then
might be any of your scripts, if not you can try by restarting the slon
process on origin.

Abbas.

Thanks in Advance .

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

--
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

On 08/18/11 2:17 AM, tamanna madaan wrote:

Yes , restarting the slon resolves the issue. But. there are other
processes also in my application which connect to postgres . Those
processes can also cause "idle in transaction" postgres connection .
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" process after
a certain amount of time . Will this help or not ??

Idle In Transaction means that the process has issued a BEGIN to start a
transaction but is sitting there doing nothing. if they stay that way
for hours, they block vacuum from freeing any tuples since the start of
the oldest transaction.. Long running Idle in Transaction processes
generally indicate a error in your program design.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On Sun, Aug 21, 2011 at 10:52 PM, tamanna madaan
<tamanna.madaan@globallogic.com> wrote:

Can anyone please suggest me on this .

Both the PostgreSQL and Slony versions you are running have known bugs
that cause problems. Update them first and see if your problem goes
away.