Re: [HACKERS] DROP TABLE inside transaction block

=?iso-8859-1?Q?Jos=E9?= Soares <jose(at)sferacarta(dot)com> writes:
> Seems a good solution. I have an old note about this problem.
> What about to reject also the following commands inside transactions?

> * BUGS: There are some commands that doesn't work properly
> inside transactions. Users should NOT use the following
> statements inside transactions:

> - DROP TABLE -- in case of ROLLBACK only table structure
> will be recovered, data will be
> lost.
> - CREATE VIEWS -- the behavior of the backend is unpredictable.
> - ALTER TABLE -- the behavior of the backend is unpredictable.
> - CREATE DATABASE -- in case of ROLLBACK will be removed references
> from "pg_database" but directory
> $PGDATA/databasename will not be removed.

CREATE DATABASE (and presumably also DROP DATABASE) probably should
refuse to run inside a transaction.

I see no good reason that CREATE VIEW or ALTER TABLE should not work
cleanly in a transaction. It may be that they have bugs interfering
with that (for example, Hiroshi just pointed out that ALTER TABLE
seems not to be locking the table, which is surely bogus).

The main reason that DROP TABLE is an issue is that it alters the
underlying Unix file structure, which means we can't just rely on the
normal transaction mechanisms of committed/uncommitted tuples to handle
rollback. ALTER TABLE doesn't do anything except change tuples.
CREATE VIEW is a CREATE TABLE plus tuple changes (and while CREATE TABLE
does alter the file structure by making a new file, we have extra code
in there to handle rolling it back). So it seems like they oughta work.

RENAME TABLE is another thing that can't currently be rolled back,
because it renames the underlying Unix files and there's no mechanism
to undo that. (RENAME TABLE is missing a lock too...)

regards, tom lane