[HACKERS] Autocommit off and transaction isolation level

2003-01-02 Thread Michael Paesold 
Hi all,

I have come across some weird behavior in postgres concerning autocommit=off
and setting the transaction isolation level. I have no explanation why
things should work as they do, so I consider this a bug, no?

With autocommit=on and normal begin; ... commit; block setting the
transaction isolation level works fine:

billing=# begin;
BEGIN
billing=# set transaction isolation level serializable;
SET
billing=# show transaction isolation level;
 TRANSACTION ISOLATION LEVEL
-
 SERIALIZABLE
(1 row)

billing=# commit;
COMMIT


Now setting autocommit=off the set transaction isolation level command does
not show any effect:

billing=# set autocommit to off;
SET
billing=# set transaction isolation level serializable;
SET
billing=# select current_date;
date

 2003-01-02
(1 row)

billing=# show transaction isolation level;
 TRANSACTION ISOLATION LEVEL
-
 READ COMMITTED this should be SERIALIZABLE, no??
(1 row)

billing=# commit;
COMMIT

Is it a bug?
Regards,
Michael Paesold


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Autocommit off and transaction isolation level

2003-01-02 Thread Tom Lane 
Michael Paesold [EMAIL PROTECTED] writes:
 Now setting autocommit=off the set transaction isolation level command does
 not show any effect:

 billing=# set autocommit to off;
 SET
 billing=# set transaction isolation level serializable;
 SET

SET does not start a transaction block, so this will not work.  You must
use an explicit BEGIN before setting TRANSACTION ISOLATION LEVEL.

You might instead set default_transaction_isolation to get the behavior
I think you are looking for.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Autocommit off and transaction isolation level

2003-01-02 Thread Stephan Szabo 

On Thu, 2 Jan 2003, Tom Lane wrote:

 Michael Paesold [EMAIL PROTECTED] writes:
  Now setting autocommit=off the set transaction isolation level command does
  not show any effect:

  billing=# set autocommit to off;
  SET
  billing=# set transaction isolation level serializable;
  SET

 SET does not start a transaction block, so this will not work.  You must
 use an explicit BEGIN before setting TRANSACTION ISOLATION LEVEL.

 You might instead set default_transaction_isolation to get the behavior
 I think you are looking for.

The overall behavior appears to be against spec, but I figure this was
discussed at the time the set transation was added.



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org