Re: [BUGS] 6.5.2 BUG: Rolling back after dropping a table causes dangling relationships.

Lincoln Yeoh <lylyeoh@mecomb.com> writes:

If you drop a table in a transaction and do a rollback, the table is "half
dropped", the table is gone but you cannot create a new table with the same
name, nor can you drop it again.

Yes, this is a known problem that's rather difficult to fix.
You can't undo a file removal :-(

(Postponing the physical delete of the database's file until commit
wouldn't be good enough, since that would interfere with an attempt to
create another table by the same name later in the same transaction.)

Current sources issue a notice message like "DROP TABLE can't be rolled
back, so don't abort now" if you execute DROP TABLE within a transaction
block. Kind of a half-baked answer, but fixing it really right will
require more effort than anyone seems to want to put into the issue
at the moment. A lot of other DBMSes don't support rolling back this
kind of command either, so we don't feel too horrible about it.

Are there other similar problems for drop sequence/index and stuff?

Yes, drop index, sequence, or view are the same as drop table in that
they remove an associated file. Renaming a table or truncating it (new
for 6.6) also have non-undoable side effects. Not all of these issue
warning messages right at the moment, but I'm working on it. VACUUM
mustn't be executed inside a transaction block at all (6.5.* doesn't
enforce that, but future releases will), and I think the same probably
goes for CREATE DATABASE and DROP DATABASE.

regards, tom lane