Обсуждение: NEXT VALUE FOR
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@ip9.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@vmware.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@sss.pgh.pa.us> wrote: > Thomas Munro <munro@ip9.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@ip9.org> wrote: > On 2 October 2014 14:48, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Thomas Munro <munro@ip9.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@ip9.org> writes:
> On 2 October 2014 14:48, Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.us> wrote: > Thomas Munro <munro@ip9.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@ip9.org> writes:
> On 3 October 2014 00:18, Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.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 atTom> command ID 1. You could fix that particular objectionby alsoTom> tracking virtual xid. But the bigger issue is that usingTom> es_processed for this seems like an utterhack. It's not meantTom> to be anything but statistical, and it's not maintained anywayTom> for non-canSetTag queries(ie, DO ALSO rule commands). ThatTom> reflects the fact that what it's meant to do is count the numberTom> of rowsreturned to the executor's caller, which isn'tTom> 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)