Thread: BUG #4243: Idle in transaction

The following bug has been logged online:

Bug reference:      4243
Logged by:          Clemens Wagner
Email address:      clemens.wagner@denkwerk.com
PostgreSQL version: 8.2.7 and 8.3.1
Operating system:   Linux 2.6.18-53.1.14.el5
Description:        Idle in transaction
Details:

I have sometimes hanging psql backend processes with "idle in transaction"
state:

------ 8< ------
 499 ?        Ss     0:00 postgres: logger process
 501 ?        Ss     0:00 postgres: writer process
 502 ?        Ss     0:00 postgres: wal writer process
 503 ?        Ss     0:00 postgres: autovacuum launcher process
 504 ?        Ss     0:03 postgres: stats collector process
1896 ?        Ss     0:04 postgres: seis survey 127.0.0.1(40753) SELECT
waiting
1917 ?        Ss     0:03 postgres: seis survey 127.0.0.1(58432) idle in
transaction
2139 ?        Ss     0:00 postgres: seis survey 127.0.0.1(40649) idle
2410 ?        Ss     0:03 postgres: seis survey 127.0.0.1(33320) SELECT
waiting
12797 ?        Ss     0:00 postgres: autovacuum worker process   survey
21315 ?        Ss     0:00 postgres: seis survey 127.0.0.1(55182) idle
21342 ?        Ss     0:01 postgres: seis survey 127.0.0.1(33722) idle
21730 ?        Ss     0:00 postgres: seis survey 127.0.0.1(52894) idle
------ >8 ------

They block other processes from working. I know that this state indicates a
non-terminated transaction. If I kill the process from shell with SIGTERM
and I got the folowing stack trace:

------ >8 ------
Caused by: org.postgresql.util.PSQLException: FATAL: terminating connection
due
to administrator command
       at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:1592)
       at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1327)
       at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:192)
       at
org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionComman
d(AbstractJdbc2Connection.java:650)
       at
org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Co
nnection.java:687)
       at
com.denkwerk.database.jdbc.JDBCContext.rollbackTransaction(JDBCContex
t.java:189)
       ... 60 more
------ 8< ------

I think this problem occurs while a (auto) vacuum is running and there is
high traffic on the database. It leads sometimes to an inconsisten database
with double primary keys in a table or a broken unique indexes (
"pg_statistic_relid_att_index" for instance). I can't enforce this error.

On Tue, Jun 17, 2008 at 06:49:57AM +0000, Clemens Wagner wrote:
> I have sometimes hanging psql backend processes with "idle in transaction"

There is no bug here.  Some transaction is stuck waiting for something
else, or else you've left a transaction hanging around that way (is
your JDBC driver old?  It used to issue BEGIN right after COMMIT when
in non-autocommit mode.  That'd cause this issue).

You need to figure out what's blocking what.  Look in the pg_locks
system view.  See the manual for more on this.

> high traffic on the database. It leads sometimes to an inconsisten database
> with double primary keys in a table or a broken unique indexes (
> "pg_statistic_relid_att_index" for instance). I can't enforce this error.

I don't believe this is the problem you're having there.  If what
you're saying is the case, you need to look for breakage elsewhere.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/