Re: functions with side effect

Pavel Luzanov <p.luzanov@xxxxxxxxxxxxxx> · Thu, 19 Jul 2018 20:18:43 +0300



    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.klaver@xxxxxxxxxxx>
            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.