Обсуждение: [HACKERS] DROP TABLE inside transaction block

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

>> 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?
>
> Oracle auto-commits current in-progress transaction before
> execution of any DDL statement and executes such statements in
> separate transaction.

Informix does allow rollback of ddl statements.

Andreas

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Seems like good comments on these items.  Anything
> for TODO list here?
>
> Actually, the current state of play is that I
> reduced the ERROR messages
> to NOTICEs in DROP TABLE and DROP INDEX ("NOTICE:
> DROP TABLE cannot be
> rolled back, so don't abort now"), since there
> seemed to be some
> unhappiness about making them hard errors.  I also
> put similar messages
> into RENAME TABLE and TRUNCATE TABLE.
>
> I have a personal TODO item to go and insert some
> more checks: per the
> discussions so far, CREATE/DROP DATABASE probably
> need similar messages,
> and I think we need to make VACUUM refuse to run
> inside a transaction
> block at all (since its internal commits will not do
> the intended thing
> if you do BEGIN; VACUUM).  Also on my list is to
> investigate these
> reports that CREATE VIEW and ALTER TABLE don't roll
> back cleanly ---
> there may be bugs lurking there.  If you want to add
> those to the
> public list, go ahead.
>
>             regards, tom lane

If my TRUNCATE TABLE patch was applied as submitted,
(I haven't downloaded a newer snapshot yet), then
it falls into category #2...same as VACUUM. It
commits the current transaction before truncating
the specified relation, then begins a new transaction.
Quite frankly, as Vadim pointed out in earlier posts,
PostgreSQL attempts to go "above and beyond" with
respect to rolling back transactions which contain
DDL statements.

>From the ORACLE 7 Server Manual:

Transaction

A transaction (or a logical unit of work) is a
sequence
of SQL statements that ORACLE treats as a single unit.
A transaction begins with the first executable SQL
statement after a COMMIT, ROLLBACK or connection to
ORACLE. A transaction ends with a COMMIT statement, a
ROLLBACK statement, or disconnection (intentional or
unintentional) from ORACLE. ORACLE issues an implicit
                            ^^^^^^^^^^^^^^^^^^^^^^^^^
COMMIT before and after any Data Definition Language
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
statement.
^^^^^^^^^

Anyways, so did the TRUNCATE TABLE patch.

For what its worth,

Mike Mascari
(mascarim@yahoo.com)

__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com

> If my TRUNCATE TABLE patch was applied as submitted,
> (I haven't downloaded a newer snapshot yet), then

Yes, applied.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mike Mascari <mascarim@yahoo.com> writes:
> If my TRUNCATE TABLE patch was applied as submitted,
> (I haven't downloaded a newer snapshot yet), then
> it falls into category #2...same as VACUUM. It 
> commits the current transaction before truncating
> the specified relation, then begins a new transaction.

I took all that out ;-) while updating it to compile against the current
state of heap_open et al.  I see no need for multiple transactions in
TRUNCATE.  It's really on a par with RENAME TABLE, since both have to
force a buffer flush.
        regards, tom lane