Hi Markus, So that means you, too, have some queries that are stuck. Postgres is waiting for you to issue a commit. As long as they live, it will not be able to vacuum properly. When you close down the .woa, it will no longer be “idle in transaction”
It’s a curious thing. For me it appears to be from jdbcInfo() when a new objectStore/editingContext does a fetch it leaves that around for the life of the .woa instance. > On Dec 15, 2020, at 2:41 AM, Markus Stoll, junidas GmbH > <markus.st...@junidas.de> wrote: > > Hi Aaron, > > did the same on my single WO system using postgres (with only moderate load), > but on your sql query I DO get some result rows. Did no yet have time for > further analysis > > Regards, Markus > >> Am 14.12.2020 um 22:29 schrieb Aaron Rosenzweig via Webobjects-dev >> <webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>>: >> >> 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 >> <mailto:Webobjects-dev@lists.apple.com>) >> Help/Unsubscribe/Update your Subscription: >> https://lists.apple.com/mailman/options/webobjects-dev/markus.stoll%40junidas.de >> >> <https://lists.apple.com/mailman/options/webobjects-dev/markus.stoll%40junidas.de> >> >> This email sent to markus.st...@junidas.de > > Mit freundlichen Grüßen > > Markus Stoll > > -- > Dr. Markus Stoll (Geschäftsführer) > markus.st...@junidas.de <mailto:markus.st...@junidas.de> > > junidas GmbH, Aixheimer Str. 12, 70619 Stuttgart > Tel. +49 (711) 4599799-11, Fax +49 (711) 4599799-10 > Geschäftsführer: Dr. Markus Stoll, Matthias Zepf > Amtsgericht Stuttgart, HRB 21939 >
_______________________________________________ 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
