Re: AW: [HACKERS] DROP TABLE inside a transaction block

Peter Eisentraut wrote:
> 
> On Mon, 6 Mar 2000, Zeugswetter Andreas SB wrote:
> 
> > Yes, that was also the general consensus on the list. No statement is
> > ever going to do an implicit commit of previous statements.
> 
> I can understand that, but one of these days I hope we can offer the SQL
> semantics of transactions where you don't require a BEGIN. (*Optional*,
> people.) 

I often think that the current behavior with respect to BEGIN
often hurts PostgreSQL's reputation with respect to speed. If the
default behavior was to begin a transaction at the first
non-SELECT DML statement, PostgreSQL wouldn't fare so poorly in
tests of:

INSERT INTO testspeed(1);
INSERT INTO testspeed(2);
INSERT INTO testspeed(3);
...
INSERT INTO testspeed(100000);

where, the same .sql script submitted against other databases is
running in a transaction, and, as such, is not being committed
immediately to disk. Fortunately, the Ziff-Davis reviewer ran his
tests with fsync() off. But your run-of-the-mill enterprise
application developer is probably going to just install the
software via rpms and run their sql scripts against it.

> In that case you have to do *something* about non-rollbackable
> DDL (face it, there's always going to be one). Doing what Oracle does is
> certainly not the *worst* one could do. Again, optional.
> 
> That still doesn't excuse the current behavior though.

I can certainly understand Andreas' viewpoint. If no DDL,
however, was allowed inside a transaction -or- you could
optionally turn on implicit commit, imagine how much easier life
becomes in implementing ALTER TABLE DROP COLUMN, DROP TABLE, DROP
INDEX, etc, not having to worry about restoring filesystem files,
or deleting them in aborted CREATE TABLE/CREATE INDEX statements,
etc. A far-reaching idea would be to make use of foreign keys in
the system catalogue, with triggers used to add/rename/remove
relation files. That could be done if DDL statements could not be
executed in transactions. With AccessExclusive locks on the
appropriate relations, a host of race-condition related bugs
would disappear. And the complexity involved with dropping (or
perhaps disallowing the dropping of) related objects, such as
triggers, indexes, etc. would be automatic.

Mike Mascari