Thread: SQL statements: begin and end
I see everybody using the following PostgreSQL statements: "begin" instead of "begin work" "end" instead of "commit work" This is really bad, because it is not standard, and can easily be taken for a statement block, which it is definitely not ! It is a transaction block. I vote for issuing a NOTICE for these in V7 and remove them in V8, at least the single "end" Bruce, please don't use "begin" and "end" in your book. Andreas
Whatever happened to BEGIN TRANSACTION;... COMMIT; I never liked END to begin with, since it doesn't really imply that you are committing anything. Or what is the non-committing counterpart of END? But I think we should go strictly with the SQL standard, even if that contradicts what I just said. (?) -Peter On Tue, 23 Nov 1999, Zeugswetter Andreas SEV wrote: > I see everybody using the following PostgreSQL statements: > > "begin" instead of "begin work" > "end" instead of "commit work" > > This is really bad, because it is not standard, and can easily be taken for > a statement block, which it is definitely not ! It is a transaction block. > > I vote for issuing a NOTICE for these in V7 and remove them in V8, > at least the single "end" > > Bruce, please don't use "begin" and "end" in your book. > > Andreas > > ************ > > -- Peter Eisentraut Sernanders vaeg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden
Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at> writes:
> I see everybody using the following PostgreSQL statements:
> "begin" instead of "begin work"
> "end" instead of "commit work"
> This is really bad, because it is not standard,
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).
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.
Bottom line: pointing at the spec is a very weak argument for telling
people how to spell their begin/end statements.
> I vote for issuing a NOTICE for these in V7 and remove them in V8,
> at least the single "end"
My feeling is that application authors have already decided whether
they prefer "BEGIN" or "BEGIN TRANSACTION" or "BEGIN WORK", and trying
to enforce a single standard now is just going to irritate people and
break existing applications. I vote for leaving well enough alone.
> Bruce, please don't use "begin" and "end" in your book.
Sure, it makes sense for the book to consistently use "BEGIN WORK"
and "COMMIT WORK", which are probably the least likely to confuse
novices. But I think actually removing the other variants would be
just an exercise in causing trouble.
regards, tom lane
> I went looking in the SQL spec to confirm this, and was rather
> startled to discover that BEGIN is not SQL at all!
Right- me too! I glanced through the SQL98 docs I have (mostly from
'94) and there is no addition afaict. BEGIN/END *are* defined in SQL,
but only in the context of embedded SQL.
> > Bruce, please don't use "begin" and "end" in your book.
> Sure, it makes sense for the book to consistently use "BEGIN WORK"
> and "COMMIT WORK", which are probably the least likely to confuse
> novices. But I think actually removing the other variants would be
> just an exercise in causing trouble.
WORK is an optional noise word in SQL92. BEGIN WORK is not defined at
all, but I agree with Tom that the extension is essential.
I'm pretty sure that Andrea's biggest objection was to the acceptance
and use of END, which has no connection in official SQL to transaction
completion but only to block delimiting for cursor loops. It is almost
certainly a holdover from PostQuel.
Any thoughts on whether AZ's suggestion for dropping END in this
context should be done for 7.0? We certainly could make an effort to
at least purge it from our examples in the docs...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
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@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> I'm pretty sure that Andrea's biggest objection was to the acceptance
> and use of END, which has no connection in official SQL to transaction
> completion but only to block delimiting for cursor loops. It is almost
> certainly a holdover from PostQuel.
> Any thoughts on whether AZ's suggestion for dropping END in this
> context should be done for 7.0? We certainly could make an effort to
> at least purge it from our examples in the docs...
Even AZ wasn't suggesting dropping it for 7.0!
We ought to check what other RDMSs are doing in this area before making
any decisions. The fact that we've got so many ways to spell "BEGIN"
suggests to me that some of them were tacked on for compatibility with
other products...
regards, tom lane
On Tue, 23 Nov 1999, Tom Lane wrote: > My feeling is that application authors have already decided whether > they prefer "BEGIN" or "BEGIN TRANSACTION" or "BEGIN WORK", and trying > to enforce a single standard now is just going to irritate people and > break existing applications. I vote for leaving well enough alone. > > > Bruce, please don't use "begin" and "end" in your book. > > Sure, it makes sense for the book to consistently use "BEGIN WORK" > and "COMMIT WORK", which are probably the least likely to confuse > novices. But I think actually removing the other variants would be > just an exercise in causing trouble. I don't know how Oracle or most everyone else is doing it, but Sybase uses: begin transaction [transaction name] and commit transaction [transaction name] I don't see an end transaction in the quick ref, but they do have a: begin statement block end in there. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h> Have you seenhttp://www.pop4.net? Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
[Charset iso-8859-1 unsupported, filtering to ASCII...] > I see everybody using the following PostgreSQL statements: > > "begin" instead of "begin work" > "end" instead of "commit work" > > This is really bad, because it is not standard, and can easily be taken for > a statement block, which it is definitely not ! It is a transaction block. > > I vote for issuing a NOTICE for these in V7 and remove them in V8, > at least the single "end" Not sure on this one. Why not let them use it? > > Bruce, please don't use "begin" and "end" in your book. OK. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026