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

Tue, 23 Nov 1999 11:31:04 +0100
Zeugswetter Andreas SEV <ZeugswetterA@wien.spardat.at>
SQL statements: begin and end
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


Tue, 23 Nov 1999 12:52:16 +0100 (MET)
Peter Eisentraut <e99re41@DoCS.UU.SE>
Re: [HACKERS] SQL statements: begin and end
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

Tue, 23 Nov 1999 09:59:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SQL statements: begin and end 
> 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

Tue, 23 Nov 1999 15:16:58 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
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@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Tue, 23 Nov 1999 07:29:12 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] SQL statements: begin and end 
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

Tue, 23 Nov 1999 10:36:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] SQL statements: begin and end 
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 seen http://www.pop4.net?
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Tue, 23 Nov 1999 11:15:21 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] SQL statements: begin and end 
[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, Pennsylvania 19026

Tue, 23 Nov 1999 13:04:14 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SQL statements: begin and end