Обсуждение: AW: [HACKERS] DROP TABLE inside a transaction block

> >> 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

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

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