Thread: SET TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL

From
Philip Crotwell
Date:
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




Re: SET TRANSACTION ISOLATION LEVEL

From
"Dave Cramer"
Date:
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



Re: SET TRANSACTION ISOLATION LEVEL

From
Barry Lind
Date:
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
>



Re: SET TRANSACTION ISOLATION LEVEL

From
Philip Crotwell
Date:
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
------------------------------------------------------------------------



Re: SET TRANSACTION ISOLATION LEVEL

From
Barry Lind
Date:
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
> ------------------------------------------------------------------------
>
>
>
>