From: | Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: functions with side effect |
Date: | 2018-07-19 17:18:43 |
Message-ID: | ae3576de-585e-401d-a449-1f5477300ddb@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-07-19 17:24:31 | Re: functions with side effect |
Previous Message | Adrian Klaver | 2018-07-19 17:15:45 | Re: functions with side effect |