Idle in transaction
Hi
I'm a postgres newbie. I just implemented a new web application using
postgres.
When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.
They never go to idle state.
The application itself remains functional and responsive. It has been up
for over 36 hours now without any issues.
What is the significance of this state? Does this imply a transaction
leak? Then why am I not noticing deadlocks, timeouts etc.
Thanks
Sid
Sorry. Forgot to mention the postgres version
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
________________________________
From: Sharma, Sid
Sent: Friday, July 17, 2009 10:05 AM
To: pgsql-general@postgresql.org
Subject: Idle in transaction
Hi
I'm a postgres newbie. I just implemented a new web application using
postgres.
When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.
They never go to idle state.
The application itself remains functional and responsive. It has been up
for over 36 hours now without any issues.
What is the significance of this state? Does this imply a transaction
leak? Then why am I not noticing deadlocks, timeouts etc.
Thanks
Sid
In response to "Sharma, Sid" <ssharma@bjs.com>:
I'm a postgres newbie. I just implemented a new web application using
postgres.
You mention that you're using PG 8.1.3, which is very old. You'll save
yourself a lot of headaches if you at least upgrade to the latest 8.1.
But that is not part of your issue, it's just a side note.
When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.They never go to idle state.
That's bad. It means your client program is starting a transaction and
leaving it running without doing anything with it. This is an issue with
the way the client is programmed, or with the client drivers, not with
the server. The server is doing what it's told.
The reason this is bad is that PG can't properly complete maintenance if
there are transactions that are left open constantly. Eventually your
DB will fill up the entire disk with old data that can't be cleaned up.
The application itself remains functional and responsive. It has been up
for over 36 hours now without any issues.What is the significance of this state? Does this imply a transaction
leak? Then why am I not noticing deadlocks, timeouts etc.
Check the design of your app. If it issues a BEGIN, then sits there, you
need to configure it to only issue a BEGIN when it's actually ready to do
some work, and issue a COMMIT when the work is complete. Simply leaving a
connection open won't cause this.
If you're not explicitly issuing a BEGIN, then it may be a bug in the
client driver, or a misunderstanding on your part as to how to use the
driver. If you tell the list what client library you're using, I'm sure
there are folks who can offer more detailed insight.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
On Friday 17 July 2009 07:17:15 am Sharma, Sid wrote:
Sorry. Forgot to mention the postgres version
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
________________________________
From: Sharma, Sid
Sent: Friday, July 17, 2009 10:05 AM
To: pgsql-general@postgresql.org
Subject: Idle in transactionHi
I'm a postgres newbie. I just implemented a new web application using
postgres.When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.They never go to idle state.
The application itself remains functional and responsive. It has been up
for over 36 hours now without any issues.What is the significance of this state? Does this imply a transaction
leak? Then why am I not noticing deadlocks, timeouts etc.Thanks
Sid
In my case it was caused by a select statement that I neither issued a commit
or rollback. So you have started a transaction without closing it somewhere
in your app. My app continued to work without apparent issue. The only
thing I noticed was pgAdmin3 could not make changes to the data structure
without closing my app.
So somewhere in your code you have started a transaction without closing it.
Johnf
On Fri, Jul 17, 2009 at 8:05 AM, Sharma, Sid<ssharma@bjs.com> wrote:
Hi
I’m a postgres newbie. I just implemented a new web application using
postgres.When I look at the db connections (via ps), I notice that all existing
connections are in ‘Idle in Transaction’ state.They never go to idle state.
Then it's likely a bug in your application / connection / pooling
software. I'm gonna guess you're using Java and jdbc. But it could
be something else.
The application itself remains functional and responsive. It has been up for
over 36 hours now without any issues.
Many ships stay afloat for hours after suffering fatal collisions with
icebergs. The likely problem here will be that your tables will
slowly bloat with dead tuples because vacuum can't reclaim space.
Eventually either your db will slow to a crawl or you'll run out of
disk space.
What is the significance of this state? Does this imply a transaction leak?
No, it implies broken application / connection / pooling code.
Then why am I not noticing deadlocks, timeouts etc.
Because that's not what happens when connections are left idle in transaction.
Sharma, Sid wrote:
Sorry. Forgot to mention the postgres version
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
Why on earth are you using that version? At the very least upgrade to
8.1.17 and if this is a new app, why not use 8.4?
When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.They never go to idle state.
You don't say how you are connecting to the DB, but it's probably your
connection manager. Are you running some sort of connection pooling perhaps?
--
Richard Huxton
Archonet Ltd
On Fri, Jul 17, 2009 at 8:17 AM, Sharma, Sid<ssharma@bjs.com> wrote:
Sorry. Forgot to mention the postgres version
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
Oh, and update to the latest 8.1.x version. There are some nasty bugs
in 8.1.3 if I recall. Won't fix this issue, but why run old
un-updated pg code?
Thx for all your response
@Bill Moran
You mention that you're using PG 8.1.3, which is very old. You'll
save
yourself a lot of headaches if you at least upgrade to the latest 8.1.
But that is not part of your issue, it's just a side note.
I'm sure it is outdated. When I suggested a new application I should
have said a new module within an existing application. In other words,
the db pre-exists. We are rewriting our entire application(s) and moving
our database to DB2 and Oracle starting pretty soon. So postgres is end
of life here and so there is no stomach or interest to upgrade.
That's bad. It means your client program is starting a transaction
and
leaving it running without doing anything with it. This is an issue
with
the way the client is programmed, or with the client drivers, not with
the server. The server is doing what it's told.The reason this is bad is that PG can't properly complete maintenance
if
there are transactions that are left open constantly. Eventually your
DB will fill up the entire disk with old data that can't be cleaned
up.
The module/application is read-only and so only does SELECTs. There are
very infrequent writes to the database. In fact writes have not happened
yet since our launch. I can monitor disk utilization to see if it's
growing but since we only do queries, I would be surprised that were to
be true. But then again, I do not know postgres.
If you're not explicitly issuing a BEGIN, then it may be a bug in the
client driver, or a misunderstanding on your part as to how to use the
driver. If you tell the list what client library you're using, I'm
sure
there are folks who can offer more detailed insight.
It's a jdbc driver. The jar file's manifest does not contain the
version. I noticed the timestamp of all the files in the jar are in
2002.
How do I find out?
@Scott Marlowe
Then why am I not noticing deadlocks, timeouts etc.
Because that's not what happens when connections are left idle in
transaction.
Then what should I look for? This is a query-only module. It is not
adding any data to the database. The data in the tables remains static.
I have been watching the number of connections and that has remained
static i.e. at the min level of the connection pool.
@Richard Huxton
You don't say how you are connecting to the DB, but it's probably your
connection manager. Are you running some sort of connection pooling
perhaps?
Yes a jdbc pool. There is another web application that uses the same
implementation of the pool (albeit a different instance of it) and
connections within it are in idle state as you would expect. So the
connection pool implementation itself appears to have an alibi.
@Johnf
In my case it was caused by a select statement that I neither issued a
commit or rollback. So you have started a transaction without closing
it
somewhere in your app. My app continued to work without apparent
issue.
The only thing I noticed was pgAdmin3 could not make changes to the
data
structure without closing my app.
So somewhere in your code you have started a transaction without
closing
it.
So there is no imminent danger then of catastrophic failure such as
memory full or disk full or cpu spikes or db slowness given my
application only does queries? I'm trying to figure out whether I should
chill this weekend on the beach or kiss that good-bye and work this
issue? :-)
I am setting auto-commit to on, so shouldn't a commit be issued once the
select executes (or a rollback on a sql exception)? I guess I'm not 100%
sure of txn semantics with the postgres driver that I have. Do you think
that despite setting the auto commit mode to off, I still need to issue
explicit commits or rollbacks?
-----Original Message-----
From: Bill Moran [mailto:wmoran@potentialtech.com]
Sent: Friday, July 17, 2009 10:44 AM
To: Sharma, Sid
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle in transaction
In response to "Sharma, Sid" <ssharma@bjs.com>:
I'm a postgres newbie. I just implemented a new web application using
postgres.
You mention that you're using PG 8.1.3, which is very old. You'll save
yourself a lot of headaches if you at least upgrade to the latest 8.1.
But that is not part of your issue, it's just a side note.
When I look at the db connections (via ps), I notice that all existing
connections are in 'Idle in Transaction' state.They never go to idle state.
That's bad. It means your client program is starting a transaction and
leaving it running without doing anything with it. This is an issue
with
the way the client is programmed, or with the client drivers, not with
the server. The server is doing what it's told.
The reason this is bad is that PG can't properly complete maintenance if
there are transactions that are left open constantly. Eventually your
DB will fill up the entire disk with old data that can't be cleaned up.
The application itself remains functional and responsive. It has been
up
for over 36 hours now without any issues.
What is the significance of this state? Does this imply a transaction
leak? Then why am I not noticing deadlocks, timeouts etc.
Check the design of your app. If it issues a BEGIN, then sits there,
you
need to configure it to only issue a BEGIN when it's actually ready to
do
some work, and issue a COMMIT when the work is complete. Simply leaving
a
connection open won't cause this.
If you're not explicitly issuing a BEGIN, then it may be a bug in the
client driver, or a misunderstanding on your part as to how to use the
driver. If you tell the list what client library you're using, I'm sure
there are folks who can offer more detailed insight.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote:
I'm sure it is outdated. When I suggested a new application I should
have said a new module within an existing application. In other words,
the db pre-exists. We are rewriting our entire application(s) and moving
our database to DB2 and Oracle starting pretty soon. So postgres is end
of life here and so there is no stomach or interest to upgrade.
But you're developing new code targeted to Pg?
That's bad. It means your client program is starting a transaction
and
leaving it running without doing anything with it. This is an issue
with
the way the client is programmed, or with the client drivers, not with
the server. The server is doing what it's told.The reason this is bad is that PG can't properly complete maintenance
if
there are transactions that are left open constantly. Eventually your
DB will fill up the entire disk with old data that can't be cleanedup.
The module/application is read-only and so only does SELECTs.
That doesn't matter if any _other_ module is performing UPDATEs (or
inserts and deletes) to the tables your module operates on. Your
module's open transactions may still prevent PostgreSQL from reclaiming
space, because it needs to keep the old versions of tuples around.
(I'm struggling, right now, to explain why this is so - if this is so -
for READ COMMITTED transactions, though it certainly is for SERIALIZABLE
transactions. For READ COMMITTED the transaction's own uncommitted
changes can't be discarded vacuumed, but these are presumably read-only
transactions anyway - and they don't need to be able to see any old
tuples other than any they may have inserted/modified. Will open READ
COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER,
sure, but plain VACUUM?)
Your uncommitted transactions will also make it very hard to make schema
changes such as adding or altering columns to your tables.
If your module is the only one active on these tables, and it's only
doing read activity, then it doesn't really matter very much that the
transactions are left open. It's kind of ugly, though.
So there is no imminent danger then of catastrophic failure such as
memory full or disk full or cpu spikes or db slowness given my
application only does queries? I'm trying to figure out whether I should
chill this weekend on the beach or kiss that good-bye and work this
issue? :-)
I am setting auto-commit to on, so shouldn't a commit be issued once the
select executes (or a rollback on a sql exception)?
Yes, it should. Are you certain your app is using autocommit across the
board, though, and that something else (say, your connection pooler)
isn't "helping" you by turning it back off again?
If you turn autocommit back off and issue a rollback before handing the
connection back to the connection pooler, does the issue go away?
Personally I prefer to explicitly manage transactions anyway. Most Java
code/libraries seem to expect autocommit to be off, it makes it easier
to guarantee that read-only code is exactly that (by issuing a rollback
when finished), and for read/write queries I like to know for certain
when data has been committed to the database.
--
Craig Ringer
That doesn't matter if any _other_ module is performing UPDATEs (or
inserts and deletes) to the tables your module operates on. Your
module's open transactions may still prevent PostgreSQL from
reclaiming
space, because it needs to keep the old versions of tuples around.
There are no updates to the underlying tables. The tables are updated as
part of a release only. They contain static data only. The data is used
for calculations only and is thus read-only.
(I'm struggling, right now, to explain why this is so - if this is so
-
for READ COMMITTED transactions, though it certainly is for
SERIALIZABLE
transactions. For READ COMMITTED the transaction's own uncommitted
changes can't be discarded vacuumed, but these are presumably
read-only
transactions anyway - and they don't need to be able to see any old
tuples other than any they may have inserted/modified. Will open READ
COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER,
sure, but plain VACUUM?)
I had no idea what vacuum was till I read your post and googled. Yup I'm
a true blue newbie. I guess I still know very little. But if vacuum is a
compaction and stats gathering utility and is table specific, then I
guess I'm not worried even if it cannot run on my tables because the
data itself is static.
Your uncommitted transactions will also make it very hard to make
schema
changes such as adding or altering columns to your tables.
We never do this. I'm not worried about this.
If your module is the only one active on these tables, and it's only
doing read activity, then it doesn't really matter very much that the
transactions are left open. It's kind of ugly, though.
I do intend to diagnose and fix the issue.
I was trying to ascertain the urgency of the issue to figure out whether
to forfeit a spectacular summer weekend. It sounds like that a
catastrophe is not imminent and can wait for module version 1.1 in about
a month. Cape Cod here I come.
Yes, it should. Are you certain your app is using autocommit across
the
board, though, and that something else (say, your connection pooler)
isn't "helping" you by turning it back off again?
I re-checked the code and auto commit mode is not being turned on. So my
bad there! I know what to do to fix the issue.
Thanks for all your help
Sid
-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au]
Sent: Friday, July 17, 2009 2:55 PM
To: Sharma, Sid
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Idle in transaction
On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote:
I'm sure it is outdated. When I suggested a new application I should
have said a new module within an existing application. In other words,
the db pre-exists. We are rewriting our entire application(s) and
moving
our database to DB2 and Oracle starting pretty soon. So postgres is
end
of life here and so there is no stomach or interest to upgrade.
But you're developing new code targeted to Pg?
That's bad. It means your client program is starting a transaction
and
leaving it running without doing anything with it. This is an issue
with
the way the client is programmed, or with the client drivers, not
with
the server. The server is doing what it's told.
The reason this is bad is that PG can't properly complete
maintenance
if
there are transactions that are left open constantly. Eventually
your
DB will fill up the entire disk with old data that can't be cleaned
up.
The module/application is read-only and so only does SELECTs.
That doesn't matter if any _other_ module is performing UPDATEs (or
inserts and deletes) to the tables your module operates on. Your
module's open transactions may still prevent PostgreSQL from reclaiming
space, because it needs to keep the old versions of tuples around.
(I'm struggling, right now, to explain why this is so - if this is so -
for READ COMMITTED transactions, though it certainly is for SERIALIZABLE
transactions. For READ COMMITTED the transaction's own uncommitted
changes can't be discarded vacuumed, but these are presumably read-only
transactions anyway - and they don't need to be able to see any old
tuples other than any they may have inserted/modified. Will open READ
COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER,
sure, but plain VACUUM?)
Your uncommitted transactions will also make it very hard to make schema
changes such as adding or altering columns to your tables.
If your module is the only one active on these tables, and it's only
doing read activity, then it doesn't really matter very much that the
transactions are left open. It's kind of ugly, though.
So there is no imminent danger then of catastrophic failure such as
memory full or disk full or cpu spikes or db slowness given my
application only does queries? I'm trying to figure out whether I
should
chill this weekend on the beach or kiss that good-bye and work this
issue? :-)
I am setting auto-commit to on, so shouldn't a commit be issued once
the
select executes (or a rollback on a sql exception)?
Yes, it should. Are you certain your app is using autocommit across the
board, though, and that something else (say, your connection pooler)
isn't "helping" you by turning it back off again?
If you turn autocommit back off and issue a rollback before handing the
connection back to the connection pooler, does the issue go away?
Personally I prefer to explicitly manage transactions anyway. Most Java
code/libraries seem to expect autocommit to be off, it makes it easier
to guarantee that read-only code is exactly that (by issuing a rollback
when finished), and for read/write queries I like to know for certain
when data has been committed to the database.
--
Craig Ringer