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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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...)

Browse pgsql-hackers by date

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