Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Custom shuffle function stopped working in 9.6 - Mailing list pgsql-general

From Pavel Stehule
Subject Re: [GENERAL] Custom shuffle function stopped working in 9.6
Date
Msg-id CAFj8pRCezCGy10XcjeWGk31UuCcAd7RYcG=nJv8=J7mzk+=rPQ@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL] Custom shuffle function stopped working in 9.6  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Tree view


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 by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Custom shuffle function stopped working in 9.6
Next
From: Frank van Vugt
Date:
Subject: [GENERAL] intentional or oversight? pg_dump -c does not restore default priviliges on schema public