Re: [GENERAL] Custom shuffle function stopped working in 9.6

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: [GENERAL] Custom shuffle function stopped working in 9.6
Дата
Msg-id CAFj8pRCezCGy10XcjeWGk31UuCcAd7RYcG=nJv8=J7mzk+=rPQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] Custom shuffle function stopped working in 9.6  (Alexander Farber <alexander.farber@gmail.com>)
Список pgsql-general
Дерево обсуждения


2017-02-11 19:51 GMT+01:00 Alexander Farber <alexander.farber@gmail.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


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Custom shuffle function stopped working in 9.6
Следующее
От: Frank van Vugt
Дата:
Сообщение: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public