Hi Aaron, for each of my instances I have two database connections to the postgres DB - obviously one with the idle transaction and the other working one. And each idle transaction originates from the woa startup. So your conclusion sounds reasonable. But I still did not find where this select statement is created...
Markus > Am 15.12.2020 um 19:14 schrieb Aaron Rosenzweig <aa...@chatnbike.com>: > > 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 <mailto: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 <mailto: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 >> > 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
smime.p7s
Description: S/MIME cryptographic signature
_______________________________________________ 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
