Re: autocommit vs TRUNCATE et al

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >>> Maybe we can throw a WARNING when autocommit is on. Would that make
> >>> everyone happy?
> >>
> >> I doubt it, because by the time you read the WARNING it's too late:
> >> the statement's already committed.
>
> > I assume the same limitation would hold for VACUUM, right, that you have
> > to turn on autocommit mode to use it?
>
> Yeah, it would, unless we wanted to throw in some additional hack to
> distinguish VACUUM from the "more dangerous" cases.

From my perspective, I think it would be consistent to disallow all
transaction-unsafe commands and tell people they have to turn autocommit
on to execute them, so it would be:

SET autocommit TO 'on';
VACUUM;
SET autocommit TO 'off';

That is a pain, but it is probably the safest, as you explained.

One particularly nasty problem is issuing a VACUUM or TRUNCATE in cases
where you don't know the autocommit mode. You could set autocommit to
'on', and issue the command, but how do you know if you need to turn
autocommit back off again? I suppose you have to conditionally test the
autocommit value and reset it after the command if needed.

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