Re: [HACKERS] DROP TABLE inside transaction block

Leon <leon@udmnet.ru> writes:
> Tom Lane wrote:
>>>> Cant you just rename to a unique name, maybee in another directory,
>> 
>> Not if other backends are also accessing the table.  Remember that to
>> make this really work, the DROP would have to be invisible to other
>> backends until commit.

> Is that really needed? Remember that table's creation is not transparent
> to other users - when someone attempts to create a table, others,
> though can't see that table, cannot create a table with the same name.
> So you can simply issue a draconian-level lock on a table being deleted.

That's a good point --- we acquire exclusive lock anyway on a table
about to be deleted, so just holding that lock till end of transaction
should prevent other backends from trying to touch the table.

So someone could probably cobble together a real solution consisting of
locking the table and renaming the files to unique temp names at DROP
time, then either completing the drop and unlinking the files at commit
time, or re-renaming them at abort.

There are a bunch of subtleties to be dealt with though.  A couple of
gotchas I can think of offhand: better flush dirty buffers for the
target rel before doing the rename, else another backend might try to
do it between DROP and COMMIT, and write to the wrong file name.  The
renaming at abort time has to be done in the right order relative to
dropping tables created during the xact, or else BEGIN; DROP TABLE foo;
CREATE TABLE foo; ABORT won't work right.  Currently, an attempt to
lock a table always involves making a relcache entry first, and the
relcache will try to open the underlying files as soon as you do that,
so other backends trying to touch the dying table for the first time
would get unexpected error messages.  Probably a few other things.

In short, a lot of work for a very marginal feature.  How many other
DBMSes permit DROP TABLE to be rolled back?  How many users care?

> I personally have a project in development which extensively uses
> that feature. It is meant to be database restructuring 'on the fly'.

What do you mean by "that feature"?  The ability to abort a DROP TABLE?
We have no such feature, and never have.  If you just mean that you
want to issue DROP TABLE inside BEGIN/END, and you don't care about
problems that ensue if the transaction is aborted, then we could
consider downgrading the error report to a notice as I suggested
yesterday.
        regards, tom lane