don't keep open pgsql transaction when trac is idle

[Radim Kolar]

When trac is idle, postgresql database connection is in midle of transaction status

53878 ?? Is 0:01.46 postgres: trac trac [local] idle in transaction

If trac with low user activity is running on server, this behaviour has nasty system-wide effects.

1. You can't VACUUM DB. You can but no rows are cleaned
2. You can't replicate DB

until transaction is closed. On lowly loaded Trac, it can take days, so it harms other db users. Close transaction while idle please.

[cboos]

Also reported by someone else on the user list: googlegroups:trac-users:f73a346b6797578e

I think this might be related to concurrent sync attempts (#4586) failing and left without doing the appropriate rollback.

[cboos]

Forgot to say: if this is indeed the case, then the current 0.10.4dev (r5153) should already fix this.

[Sonic]

Trac is definitively holding on to connections for too long. I get the same problem with Trac 0.10.4 on PosgreSQL 8.2.4. With 3 Trac environments, each as a separate schema in one database, the process list displays a lot of posgresql positions hanging as idle in transaction.

What kind of info can I provide to help solve it?

[kent@…]

Hi. I use Trac 0.9.3+PostgreSQL

I try to understand why my Trac server became too slow. I found a lot of established connections even when nobody use Trac. Command ps -aef give such output:

postgres  9709     1  0 Jan09 ?        00:00:01 /usr/bin/postmaster -D /home/postgresql/data
postgres  9782  9709  0 Jan09 ?        00:00:00 postgres: writer process
postgres  9783  9709  0 Jan09 ?        00:00:00 postgres: stats buffer process
postgres  9957  9709  0 12:02 ?        00:00:00 postgres: tracadmin kent 127.0.0.1(34090) idle
postgres 10113  9709  0 12:39 ?        00:00:00 postgres: tracadmin svninfo 127.0.0.1(32960) idle
postgres 10123  9709  0 12:39 ?        00:00:00 postgres: tracadmin svninfo 127.0.0.1(32961) idle
postgres 10378  9709  0 13:44 ?        00:00:00 postgres: tracadmin l-process-draft 127.0.0.1(44468) idle
postgres 10395  9709  0 13:45 ?        00:00:00 postgres: tracadmin lirxca_0_6_4 127.0.0.1(44469) idle
postgres 10404  9709  0 13:45 ?        00:00:00 postgres: tracadmin lirxca_0_6_4 127.0.0.1(44470) idle
postgres 10493  9709  0 13:51 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(33534) idle
postgres 10531  9709  0 13:52 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(33535) idle
postgres 10536  9709  0 13:52 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(33536) idle
postgres 10557  9709  0 13:52 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(33537) idle
postgres 10565  9709  0 13:52 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(33538) idle
postgres 10593  9709  0 14:00 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(53450) idle
postgres 10621  9709  0 14:00 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(53452) idle
postgres 10657  9709  0 14:06 ?        00:00:00 postgres: tracadmin l-process-draft 127.0.0.1(34525) idle
postgres 10676  9709  0 14:07 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(34526) idle
postgres 10687  9709  0 14:07 ?        00:00:00 postgres: tracadmin lirvpn 127.0.0.1(34527) idle

Is it the same trouble as described in this ticket?

P.S.: sorry for my English.

[danimo]

I can still confirm this with a recent version of trac 0.11. This is really nasty, as one runs out of postgres connections very quickly.

[Jennifer Drummond <jenn@…>]

I had r7190 recommended to me as a patch that might help, but unfortunately it's from farther up on trunk and doesn't apply cleanly against 0.11. Anyone feel like tweaking it for backwards compatibility?

[cboos]

That changeset has been ported to branches/0.11-stable, simply upgrade to latest stable from svn (TracDownload#Tracstable).

It will be very appreciated if you report back whether the problem got fixed or not after the upgrade, so that we could eventually close this ticket.

[cboos]

Jonas, I see that t.e.o is running using r7316, how is this doing w.r.t to this issue? Do you think we can close it now?

[jonas]

Yes I think we can close this for now. I've not seen this on t.e.o for a while now and the new pool should be better at closing unused connections.