From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Torsten Förtsch <tfoertsch123(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: functions with side effect |
Date: | 2018-07-19 17:24:31 |
Message-ID: | 0bf26f27-7b67-c344-35fd-d4be6f8d21b2@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 07/19/2018 09:43 AM, 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.
>
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2018-07-19 17:43:18 | Re: User documentation vs Official Docs |
Previous Message | Pavel Luzanov | 2018-07-19 17:18:43 | Re: functions with side effect |