Re: [HACKERS] DROP TABLE inside transaction block

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


>> José Soares <jose@sferacarta.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...)