Re: NEXT VALUE FOR <sequence>

Hi

SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
tells me that at least DB2, SQL Server and a few niche databases
understand it so far. As far as I can tell there is no standardised
equivalent of currval and setval (but I only have access to second
hand information about the standard, like articles and the manuals of
other products).

Here is a starter patch to add it. To avoid a shift/reduce conflict,
I had to reclassify the keyword NEXT. I admit that I don't fully
understand the consequences of that change! Please let me know if you
think this could fly.

Best regards,
Thomas Munro

On 10/01/2014 07:28 PM, Thomas Munro wrote:
> Hi
>
> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
> tells me that at least DB2, SQL Server and a few niche databases
> understand it so far. As far as I can tell there is no standardised
> equivalent of currval and setval (but I only have access to second
> hand information about the standard, like articles and the manuals of
> other products).
>
> Here is a starter patch to add it. To avoid a shift/reduce conflict,
> I had to reclassify the keyword NEXT. I admit that I don't fully
> understand the consequences of that change! Please let me know if you
> think this could fly.

Looks correct. Of course, it's annoying to have to reserve the NEXT
keyword (as a type_func_name_keyword, not fully reserved).

One way to avoid that is to collapse NEXT VALUE FOR into a single token
in parser.c. We do that for a few other word pairs: NULLS FIRST, NULLS
LAST, WITH TIME and WITH ORDINALITY. In this case you'd need to
look-ahead three tokens, not two, but I guess that'd be doable.

- Heikki

Thomas Munro <munro(at)ip9(dot)org> writes:
> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
> tells me that at least DB2, SQL Server and a few niche databases
> understand it so far. As far as I can tell there is no standardised
> equivalent of currval and setval (but I only have access to second
> hand information about the standard, like articles and the manuals of
> other products).

Have you checked the archives about this? My recollection is that one
reason it's not in there (aside from having to reserve "NEXT") is that
the standard-mandated semantics are not the same as nextval().

regards, tom lane

On Thu, Oct 2, 2014 at 7:27 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
>> tells me that at least DB2, SQL Server and a few niche databases
>> understand it so far. As far as I can tell there is no standardised
>> equivalent of currval and setval (but I only have access to second
>> hand information about the standard, like articles and the manuals of
>> other products).
>>
>> Here is a starter patch to add it. To avoid a shift/reduce conflict,
>> I had to reclassify the keyword NEXT. I admit that I don't fully
>> understand the consequences of that change! Please let me know if you
>> think this could fly.
>
> Looks correct. Of course, it's annoying to have to reserve the NEXT keyword
> (as a type_func_name_keyword, not fully reserved).
>
> One way to avoid that is to collapse NEXT VALUE FOR into a single token in
> parser.c. We do that for a few other word pairs: NULLS FIRST, NULLS LAST,
> WITH TIME and WITH ORDINALITY. In this case you'd need to look-ahead three
> tokens, not two, but I guess that'd be doable.

Those kinds of hacks are not scalable. It's not too bad right now
because NULLS, FIRST, and LAST are all rarely-used keywords and
there's rarely a reason for FIRST and LAST to follow NULLS except in
the exact context we care about. But the more we extend those hacks,
the more likely it is that the lexer will smash the tokens in some
case where the user actually meant something else. Hacking the lexer
to get around grammar conflicts doesn't actually fix whatever
intrinsic semantic conflict exists; it just keeps bison from knowing
about it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2 October 2014 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thomas Munro <munro(at)ip9(dot)org> writes:
>> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
>> tells me that at least DB2, SQL Server and a few niche databases
>> understand it so far. As far as I can tell there is no standardised
>> equivalent of currval and setval (but I only have access to second
>> hand information about the standard, like articles and the manuals of
>> other products).
>
> Have you checked the archives about this? My recollection is that one
> reason it's not in there (aside from having to reserve "NEXT") is that
> the standard-mandated semantics are not the same as nextval().

Right, I found the problem: "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 was discussed in a thread
from 2002 [1].

So the first step would be to make a standard conforming function to transform
the standard's syntax into.

I found the text in the 20nn draft specification and it didn't seem immediately
clear what 'statement' should mean, for example what if your statement calls
pl/pgsql which contains further statements, and what if triggers, default
expressions, etc are invoked? I suppose one approach would be to use command
IDs as the scope. Do you think the following change would make sense?

In struct SeqTableData (from sequence.c), add a member last_command_id.
When you call the new function, let's say nextval_for_command(regclass),
if last_command_id matches GetCommandId() then it behaves like currval_oid
and returns last, otherwise it behaves like nextval_oid, and updates
last_command_id to the current command ID.

BTW there was also a problem with the handling of quoted identifiers (ie case
folding etc), which is fixed in the v2 patch, attached.

