From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
Cc: | Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Strange behavior of some volatile function like random(), nextval() |
Date: | 2016-06-29 12:30:01 |
Message-ID: | CAKFQuwbxcFcCN-FYqwSeH0-GkwLhC6rzVOWSDG=v9vYJLR0e0g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
More specifically...
On Wed, Jun 29, 2016 at 7:34 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:
> On Wed, Jun 29, 2016 at 7:43 PM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
> wrote:
> > Hello!
> >
> > Got some strange behavior of random() function:
> >
> > postgres=# select (select random() ) from generate_series(1,10) as i;
> > random
> > -------------------
> > 0.831577288918197
> > [...]
> > (10 rows)
>
> I recall that this is treated as an implicit LATERAL, meaning that
> random() is calculated only once.
>
A non-correlated (i.e., does not refer to outer variables) subquery placed
into the target-list need only have its value computed once - so that is
what happens. The fact that a volatile function can return different
values given the same arguments doesn't mean much when the function is only
ever called a single time.
> > postgres=# select (select random()+i*0 ) from generate_series(1,10) as i;
> > ?column?
> > --------------------
> > 0.97471913928166
> > [...]
> > (10 rows)
>
> But not that. So those results do not surprise me.
>
>
A correlated subquery, on the other hand, has to be called once for every
row and is evaluated within the context supplied by said row. Each time
random is called it returns a new value.
Section 4.2.11 (9.6 docs)
https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
Maybe this could be worded better but the first part talks about a single
execution while "any one execution" is mentioned in reference to "the
surrounding query".
I do think that defining "correlated" and "non-correlated" subqueries
within this section would be worthwhile.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Sachin Kotwal | 2016-06-29 12:36:43 | Re: pgbench unable to scale beyond 100 concurrent connections |
Previous Message | Fabien COELHO | 2016-06-29 11:37:46 | Re: pgbench unable to scale beyond 100 concurrent connections |