Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is select a transaction starting statement?

Is select a transaction starting statement?

From
Dennis Bjorklund
Date:
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

Re: Is select a transaction starting statement?

From
Alvaro Herrera
Date:
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)

Re: Is select a transaction starting statement?

From
Dennis Bjorklund
Date:
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

Re: Is select a transaction starting statement?

From
Peter Eisentraut
Date:
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/

Re: Is select a transaction starting statement?

From
Dennis Bjorklund
Date:
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