Has anyone dealt with SQL statements that linger and get stuck “idle in transaction” ?
We started looking carefully at our WO app and Postgres data store. We discovered that it wasn’t vacuuming because of these hung statements. Turns out it appears to be deep into WO, when we do fetches sometimes there is a “begin” with no “commit” - Predominantly it is a select from “pg_catalog.pg_type” as a fetch of jdbcInfo. Doing a select doesn’t really warrant a “begin/commit” but since it starts with a “begin” it should cleanup with a “commit” but it often doesn’t do that… which yields “idle in transaction” If you are using Postgres, you might want to run the following to see if you have anything stuck in this state: SELECT pid, datname, usename, state, backend_xmin,query_start,xact_start, age(backend_xmin), backend_start,age(now(), pg_stat_activity.backend_start), state_change, query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL and state = 'idle in transaction’; For now, we are sidestepping the issue by forcing PG to cut those loose if they are older than 5 minutes. This allows vacuum to occur. It’s not ideal, but not a bad workaround either. Anyone have any thoughts or experience with this? Cheers, — Aaron _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
