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

Attachment: 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

Reply via email to