Very interesting question. postgres=# create sequence s; CREATE SEQUENCE postgres=# select currval('s'), nextval('s'); ERROR: currval of sequence "s" is not yet defined in this session postgres=# select nextval('s'), currval('s'); nextval | currval ---------+--------- 1 | 1 We see different result with different order of functions. So the question is: in which order expressions evaluated. And I don't think that we can rely on this order.
Moreover, according to SQL standard[1]: "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."
But in fact nextval return new value each time: postgres=# select nextval('s'), nextval('s') from generate_series (1,3); nextval | nextval ---------+--------- 2 | 3 4 | 5 6 | 7
On 07/19/2018 07:15 AM, Torsten Förtsch wrote: > Hi, > > assuming > > SELECT nextval('s'), currval('s'); > > or > > SELECT * FROM (VALUES (nextval('s'), currval('s'))) t; > > is there any guarantee that the 2 output values are the same?
Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did."
I know that. My question was about the execution order of f1 and f2 in "SELECT f1(), f2()". In theory they can be executed in any order. But since the side effect in nextval determines the result of currval, I am asking if that order is well-defined or considered an implementation detail like in C.
