Is select a transaction starting statement according to the the sql
specification?

In the specification (sql99) there is a list of things that are and a list
of things that are not, but I can't figure out which list a select query
belongs to. Isn't that great :-)

In postgresql it is implemented as one. A guy on irc informed me that in
oracle only SELECT ... FOR UPDATE is a transaction starting command.

Now I'm trying to figure out which is the correct way.

I'm afraid that no one knows this and I will have to spend a full day
reading the spec, just to stop my curiosity. Please help me save a day!

-- 
/Dennis Bjï¿½rklund


Tue, 14 Sep 2004 19:35:29 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Is select a transaction starting statement?
On Tue, Sep 14, 2004 at 07:35:29PM +0200, Dennis Bjorklund wrote:
> Is select a transaction starting statement according to the the sql
> specification?

Yes, at least in my copy of sql2003.

> In the specification (sql99) there is a list of things that are and a list
> of things that are not, but I can't figure out which list a select query
> belongs to. Isn't that great :-)

In SQL2003-5WD I see:

4.33.4 SQL-statements and transaction states

The following SQL-statements are transaction-initiating SQL-statements,
i.e., if there is no current SQLtransaction, and a statement of this
class is executed, an SQL-transaction is initiated:

[...]

-- The following SQL-data statements:
   [...]
   <direct select statement: multiple rows>.
   [...]


The <direct select statement: multiple rows> is in time defined as
<cursor specification>, which in turn is a <query expression>, which is
our SELECT statement.  A lot of jumps in the grammar, but it's there.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)


Tue, 14 Sep 2004 13:57:34 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Is select a transaction starting statement?
On Tue, 14 Sep 2004, Alvaro Herrera wrote:

> -- The following SQL-data statements:
>    [...]
>    <direct select statement: multiple rows>.
>    [...]
> 
> 
> The <direct select statement: multiple rows> is in time defined as
> <cursor specification>, which in turn is a <query expression>, which is
> our SELECT statement.  A lot of jumps in the grammar, but it's there.

Nice.

In sql99 there is only <direct select statement: single row> which is 
SELECT .. INTO .. But maybe some of the other includes queries after 4-5 
jumps or so. Or maybe they simply forgot that one. Just my luck to be 
reading sql99.

Thanks.

-- 
/Dennis Bjï¿½rklund


Tue, 14 Sep 2004 20:54:10 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Is select a transaction starting statement?
Dennis Bjorklund wrote:
> In sql99 there is only <direct select statement: single row> which is
> SELECT .. INTO .. But maybe some of the other includes queries after
> 4-5 jumps or so. Or maybe they simply forgot that one. Just my luck
> to be reading sql99.

It's the same in SQL 99.  I think you missed that most of the "direct 
SQL" is specified in part 5 instead of part 2.  In SQL 2003 they have 
merged these parts.  The relevant section for you in SQL 99 is 4.6.3 in 
part 5.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Tue, 14 Sep 2004 22:49:35 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: Is select a transaction starting statement?
On Tue, 14 Sep 2004, Peter Eisentraut wrote:

> It's the same in SQL 99.  I think you missed that most of the "direct 
> SQL" is specified in part 5 instead of part 2.  In SQL 2003 they have 
> merged these parts.  The relevant section for you in SQL 99 is 4.6.3 in 
> part 5.

Good, that explains it all. It never occured to me that this could be in
part 5 "Host Languge Bindings".

-- 
/Dennis Bjï¿½rklund


Wed, 15 Sep 2004 07:14:47 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: Is select a transaction starting statement?