Pursuant to a phone conversation I had with Bruce, I added code this
morning to reject DROP TABLE or DROP INDEX inside a transaction block;
that is, you can't do BEGIN; DROP TABLE foo; END anymore. The reason
for rejecting this case is that we do the wrong thing if the transaction
is later aborted. Following BEGIN; DROP TABLE foo; ABORT, the system
tables will claim that foo is still valid (since the changes to them
were never committed) but we've already unlinked foo's physical file,
and we can't get it back. Solution: only allow DROP TABLE outside
BEGIN, so that the user can't try to change his mind later.
However, on second thought I wonder if this cure is worse than the
disease. Will it be unreasonably hard to drop tables using client
interfaces that like to wrap everything in BEGIN/END? Plugging an
obscure hole might not be worth that.
A possible compromise is not to error out, but just to issue a NOTICE
along the lines of "DROP TABLE is not undoable, so don't even think of
trying to abort now..."
(Of course, what would be really nice is if it just worked, but I don't
see any way to make that happen without major changes. Simply
postponing the unlink to end of transaction isn't workable; consider
BEGIN; DROP TABLE foo; CREATE TABLE foo; ...)
Any thoughts? Will there indeed be a problem with JDBC or ODBC if we
leave this error check in place?
regards, tom lane
