Thread

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

   Mike Mascari <mascarm@mascari.com> — 2000-03-06T05:44:39Z

   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
   
   

2. AW: [HACKERS] DROP TABLE inside a transaction block

   Zeugswetter Andreas SB <zeugswettera@wien.spardat.at> — 2000-03-06T08:31:30Z

   > >> 3) Implicitly commit the running transaction and begin a new one.
   > >> Only Vadim and I support this notion, although this is precisely
   > >> what Oracle does (not that that should define PostgreSQL's
   > >> behavior, of course). Everyone else, it seems wants to try to
   > >> implement #1 successfully...(I don't see it happening any time
   > >> soon).
   > >
   > >I support that too since it also happens to be SQL's idea more or less.
   > >One of these days we'll have to offer this as an option. At least for
   > >commands for which #1 doesn't work yet.
   > 
   > Do you really mean it when ou say 'Implicitly commit the running
   > transaction'. I would be deeply opposed to this philosophically, if so. No
   > TX should ever be commited unless the user requests it.
   
   Yes, that was also the general consensus on the list.
   No statement is ever going to do an implicit commit of
   previous statements.
   
   Andreas
   
   

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

   Peter Eisentraut <e99re41@docs.uu.se> — 2000-03-06T10:08:04Z

   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.) 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.
   
   -- 
   Peter Eisentraut                  Sernanders väg 10:115
   peter_e@gmx.net                   75262 Uppsala
   http://yi.org/peter-e/            Sweden