Re: Custom shuffle function stopped working in 9.6

2017-02-11 19:51 GMT+01:00 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:

> At the same time this advice from
> http://stackoverflow.com/questions/42179012/how-to-
> shuffle-array-in-postgresql-9-6-and-also-lower-versions
> works, don't know why though:
>
> words=> select array_agg(u order by random())
> words-> from unnest(array['a','b','c','d','e','f']) u;
> array_agg
> ---------------
> {d,a,f,c,b,e}
>

There is a change in plan

postgres=# explain analyze verbose select * from
unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
Sort (cost=4.57..4.82 rows=100 width=40) (actual time=0.054..0.056 rows=6
loops=1)
Output: unnest, (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Function Scan on pg_catalog.unnest (cost=0.00..1.25 rows=100
width=40) (actual time=0.029..0.033 rows=6 loops=1)
Output: unnest, random()
Function Call: unnest('{a,b,c,d,e,f}'::text[])
Planning time: 0.125 ms
Execution time: 0.119 ms

postgres=# explain analyze verbose select
unnest(ARRAY['a','b','c','d','e','f']) order by random();
QUERY PLAN

------------------------------------------------------------------------------------------------
ProjectSet (cost=0.02..0.54 rows=100 width=40) (actual time=0.032..0.037
rows=6 loops=1)
Output: unnest('{a,b,c,d,e,f}'::text[]), (random())
-> Sort (cost=0.02..0.03 rows=1 width=8) (actual time=0.020..0.021
rows=1 loops=1)
Output: (random())
Sort Key: (random())
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..0.01 rows=1 width=8) (actual
time=0.006..0.006 rows=1 loops=1)
Output: random()
Planning time: 0.100 ms
Execution time: 0.072 ms

In second case, the random function is called only once, and result is
multiplied.

Maybe it is bug, because volatile functions should be evaluated every time

Regards

Pavel