Geek asks and answers

PostgreSQL constant expressions and subqueries

In a message to Bruce Momjian Generating Random Data via SQL, he used the following code to generate 5 random rows:

SELECT
(
        SELECT string_agg(x, '')
        FROM (
                SELECT chr(ascii('a') + floor(random() * 26)::integer)
                FROM generate_series(1, 40 + b * 0) as f(g)
        ) AS y(x)
) AS result
FROM generate_series(1,5) as a(b);

              result                  
------------------------------------------
 plwfwcgajxdygfissmxqsywcwiqptytjjppgrvgb
 sjaypirhuoynnvqjdgywfsfphuvzqbbilbhakyhf
 ngtabkjfqibwahlicgisijatliuwgbcuiwujgeox
 mqtnyewalettounachwjjzdrvxbbbpzogscexyfi
 dzcstpsvwpefohwkfxmhnlwteyybxejbdltwamsx
(5 rows)

I wondered why 'b * 0' in line 6 is required. When I deleted it, the result was changed to 5 exactly similar lines, which means that Postgres caches the external expression (result)!

I could not find how expansive caching works in Postgres. According to the documentation , the random () function is marked VOLATILE, so I expect that some expression will also depend on it.

How does expression caching work in Postgres? Is it documented anywhere? Why did 'b * 0' disable the cache where random () did not?

Update:

, 'b * 0' floor(), /, random():

...
                SELECT chr(ascii('a') + floor(random() * 26 + b * 0)::integer)
                FROM generate_series(1, 40) as s(f)
...

- ; .

: ,

create sequence seq_test;

SELECT (SELECT nextval('seq_test')) FROM generate_series(1,5);

 ?column? 
----------
        1
        1
        1
        1
        1
(5 rows)
+5
1

, random() , , .

b*0, :

b*0:

 Function Scan on generate_series a  (cost=0.00..37530.00 rows=1000 width=4)
   SubPlan 1
     ->  Aggregate  (cost=37.51..37.52 rows=1 width=32)
           ->  Function Scan on generate_series  (cost=0.01..25.01 rows=1000 width=0)

b*0:

 Function Scan on generate_series a  (cost=37.52..47.52 rows=1000 width=0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=37.50..37.51 rows=1 width=32)
           ->  Function Scan on generate_series  (cost=0.00..25.00 rows=1000 width=0)

PostgreSQL , a, InitPlan, . a, .. , a.

+4

More articles:


All Articles