Re: SET TRANSACTION ISOLATION LEVEL

Humm. I never considered needing to rollback a transaction that was
read-only, but Iguess that makes sense.

So, if I am trying to read large objects, then I suppose that my code
should look like this, with a finally to insure that the rollback happens?

Connection conn;
try {
  // do queries...
} finally {
  conn.rollback();
}
// now connection can be reused

Is there any reason to prefer commit over rollback if the query is
read-only? I would think that they would both simply clean up and release
locks as there were not any changes made.

thanks,
Philip


On Mon, 25 Feb 2002, Barry Lind wrote:

> Philip,
>
>  From the log file you have provided, the problem is that you are
> continuing to use a connection after an error has been reported.  After
> any error in postgresql you need to rollback and start a new
> transaction.  You cannot catch an error and continue processing.  The
> error you are seeing is as a result of trying to continue processing
> after the error without having done a rollback.
>
> thanks,
> --Barry
>
> Philip Crotwell wrote:
>
> >Hi
> >
> >I got this error, it isn't the first transaction since the server was
> >started, so I don't understant what it means. The docs seem to suggest
> >that the READ COMMITTED level is default. If there is a default, why do I
> >need to set it?
> >
> >I am AFAIK running single threaded in my java code, so I don't think I
> >would be dong two separate transactions in the same connection.
> >
> >postgres 7.1. and this code involved is to get large objects, so it is
> >non-auto-commiting.
> >
> >thanks,
> >PHilip
> >
> >
> > java.sql.SQLException: ERROR:  SET TRANSACTION ISOLATION LEVEL must be
> >called be fore any query
> >
> >        at org.postgresql.Connection.ExecSQL(Connection.java:533)
> >        at org.postgresql.Connection.ExecSQL(Connection.java:400)
> >        at
> >org.postgresql.jdbc2.Connection.doIsolationLevel(Connection.java:412)
> >        at org.postgresql.jdbc2.Connection.commit(Connection.java:226)
> >    ...
> >
> >And from the server log:
> >
> >NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'quit_pkey'
> >for tab
> >le 'quit'
> >ERROR:  Relation 'quit' already exists
> >ERROR:  Relation 'plottable_cache_seq' already exists
> >NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'quit_pkey'
> >for tab
> >le 'quit'
> >ERROR:  Relation 'quit' already exists
> >DEBUG:  MoveOfflineLogs: remove 0000000C000000E4
> >pq_flush: send() failed: Broken pipe
> >pq_recvbuf: unexpected EOF on client connection
> >DEBUG:  MoveOfflineLogs: remove 0000000C000000E5
> >ERROR:  Relation 'event' does not exist
> >DEBUG:  MoveOfflineLogs: remove 0000000C000000E6
> >NOTICE:  BEGIN: already a transaction in progress
> >ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >NOTICE:  current transaction is aborted, queries ignored until end of
> >transactio
> >n block
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

------------------------------------------------------------------------
Philip Crotwell   (803)777-0955  (803)777-0906 fax  crotwell@seis.sc.edu
------------------------------------------------------------------------