Re: [HACKERS] DROP TABLE inside a transaction block

Philip Warner wrote:
>
> At 07:59 6/03/00 +0100, Peter Eisentraut wrote:
> >> (such as TRUNCATE) make no sense with respect to ROLLBACK. So, I
> >> guess, the idea is that SOME DDL statements will be ROLLBACK-able
> >> and some won't - yuck.
> >
> >I don't see a problem with disallowing some DDL commands in a transaction
> >as long as they throw an error and the transaction aborts.
>
> Is it really necessary to abort the TX? Seems a little antisocial - can't
> you just return an error, and let the user/application decide if it needs
> to abort?
>
> >> 3) Implicitly commit the running transaction and begin a new one.
> >> Only Vadim and I support this notion, although this is precisely
> >> what Oracle does (not that that should define PostgreSQL's
> >> behavior, of course). Everyone else, it seems wants to try to
> >> implement #1 successfully...(I don't see it happening any time
> >> soon).
> >
> >I support that too since it also happens to be SQL's idea more or less.
> >One of these days we'll have to offer this as an option. At least for
> >commands for which #1 doesn't work yet.
>
> Do you really mean it when ou say 'Implicitly commit the running
> transaction'. I would be deeply opposed to this philosophically, if so. No
> TX should ever be commited unless the user requests it.
>
> Just my 0.02c

Philosophically, I agree with you 100%. And apparently, from the
previous discussion on this issue, databases like Informix are
completely capable of rolling back DDL statements like DROP
TABLE, ALTER TABLE RENAME, etc. However, the complexity involved
apparently was too much for Oracle:

"ORACLE implicitly commits the current transaction before and
after every Data Definition Language statement."

Its just my feeling that robustness is the number one priority
and that the current state is kind of "riding the fence" between
ORACLE and Informix. On either side of the fence, it is safe, but
in the middle, you risk corruption.

Naturally, I'd like to see PostgreSQL on the Informix side of the
fence, but I don't see it happening any time soon. And the ORACLE
side of the fence is far easier to implement. Or, of course, you
could choose Peter's suggestion, and disallow the DDL statement
entirely. But as soon as that happened, all those people that
begin their .cgi programs with BEGIN and end it with END will
say, "Hey, if we can't use DDL statements in transactions, can't
we at least do what Oracle does so we don't have to change our
code?"

Mike Mascari