Re: [HACKERS] Re: [GENERAL] drop/rename table and transactions

Tom Lane wrote:

> Mike Mascari wrote:
> >> This is one of the few areas that I disagree with the development
> >> trend in PostgreSQL. Every release contains different bugs related to
> >> DDL statements in transactions. The developers appear to want to make
> >> them work (i.e., have the ability to rollback a DROP TABLE, ALTER
> >> TABLE ADD COLUMN, etc.). This, in my opinion, goes far above and
> >> beyond the call of duty for a RDBMS. Oracle issues an implicit COMMIT
> >> whenever a DDL statement is found.
>
> So, the limits of our ambition should be to be as good as Oracle?
> (Only one-half :-) here.)
>
> I've seen quite a few discussions on the mailing lists about
> applications that could really use rollback-able DDL commands.
>
> Personally, I certainly wouldn't give up any reliability for this,
> and darn little performance; but within those constraints I think
> we should do what we can.
>
>                         regards, tom lane
>

Well, I agree that it would be GREAT to be able to rollback DDL
statements.  However, at the moment, failures during a transaction while
DDL statements occur usually require direct intervention by the user (in
the case of having to drop/recreate indexes) and often require the services
of the DBA, if filesystem intervention is necessary (i.e., getting rid of
partially dropped/created tables and their associated fileystem files). I
guess I'm worried by the current state of ambiguity with respect to which
DDL statements can be safely rolled back and which can't.  I know you added
NOTICEs in current, but it seems less than robust to ask the user not to
trigger a bug. And of course, something like the following can always
happen:

test=# CREATE TABLE example(value text);
CREATE
test=# BEGIN;
BEGIN
test=# DROP TABLE example;
NOTICE:  Caution: DROP TABLE cannot be rolled back, so don't abort now
DROP

-- someone just yanked the RJ45 cable from the hub in the T-COM closet --

(which, ludicrous as it might seem, happens)

From an otherwise EXTREMELY happy user :-) (full smile...),  I see 3
scenarios:

(1) Disallow DDL statements in transactions
(2) Send NOTICE's asking for the user to not trigger the bug until the bugs
can be fixed -or-
(3) Have all DDL statements implicity commit any running transactions.

1, of course, stinks. 2 is the current state and would probably take
several releases before all DDL statement rollback bugs could be crushed
(look how many times it took to get segmented files right -- and are we
REALLY sure it is?). 3, it seems to me, could be implemented in a day's
time, would prevent the various forms of data corruption people often post
to this list (GENERAL) about, and still allows 2 to happen in the future as
a configure-time or run-time option.

Just some ramblings,

Mike Mascari