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

Reply via email to