Not sure if this is still the correct solution (it's been a while) but we solved this back in the day by appending the parameter ?useBundledJdbcInfo=true <postgresql://your-db-server:5432/your-db-name?useBundledJdbcInfo=true> to the DB connection URL. Makes the Postgres plugin load jdbcInfo from the plugin bundle rather than the DB.
https://github.com/wocommunity/wonder/blob/73ef902e88130c1d9398458affc23a68431a582a/Frameworks/PlugIns/PostgresqlPlugIn/Sources/com/webobjects/jdbcadaptor/PostgresqlPlugIn.java#L108-L113 - hugi > On 15 Dec 2020, at 19:17, Markus Stoll, junidas GmbH via Webobjects-dev > <webobjects-dev@lists.apple.com> wrote: > > 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 >> <mailto: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 > > _______________________________________________ > 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/hugi%40karlmenn.is > > This email sent to h...@karlmenn.is
_______________________________________________ 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