Skip site navigation (1) Skip section navigation (2)

Re: SET autocommit begins transaction?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Sean Chittenden <sean(at)chittenden(dot)org>
Cc: Rod Taylor <rbt(at)rbt(dot)ca>,Thomas Lockhart <lockhart(at)fourpalms(dot)org>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: SET autocommit begins transaction?
Date: 2002-09-19 00:44:36
Message-ID: 200209190044.g8J0iac18128@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Sean Chittenden wrote:
> > > Of course, the reason they're rollbackable is:
> > > 
> > > begin;
> > > create schema newschema;
> > > set search_path = newschema;
> > > rollback;
> > > 
> > > create table junk;  -- DOH!
> > 
> > And:
> > 	
> > 	set statement_timeout = 20;
> > 	query_with_error;
> > 	set statement_timeout = 0;
> > 	COMMIT;
> > 
> > That will have to change in autocommit off to:
> > 
> > 	BEGIN;
> > 	SET statement_timeout = 20;
> > 	query_with_error;
> > 	SET statement_timeout = 0;
> > 	COMMIT;
> > 
> > I assume that BEGIN does start a transaction.  With no BEGIN above, the
> > big problem is that it will work most of the time, but when/if the query
> > fails, they will find out they forgot the BEGIN.
> 
> Wouldn't it roll back to 0 though because the SET statement_timeout TO
> 20 was inside of a transaction (assuming the value was 0 before the
> transaction began)?  -sc

Yes, with the BEGIN, it will roll back.  With autocommit off, this:

> > 	
> > 	set statement_timeout = 20;
> > 	query_with_error;
> > 	set statement_timeout = 0;
> > 	COMMIT;

will not roll back to 0.  It will be 20.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-bugs by date

Next:From: Sean ChittendenDate: 2002-09-19 00:49:22
Subject: Re: SET autocommit begins transaction?
Previous:From: Rod TaylorDate: 2002-09-19 00:41:52
Subject: Re: Bug #772: Rewriting on multi-record updates is

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group