Re: autocommit vs TRUNCATE et al

Tom Lane wrote:
> There are a number of statements, such as TRUNCATE TABLE, that refuse to
> run in a transaction block because they perform actions that can't be
> rolled back later.
>
> These statements currently do not look at autocommit, which means that
> if autocommit is off, their tests will succeed ... but then a
> transaction block is started anyway, defeating the point of the test.
>
> We could fix these statements to fail if autocommit is off, which means
> that you could not use them at all except by setting autocommit on.
> Ugh.
>
> Or we could fix them to force an autocommit. Which would mean that
> these "dangerous" statements would become even more dangerous, since
> that's exactly the behavior a person using autocommit-off would not
> expect. Also ugh.
>
> Anyone see a way out of this catch-22? If not, which is the least
> bad alternative?

Ugh, I see what you mean. With the old code, you had to do a BEGIN to
start a multi-statement transaction, while with autocommit off, you are
always in one. I don't think forcing them to turn on autocommit makes
any sense; it isn't obvious, and it doesn't buy anything. I think we
should just do an automatic COMMIT if it is the first statement of a
transaction, and if not, throw the same error we used to throw. We are
performing autocommit for SET at the start of a transaction now anyway,
so it isn't totally strange to do it for TRUNCATE, etc. too. In fact,
you can just put the xact commit check in the same place SET is handled
in postgres.c. It isn't great, but it is clean. ;-)

You could also throw a NOTICE mentioning it was committed as a separate
transaction.

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