Re: functions with side effect

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

[1] http://www.wiscorp.com/sql_2003_standard.zip
    5WD-02-Foundation-2003-09.pdf
        4.21.2 Operations involving sequence generators

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 19.07.2018 19:43, Torsten Förtsch wrote:
> On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> 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?
>
> Assuming you are only working in single session:
>
> https://www.postgresql.org/docs/10/static/functions-sequence.html
>
> "currval
>
>      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.
>