Thread: SET TRANSACTION ISOLATION LEVEL
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
Philip,
It would be useful to see the rest of your code. It is hard to determine
what is going on.
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Philip Crotwell
Sent: Friday, February 22, 2002 2:54 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] SET TRANSACTION ISOLATION LEVEL
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
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 >
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
------------------------------------------------------------------------
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
> ------------------------------------------------------------------------
>
>
>
>