Обсуждение: functions with side effect

Torsten Förtsch wrote:
> 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?

You can use at EXPLAIN (VERBOSE) output to see if it will work
in this special case, but I wouldn't depend on it in general.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

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."

> 
> Thanks,
> Torsten


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/19/2018 09:43 AM, Torsten Förtsch wrote:
> On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.
> 

Can't answer definitively, but:

create sequence order_test;

DO
$$
DECLARE
     rs record;
BEGIN
     FOR i IN 1..1000 LOOP
         SELECT INTO rs nextval('order_test'), currval('order_test');
         RAISE NOTICE 'Currval is %', rs.currval;
     END LOOP;
END$$;

has not failed over multiple runs.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver <adrian.klaver@aklaver.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.

On 07/19/2018 09:43 AM, Torsten Förtsch wrote:
> On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.
> 

To eliminate plan caching:

DO
$$
DECLARE
     rs record;
BEGIN
     FOR i IN 1..1000 LOOP
         EXECUTE 'SELECT  nextval($1), currval($1)' INTO rs USING 
'order_test';
         RAISE NOTICE 'Currval is %', rs.currval;
     END LOOP;
END$$;

It still works over multiple runs, even when bumping LOOP counter to 
100,000.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

=?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com> writes:
> 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.

The current implementation evaluates select-list items left to right.
I doubt we'd be eager to change that, since there are surely many
applications that depend on that behavior, whether it's formally specified
or not.  But elsewhere in a query than the select target list, there are
no guarantees, and there's lots of precedent for whacking around the
evaluation order in e.g. WHERE.

I'd be a little more wary with examples like your other one:

SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;

since there's an additional unspecified question there, which is
whether the planner will "flatten" the sub-select.  To put it more
clearly, you'd be taking big risks with

SELECT y, x FROM (VALUES (nextval('s'), currval('s'))) t(x, y);

Right now it seems the nextval is done first, but I would not want to bet
on that staying true in the future.  [ experiments some more ... ]
Actually, looks like we have a rule against flattening sub-selects whose
targetlists contain volatile functions, so maybe you'd get away with that
for the indefinite future too.

            regards, tom lane