Re: autocommit vs TRUNCATE et al

Joe Conway wrote:
> Bruce Momjian wrote:
> >>most useful approach.  The analogy to SET hadn't occurred to me.
> > 
> > 
> > Yea, the SET behavior appeared pretty queer to me, but now that I have
> > used it, I am getting used to it.
> > 
> 
> So does that mean:
> 
>    set autocommit to off;
>    begin;
>    insert into foo values('a');
>    insert into bar values('b');
>    truncate table foobar;
> 
> will automatically commit the two inserts?

No, the entire transaction will aborted because TRUNCATE has to be at
the start of a multi-statement transaction.  This will also fail:

>    set autocommit to off;
>    begin;
>    truncate table foobar;
>    insert into foo values('a');
>    insert into bar values('b');

but this will work:

>    set autocommit to off;
>    truncate table foobar;
>    insert into foo values('a');
>    insert into bar values('b');

In the last case, the TRUNCATE will happen, and the INSERTs will be in
their own multi-statement transaction.  A SET in place of TRUNCATE will
behave the same way.

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