Re: BUG #6503: Idle in transaction while lazy loading in JSF render response

The following bug has been logged on the website:

Bug reference: 6503
Logged by: Jo Vreven
Email address: j(dot)vreven(at)aca-it(dot)be
PostgreSQL version: 9.0.0
Operating system: MAC OSX
Description:

This bug was not in jdbc4 driver: 8.4-701
But is introduced in jdbc4 driver: 8.4-702
It is still present in 9.1-901

Context:
* Tomcat 7
* JPA2 Hibernate
* Atomikos
* Spring
* JTA transaction manager (using org.postgresql.xa.PGXADataSource)

===

If in the JSF lifecycle render-response an element is fetched by means of
lazy loading (that was not fetched in invoke-application lifecycle) a
transaction will be started but it remains idle in transaction for
postgres.

The JTA Transaction manager has no active transactions at that moment, so
for JTA everything seems commit/rollback.

The connection seems to be back in the connectionpool, since the transaction
was used for only read operations no harm is done, and the connection
becomes idle for postgres when the next thread invokes a commit on this
connection.

Workaround 1: Use version 8.4-701 or lower
Workaround 2: JSF phaselistener to begin and rollback transaction before and
after render-response.

<j(dot)vreven(at)aca-it(dot)be> wrote:

> This bug was not in jdbc4 driver: 8.4-701
> But is introduced in jdbc4 driver: 8.4-702
> It is still present in 9.1-901

You might get this in front of a more appropriate group of people if
you post to the pgsql-jdbc list. I'm moving discussion to that list.

> Context:
> * Tomcat 7
> * JPA2 Hibernate
> * Atomikos
> * Spring
> * JTA transaction manager (using org.postgresql.xa.PGXADataSource)
> ===
>
> If in the JSF lifecycle render-response an element is fetched by
> means of lazy loading (that was not fetched in invoke-application
> lifecycle) a transaction will be started but it remains idle in
> transaction for postgres.
>
> The JTA Transaction manager has no active transactions at that
> moment, so for JTA everything seems commit/rollback.
>
> The connection seems to be back in the connectionpool, since the
> transaction was used for only read operations no harm is done, and
> the connection becomes idle for postgres when the next thread
> invokes a commit on this connection.

Instead of assuming that someone can install a matching software
stack and replicate the problem, can you determine what methods are
invoked on which objects to manifest the problem, and create a
simple test case that can demonstrate the issue with just the test
case source code and a JDBC jar?

> Workaround 1: Use version 8.4-701 or lower

Are you able to try reverting portions of the difference between 701
and 702 to see which make a difference? If you ignore the
translation and test code, the changes weren't huge. It was
probably this commit:

https://github.com/pgjdbc/pgjdbc/commit/482c77d67efdcaf2b7db16c96bba20ea34bc294c

or maybe this one:

https://github.com/pgjdbc/pgjdbc/commit/cf625c7ba2647825b0e3995da3604785f14fa20e

Do you see anything wrong with either of those?

> Workaround 2: JSF phaselistener to begin and rollback transaction
> before and after render-response.

I have no idea what that means in terms of what statements are run,
or when.

-Kevin

Thanks for the reply Kevin.

I understand what you are saying, but at this moment unfortunately I
dont have the time on my project to create small test cases or
rollback certain commits.

I know this is a very specific technoligie stack, that's what made it
so hard to find anything about this problem online. And that's the
reason I would like to put something online about it, together with
the two work arounds I have tested.

My best bet was to post this on the postgres site, since this was one
of my first places to search.

Again, thanks for the reply, and maybe someday I or someone else is
able to provide more information about this.

Jo

On 02 Mar 2012, at 21:52, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> <j(dot)vreven(at)aca-it(dot)be> wrote:
>
>> This bug was not in jdbc4 driver: 8.4-701
>> But is introduced in jdbc4 driver: 8.4-702
>> It is still present in 9.1-901
>
> You might get this in front of a more appropriate group of people if
> you post to the pgsql-jdbc list. I'm moving discussion to that list.
>
>> Context:
>> * Tomcat 7
>> * JPA2 Hibernate
>> * Atomikos
>> * Spring
>> * JTA transaction manager (using org.postgresql.xa.PGXADataSource)
>> ===
>>
>> If in the JSF lifecycle render-response an element is fetched by
>> means of lazy loading (that was not fetched in invoke-application
>> lifecycle) a transaction will be started but it remains idle in
>> transaction for postgres.
>>
>> The JTA Transaction manager has no active transactions at that
>> moment, so for JTA everything seems commit/rollback.
>>
>> The connection seems to be back in the connectionpool, since the
>> transaction was used for only read operations no harm is done, and
>> the connection becomes idle for postgres when the next thread
>> invokes a commit on this connection.
>
> Instead of assuming that someone can install a matching software
> stack and replicate the problem, can you determine what methods are
> invoked on which objects to manifest the problem, and create a
> simple test case that can demonstrate the issue with just the test
> case source code and a JDBC jar?
>
>> Workaround 1: Use version 8.4-701 or lower
>
> Are you able to try reverting portions of the difference between 701
> and 702 to see which make a difference? If you ignore the
> translation and test code, the changes weren't huge. It was
> probably this commit:
>
> https://github.com/pgjdbc/pgjdbc/commit/482c77d67efdcaf2b7db16c96bba20ea34bc294c
>
> or maybe this one:
>
> https://github.com/pgjdbc/pgjdbc/commit/cf625c7ba2647825b0e3995da3604785f14fa20e
>
> Do you see anything wrong with either of those?
>
>> Workaround 2: JSF phaselistener to begin and rollback transaction
>> before and after render-response.
>
> I have no idea what that means in terms of what statements are run,
> or when.
>
> -Kevin