Re: SET TRANSACTION ISOLATION LEVEL

Philip,

rollback would be preferred.  If an error occured during the
transaction, rollback is the only way to end the transaction.

--Barry

Philip Crotwell wrote:
> 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
> ------------------------------------------------------------------------
>
>
>
>