Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] DROP TABLE inside transaction block

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)us>
Cc: "[Jos_] Soares" <jose(at)sferacarta(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] DROP TABLE inside transaction block
Date: 1999-09-28 13:40:17
Message-ID: 316.938526017@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian <maillist(at)candle(dot)pha(dot)pa(dot)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


>> =?iso-8859-1?Q?Jos=E9?= Soares <jose(at)sferacarta(dot)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...)

pgsql-hackers by date

Next:From: Thomas LockhartDate: 1999-09-28 13:43:00
Subject: Re: [HACKERS] RI status report #1
Previous:From: Tom LaneDate: 1999-09-28 13:28:48
Subject: Re: [HACKERS] md.c is feeling much better now, thank you

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group