Re: Idle in transaction
Re: Idle in transaction
- Subject: Re: Idle in transaction
- From: Hugi Thordarson via Webobjects-dev <email@hidden>
- Date: Tue, 15 Dec 2020 20:11:00 +0000
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
> <email@hidden> 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 <email@hidden
>> <mailto:email@hidden>>:
>>
>> 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
>>> <email@hidden <mailto:email@hidden>> 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
>>>> <email@hidden <mailto:email@hidden>>:
>>>>
>>>> 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 (email@hidden
>>>> <mailto:email@hidden>)
>>>> Help/Unsubscribe/Update your Subscription:
>>>>
>>>>
>>>> This email sent to email@hidden <mailto:email@hidden>
>>>
>>> Mit freundlichen Grüßen
>>>
>>> Markus Stoll
>>>
>>> --
>>> Dr. Markus Stoll (Geschäftsführer)
>>> email@hidden <mailto:email@hidden>
>>>
>>> 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)
> email@hidden <mailto:email@hidden>
>
> 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 (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden