Re: [HACKERS] SQL statements: begin and end

At 09:59 AM 11/23/99 -0500, Tom Lane wrote:

>I went looking in the SQL spec to confirm this, and was rather
>startled to discover that BEGIN is not SQL at all! The SQL spec
>seems to envision the always-in-a-transaction-block model of operation.
>They have
> <commit statement> ::=
> COMMIT [ WORK ]
>which is defined to commit the current transaction; but a new xact is
>implicitly started by the next SQL operation (cf. sec. 4.28 in SQL92).

This is how Oracle's SQL*Plus works.

>If we wanted to be completely standards-conformant, I think we'd have to
>abandon the begin/end model entirely. I wouldn't support that ---
>auto commit of standalone statements is too convenient.

Oracle supports two modes, AFAIK (my Oracle experience is limited, but
not entirely non-existent). You can set it to autocommit mode. The
Tcl API I'm familiar with (for the web server AOLserver) works in
autocommit mode. You feed it a (guess what?) "BEGIN" dml statement
to switche off autocommit. Then you feed it a "COMMIT", it
commits the transaction, and tells Oracle to go back to autocommit mode.

Just like PostgreSQL...

Regarding the fact that SQL*Plus defaults to NOT auto-commit isn't
necessarily a bad thing, I might add - if you boo-boo when typing
in deletes and updates, forgetting an "and" clause perhaps, you
can type "abort". In psql, I always do a "begin" before doing any
deletes or updates to the database which backs my website, watching
to make sure that the number of rows changed or delted jives with
my expectation before committing.

I don't mind the way Postgres does stuff, though for someone used
to Oracle the fact that psql is autocommitting might come as an
unpleasant surprise.

>Bottom line: pointing at the spec is a very weak argument for telling
>people how to spell their begin/end statements.

Folks who do this should probably at least read the standard first.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.