Thread: next value expression

By accident I stumbled across the following paragraph in the August
2002 draft of SQL 2003:
If there are multiple instances of <next value expression>sspecifying the same sequence generator within a
singleSQL-statement,all those instances return the same value for agiven row processed by that SQL-statement.
 

Is this of any relevance to PG's nextval()?

ServusManfred

On Wed, 2002-11-27 at 10:29, Manfred Koizar wrote:
> By accident I stumbled across the following paragraph in the August
> 2002 draft of SQL 2003:
> 
>     If there are multiple instances of <next value expression>s
>     specifying the same sequence generator within a single
>     SQL-statement, all those instances return the same value for a
>     given row processed by that SQL-statement.
> 
> Is this of any relevance to PG's nextval()?

Somewhat -- SQL2003 defines sequence generators that are pretty much
identical in functionality to PostgreSQL's sequences, although the
syntax is a bit different. I submitted a patch for 7.4 that adjusts the
CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more
closely, but there's a bunch more work that can be done, if we want to
be fully SQL-compliant.

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Neil Conway <neilc@samurai.com> writes:
> On Wed, 2002-11-27 at 10:29, Manfred Koizar wrote:
>> By accident I stumbled across the following paragraph in the August
>> 2002 draft of SQL 2003:
>> 
>> If there are multiple instances of <next value expression>s
>> specifying the same sequence generator within a single
>> SQL-statement, all those instances return the same value for a
>> given row processed by that SQL-statement.
>> 
>> Is this of any relevance to PG's nextval()?

> Somewhat -- SQL2003 defines sequence generators that are pretty much
> identical in functionality to PostgreSQL's sequences, although the
> syntax is a bit different.

I would think his point is that the above paragraph specifies behavior
that is very definitely NOT like Postgres'.

> I submitted a patch for 7.4 that adjusts the
> CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more
> closely,

Did we apply it?  I'm inclined not to, until we nail down the semantic
implications a little more.  Conforming to the spec on syntax when we
don't on semantics strikes me as a bad idea.
        regards, tom lane

On Wed, 2002-11-27 at 12:24, Tom Lane wrote:
> I would think his point is that the above paragraph specifies behavior
> that is very definitely NOT like Postgres'.

Ah, I see now -- yeah, I misunderstood.

> > I submitted a patch for 7.4 that adjusts the
> > CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more
> > closely,
> 
> Did we apply it?  I'm inclined not to, until we nail down the semantic
> implications a little more.  Conforming to the spec on syntax when we
> don't on semantics strikes me as a bad idea.

I agree, although the patch has already been applied.

There's already a need to reform the way in which the next value of a
sequence is produced (nextval() makes it difficult to get the dependancy
information right); would it be a good idea to change it to be
completely SQL compatible at the same time?

Cheers,

Neil

Neil Conway <neilc@samurai.com> writes:
> There's already a need to reform the way in which the next value of a
> sequence is produced (nextval() makes it difficult to get the dependancy
> information right); would it be a good idea to change it to be
> completely SQL compatible at the same time?

What do you consider "completely SQL compatible" here?  In particular,
what is a "statement"?  My initial reaction to this part of the SQL
draft is that it's broken.  Consider plpgsql functions invoked within
an interactive statement --- if they invoke nextval() should it fail to
increment across repeated attempts?  Does your answer change if the
functions are invoked as triggers, rather than directly in the text of
the statement?  How about queries inserted by rule rewriting; are those
separate statements for this purpose?  In any of these contexts I think
you can construct examples that would favor either answer.

ISTM that we will have all the same issues with this that we had with
the question of when "now()" should increment...
        regards, tom lane

On 27 Nov 2002 11:51:13 -0500, Neil Conway <neilc@samurai.com> wrote:
>Somewhat -- SQL2003 defines sequence generators that are pretty much
>identical in functionality to PostgreSQL's sequences, although the
>syntax is a bit different. I submitted a patch for 7.4 that adjusts the
>CREATE SEQUENCE grammar to match SQL2003's CREATE SEQUENCE a little more
>closely, but there's a bunch more work that can be done, if we want to
>be fully SQL-compliant.

Neil, I'm not advocating a change.  As long as Postgres sequences
don't look like SQL2003 sequence generators there is no problem, if
they behave differently.  OTOH if we have standard syntax, I'd prefer
to have standard semantics, too.  Maybe we can have classic Postgres
syntax (nextval('...')) with classic Postgres behaviour and SQL2003
syntax (NEXT VALUE FOR ...) with SQL2003 behaviour side by side?

CURRENT_TIMESTAMP is another issue, because it looks like standard
SQL, but ...

ServusManfred