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(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