Re: idle in transaction - should I care?

If you think about it a bit, the phenomenon you observe is very easy to
explain:

Since the java.sql.Connection does not have a beginTransaction (or
equivalent) method, you have to expect that the driver automatically starts
a new transaction after the previous one has been committed. So basically I
see no problems with the output you posted.

What you should be careful about is what the Enhydra DatabaseManager does
with connection objects behind the scenes. For example, it might provide API
for you to return connections after they are not needed. It also might
expect that you reset the connection object to the sate it was in before you
obtained it. The adverse side effects of misunderstanding the behaviour of a
connection management system/framework are unlikely to manifest themselves
in such a short output as you posted.

Peter

> -----Original Message-----
> From: Jeremy Ferry [mailto:jferry@iexposure.com]
> Sent: Thursday, March 07, 2002 10:18 PM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] idle in transaction - should I care?
>
>
> Hi all.  I am new to PostgreSQL and JDBC so any help would be
> appreciated...
>
> I've included some code that essentially represents a servlet
> that gets an
> image from a PostgreSQL database.  It appears that the
> transaction begins,
> executes, and ends correctly but then starts another query.
> Where is that
> last query comming from?
>
> public void run( HttpPresentationComms comms ) throws
> HttpPresentationException {
>         DBConnection conn = null;
>
>         try {
>             comms.response.setContentType( "image/jpg" );
>             conn = Enhydra.getDatabaseManager().allocateConnection();
>             conn.setAutoCommit( false );
>             ResultSet rs = conn.executeQuery(
>         "SELECT IMAGE_DATA FROM IMAGE WHERE IMAGE_ID = " +
> comms.request.getParameter( "id" ) );
>             byte[] data = null;
>             if ( rs.next() ) {
>                  data = rs.getBytes( source );
>             }
>
>             conn.commit();
>             conn.close();
>
>             comms.response.getOutputStream().write( data );
>
>         } catch ( Exception e ) {
>             try {
>                 conn.rollback();
>             } catch ( SQLException sqle ) {
>                 System.err.println( "ERROR rolling back" );
>                 sqle.printStackTrace();
>             }
>             e.printStackTrace();
>         } finally {
>             if ( conn != null ) {
>                 conn.release();
>             }
>         }
>     }
>
>
> Below is what I find in the PostgreSQL log.  Notice that the
> query executes
> and commits then another query begins.  Nothing else happens
> after this and
> the process says "idle in transaction".
>
>
> 2002-03-07 14:56:16 DEBUG:  query: begin
> 2002-03-07 14:56:16 DEBUG:  ProcessUtility: begin
> 2002-03-07 14:56:16 DEBUG:  query: SELECT IMAGE_DATA FROM IMAGE WHERE
> IMAGE_ID = 1131
> 2002-03-07 14:56:16 DEBUG:  query: select proname, oid from
> pg_proc where
> proname = 'lo_open'    or proname = 'lo_close'    or proname
> = 'lo_creat'
> or proname = 'lo_unlink'    or proname = 'lo_lseek'    or
> proname = 'lo_tell'
>    or proname = 'loread'    or proname = 'lowrite'
> 2002-03-07 14:56:16 DEBUG:  query: commit
> 2002-03-07 14:56:16 DEBUG:  ProcessUtility: commit
> 2002-03-07 14:56:16 DEBUG:  query: begin
> 2002-03-07 14:56:16 DEBUG:  ProcessUtility: begin
>
> I have several questions about this:  Am I doing something
> wrong?  Is this
> normal/expected behavior?  Will it cause problems?
>
> --
> Jeremy Ferry
> jferry@iexposure.com
> Internet Exposure Inc.
> http://www.iexposure.com
>
> (612) 676-1946 (ext 18)
> Web Development - Web Marketing - ISP Services
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>