[1] http://www.postgresql.org/message-id/j9o9uugl8aeq5bn8cbvcttnoc1f263lt8d@4ax.com

Best regards,
Thomas Munro

On 3 October 2014 00:01, Thomas Munro <munro(at)ip9(dot)org> wrote:
> On 2 October 2014 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Thomas Munro <munro(at)ip9(dot)org> writes:
>>> SQL:2003 introduced the function NEXT VALUE FOR <sequence>. Google
>>> tells me that at least DB2, SQL Server and a few niche databases
>>> understand it so far. As far as I can tell there is no standardised
>>> equivalent of currval and setval (but I only have access to second
>>> hand information about the standard, like articles and the manuals of
>>> other products).
>>
>> Have you checked the archives about this? My recollection is that one
>> reason it's not in there (aside from having to reserve "NEXT") is that
>> the standard-mandated semantics are not the same as nextval().
>
> Right, I found the problem: "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 was discussed in a thread
> from 2002 [1].
>
> So the first step would be to make a standard conforming function to transform
> the standard's syntax into.
>
> I found the text in the 20nn draft specification and it didn't seem immediately
> clear what 'statement' should mean, for example what if your statement calls
> pl/pgsql which contains further statements, and what if triggers, default
> expressions, etc are invoked? I suppose one approach would be to use command
> IDs as the scope. Do you think the following change would make sense?
>
> In struct SeqTableData (from sequence.c), add a member last_command_id.
> When you call the new function, let's say nextval_for_command(regclass),
> if last_command_id matches GetCommandId() then it behaves like currval_oid
> and returns last, otherwise it behaves like nextval_oid, and updates
> last_command_id to the current command ID.

Actually scratch that, it's not about statements, it's about "rows
processed by that
SQL-statement". I will think about how that could be interpreted and
implemented...

Best regards,
Thomas Munro

Thomas Munro <munro(at)ip9(dot)org> writes:
> On 2 October 2014 14:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Have you checked the archives about this? My recollection is that one
>> reason it's not in there (aside from having to reserve "NEXT") is that
>> the standard-mandated semantics are not the same as nextval().

> Right, I found the problem: "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 was discussed in a thread
> from 2002 [1].

Wow, it was that far back? No wonder I didn't remember the details.

> I suppose one approach would be to use command
> IDs as the scope.

The spec clearly says one value per row, not one per statement; so
command ID is very definitely not the right thing.

regards, tom lane

On 3 October 2014 00:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thomas Munro <munro(at)ip9(dot)org> writes:
>> I suppose one approach would be to use command
>> IDs as the scope.
>
> The spec clearly says one value per row, not one per statement; so
> command ID is very definitely not the right thing.

I think (command ID, estate->es_processed) would work. Tracking those
two values in SeqTableData would allow you to detect the level change
meaning the next tuple has been returned by a SELECT, updated by an
UPDATE or inserted by an INSERT. This could be activated by a new
2-argument nextval with a boolean argument to request the standard
behaviour. Then NEXT VALUE FOR could be translated to nextval(...,
true).

But I just can't figure out how to get my hands on the current EState
or QueryDesc from inside a fmgr function, so I can't reach
estate->es_processed from nextval...

Best regards,
Thomas Munro

Thomas Munro <munro(at)ip9(dot)org> writes:
> On 3 October 2014 00:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The spec clearly says one value per row, not one per statement; so
>> command ID is very definitely not the right thing.

> I think (command ID, estate->es_processed) would work.

Not terribly well, eg each new transaction starts over at command ID 1.
You could fix that particular objection by also tracking virtual xid.
But the bigger issue is that using es_processed for this seems like
an utter hack. It's not meant to be anything but statistical, and it's
not maintained anyway for non-canSetTag queries (ie, DO ALSO rule
commands). That reflects the fact that what it's meant to do is count the
number of rows returned to the executor's caller, which isn't necessarily
the definition we'd need here.

regards, tom lane

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>>> The spec clearly says one value per row, not one per statement;
>>> so command ID is very definitely not the right thing.

>> I think (command ID, estate->es_processed) would work.

Tom> Not terribly well, eg each new transaction starts over at
Tom> command ID 1. You could fix that particular objection by also
Tom> tracking virtual xid. But the bigger issue is that using
Tom> es_processed for this seems like an utter hack. It's not meant
Tom> to be anything but statistical, and it's not maintained anyway
Tom> for non-canSetTag queries (ie, DO ALSO rule commands). That
Tom> reflects the fact that what it's meant to do is count the number
Tom> of rows returned to the executor's caller, which isn't
Tom> necessarily the definition we'd need here.

Maybe it would make sense to do something with a SubPlan, rather than
trying to hide everything inside a function?

--
Andrew (irc:RhodiumToad)