Thread: NEXT VALUE FOR sequence

The SQL standard has the expression "NEXT VALUE FOR asequence" to do
what we traditionally do with "nextval('asequence')".

This is an attempt to implement this on top of the recently introduced
NextValueExpr node.

If there is no obvious reason why we would not want that, I'll add it
to the next commitfest.

Is this behavior ok:

test=> CREATE SEQUENCE testseq;
test=> PREPARE x AS SELECT NEXT VALUE FOR testseq;
test=> EXECUTE x;
 ?column? 
----------
        1
(1 row)

test=> DROP SEQUENCE testseq;
DROP SEQUENCE
test=> EXECUTE x;
ERROR:  could not open relation with OID 24836

If not, what could be done about it?

Yours,
Laurenz Albe

    Laurenz Albe wrote:

> The SQL standard has the expression "NEXT VALUE FOR asequence" to do
> what we traditionally do with "nextval('asequence')".

The behavior mandated by the standard is that several invocations
of NEXT VALUE on the same sequence on the same output row
must produce the same value. That is:

CREATE SEQUENCE s;
SELECT NEXT VALUE FOR s, NEXT VALUE FOR s
 UNION
SELECT NEXT VALUE FOR s, NEXT VALUE FOR s

should produce
(1,1)
(2,2)

It makes sense that the value does not depend on
the position of the expression as a column.

The trouble of course is that the equivalent with
nextval() would produce instead
(1,2)
(3,4)


There have been previous discussions on the standard syntax
that said that when it will get into postgres, it should go with
the standard conforming semantics.
I guess it would be a much more difficult patch.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> The SQL standard has the expression "NEXT VALUE FOR asequence" to do
> what we traditionally do with "nextval('asequence')".
> This is an attempt to implement this on top of the recently introduced
> NextValueExpr node.

This has been proposed repeatedly, and rejected repeatedly, because in
fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
See SQL:2011 4.22.2 "Operations involving sequence generators":

    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.

This is not terribly exact --- what is a "processed row" in a join query,
for instance?  But it's certainly not supposed to act like independent
executions of nextval() or NextValueExpr would.  Pending somebody doing
the legwork to produce something that at least arguably conforms to the
spec's semantics, we've left the syntax unimplemented.

            regards, tom lane

Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > The SQL standard has the expression "NEXT VALUE FOR asequence" to do
> > what we traditionally do with "nextval('asequence')".
> > This is an attempt to implement this on top of the recently introduced
> > NextValueExpr node.
> 
> This has been proposed repeatedly, and rejected repeatedly, because in
> fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
> See SQL:2011 4.22.2 "Operations involving sequence generators":
> 
>     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.
> 
> This is not terribly exact --- what is a "processed row" in a join query,
> for instance?  But it's certainly not supposed to act like independent
> executions of nextval() or NextValueExpr would.  Pending somebody doing
> the legwork to produce something that at least arguably conforms to the
> spec's semantics, we've left the syntax unimplemented.

Would it be reasonable to say that any two NextValueExpr in the same
target list are "in one row"?

Yours,
Laurenz Albe

On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> Tom Lane wrote:
>> Laurenz Albe <laurenz.albe@cybertec.at> writes:
>> > The SQL standard has the expression "NEXT VALUE FOR asequence" to do
>> > what we traditionally do with "nextval('asequence')".
>> > This is an attempt to implement this on top of the recently introduced
>> > NextValueExpr node.
>>
>> This has been proposed repeatedly, and rejected repeatedly, because in
>> fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
>> See SQL:2011 4.22.2 "Operations involving sequence generators":
>>
>>     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.
>>
>> This is not terribly exact --- what is a "processed row" in a join query,
>> for instance?  But it's certainly not supposed to act like independent
>> executions of nextval() or NextValueExpr would.  Pending somebody doing
>> the legwork to produce something that at least arguably conforms to the
>> spec's semantics, we've left the syntax unimplemented.
>
> Would it be reasonable to say that any two NextValueExpr in the same
> target list are "in one row"?

I think, "processed row" thing gets pretty complicated. Consider
simple case. What happens when NextValueExpr appears in one of the
conditions and that row gets eliminated, do we consider that as a
processed row and increment the NextValueExpr or do not increment it?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Ashutosh Bapat wrote:
> On Tue, Feb 20, 2018 at 8:39 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Tom Lane wrote:
> > > Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > > > The SQL standard has the expression "NEXT VALUE FOR asequence" to do
> > > > what we traditionally do with "nextval('asequence')".
> > > > This is an attempt to implement this on top of the recently introduced
> > > > NextValueExpr node.
> > > 
> > > This has been proposed repeatedly, and rejected repeatedly, because in
> > > fact the standard's semantics for NEXT VALUE FOR are *not* like nextval().
> > > See SQL:2011 4.22.2 "Operations involving sequence generators":
> > > 
> > >     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.
> > > 
> > > This is not terribly exact --- what is a "processed row" in a join query,
> > > for instance?  But it's certainly not supposed to act like independent
> > > executions of nextval() or NextValueExpr would.  Pending somebody doing
> > > the legwork to produce something that at least arguably conforms to the
> > > spec's semantics, we've left the syntax unimplemented.
> > 
> > Would it be reasonable to say that any two NextValueExpr in the same
> > target list are "in one row"?
> 
> I think, "processed row" thing gets pretty complicated. Consider
> simple case. What happens when NextValueExpr appears in one of the
> conditions and that row gets eliminated, do we consider that as a
> processed row and increment the NextValueExpr or do not increment it?

I think that is an unrelated question.

In my opinion both would be ok.  SQL does not decree in which order
conditions are evaluated, and it is ok to evaluate an expression even if
it is never used, right?

All that has to be guaranteed is that using "NEXT VALUE FOR asequence"
several times "in one row" (whatever exactly that is) will return the
same value each time.

The exact rules seem to be:

a) If NVE is directly contained in a <query specification> QS, then the
   General Rules of Subclause 9.21, “Generation of the next value of a
   sequence generator”, are applied once per row in the result of QS
   with SEQ as SEQUENCE. The result of each evaluation of NVE for a given
   row is the RESULT returned by the General Rules of Subclause 9.21,
   “Generation of the next value of a sequence generator”.

I understand:
For each NEXT VALUE FOR that appears in a select list (not a subselect),
return the same value per row.

b) If NVE is directly contained in a <contextually typed table value constructor>
   TVC, then the General Rules of Subclause 9.21, “Generation of the
   next value of a sequence generator”, are applied once per
   <contextually typed row value expression> contained in TVC.
   The result of each evaluation of NVE for a given <row value expression>
   is the RESULT returned by the General Rules of Subclause 9.21,
   “Generation of the next value of a sequence generator”.

I understand:
In a VALUES clause, each invocation of NEXT VALUE FOR should return the same value.

c) If NVE is directly contained in an <update source>, then the General Rules
   of Subclause 9.21, “Generation of the next value of a sequence generator”,
   are applied once per row to be updated by the <update statement: searched>
   or <update statement: positioned>. The result of each evaluation of NVE for a
   given row is the RESULT returned by the General Rules of Subclause 9.21,
   “Generation of the next value of a sequence generator”.

I understand:
In an UPDATE statement, all invocations of NEXT VALUE FOR in the SET clause
should return the same value per row.

Yours,
Laurenz Albe