Re: [HACKERS] DROP TABLE inside a transaction block

Peter Eisentraut wrote:
> 
> Tatsuo Ishii writes:
> 
> > I see following in HISTORY:
> >
> >       Disallow DROP TABLE/DROP INDEX inside a transaction block
> >
> > However, it seems that this is not done with current?
> >
> > test=# create table t1(i int);
> > CREATE
> > test=# begin;
> > BEGIN
> > test=# drop table t1;
> > NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
> 
> Wow, with all due respect, that's pretty sh^H^Hpoor. That's like saying
> "Haha, either you commit your transaction or your database is fried." Any
> reason that's not an ERROR before anything destructive is done?
> 
> > DROP
> > test=# end;
> > COMMIT
> > test=# \d
> > No relations found.

We had an elaborate discussion on this very topic several months
ago. What it comes down to is three possible options:

1) Allow DDL statements in transactions. If the transaction
aborts, currently, corruption can result. Some DDL statements
(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.

2) Disallow DDL statement in transactions. This would break code
for people which is working now, only because their transactions
are being committed between the time they issue the DDL statement
and the COMMIT (or END), instead of aborting and causing their
database to become corrupt, or require manual removal of files
when the catalogue gets out-of-sync with the filesystem.

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

So, as some sort of compromise, a NOTICE was issued.

Mike Mascari