Обсуждение: 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.vreven@aca-it.be
PostgreSQL version: 9.0.0
Operating system:   MAC OSX
Description:=20=20=20=20=20=20=20=20

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)

=3D=3D=3D

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.vreven@aca-it.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