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

[Francisco Olarte]

On Sat, Feb 11, 2017 at 5:37 PM, Alexander Farber
 wrote:
...
> after switching to 9.6.2 from 9.5.3 the following custom function has
> stopped working:
> CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
> RETURNS varchar[] AS
> Any suggestions for a better shuffling function please?

I've seen several sugestions and hints, but seem no one sugested the
classical shuffling algorithm. Even when of the solutions seems to be
not guaranteed to stop.

An easy way to shuffle is swap every element with a random one from
its position to the start or end ( NOT a random one on the array, this
will give you N^N combinations on an N element array, which does not
evenly divide the N! permutations on an array ( see at end ) ) ( of
course even my version is not going to give you that given random() is
not perfect, but it will be a bit better ).

Not having access to a server I've just tried this on 9.3 on sqlfiddlle:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$$
declare
   a varchar[]:=in_array;
   n integer:=array_length(a,1);
   tmp varchar;
   r integer;
 begin
for i in reverse n..2 loop
  r := floor(random()*i) + 1;
  tmp=a[i]; a[i]=a[r]; a[r]=tmp;
end loop;
return a;
 end
$$
LANGUAGE plpgsql volatile

As you can see I do it from the end swapping it with elements from the
start ( this way I swap i in the range 1..i, instead of i, n wich is a
little harder to debug ). I stop at 2 because element 1 can only be
swapped with itself. I've marked it volatile as it returns different
things each time you call it. My tests show it working, but it may
have some problems with the type conversions, as I'm not used to do
this kind of code in plpgsql, but you can get the idea.

Francisco Olarte.

P.S.:
-- shufflings of three elements, with any or from its pos to the end:


Swapping with any element in the array
0,0,0: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,0)=> cab
0,0,1: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,1)=> bca
0,0,2: abc =>swap(0,0)=> abc =>swap(1,0)=> bac =>swap(2,2)=> bac
0,1,0: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,0)=> cba
0,1,1: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,1)=> acb
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,0: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,0)=> bca
0,2,1: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,1)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,0,0: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,0)=> cba
1,0,1: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,1)=> acb
1,0,2: abc =>swap(0,1)=> bac =>swap(1,0)=> abc =>swap(2,2)=> abc
1,1,0: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,0)=> cab
1,1,1: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,1)=> bca
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,0: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,0)=> acb
1,2,1: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,1)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,0,0: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,0)=> acb
2,0,1: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,1)=> bac
2,0,2: abc =>swap(0,2)=> cba =>swap(1,0)=> bca =>swap(2,2)=> bca
2,1,0: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,0)=> abc
2,1,1: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,1)=> cab
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,0: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,0)=> bac
2,2,1: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,1)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 4
F(acb) = 5
F(bac) = 5
F(bca) = 5
F(cab) = 4
F(cba) = 4
Swapping from its own position in the array to the end ( last can be
omitted, of course )
0,1,2: abc =>swap(0,0)=> abc =>swap(1,1)=> abc =>swap(2,2)=> abc
0,2,2: abc =>swap(0,0)=> abc =>swap(1,2)=> acb =>swap(2,2)=> acb
1,1,2: abc =>swap(0,1)=> bac =>swap(1,1)=> bac =>swap(2,2)=> bac
1,2,2: abc =>swap(0,1)=> bac =>swap(1,2)=> bca =>swap(2,2)=> bca
2,1,2: abc =>swap(0,2)=> cba =>swap(1,1)=> cba =>swap(2,2)=> cba
2,2,2: abc =>swap(0,2)=> cba =>swap(1,2)=> cab =>swap(2,2)=> cab
F(abc) = 1
F(acb) = 1
F(bac) = 1
F(bca) = 1
F(cab) = 1
F(cba) = 1


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

Alexander Farber  writes:
> after switching to 9.6.2 from 9.5.3 the following custom function has
> stopped working:

> CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
> RETURNS varchar[] AS
> $func$
> SELECT array_agg(letters.x) FROM
> (SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
> $func$ LANGUAGE sql STABLE;

Expansion of SRFs in the targetlist now happens after ORDER BY.
So the ORDER BY is sorting a single dummy row and then the unnest
happens after that.  See

https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=9118d03a8

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Pavel Stehule]

2017-02-11 19:51 GMT+01:00 Alexander Farber :

> 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

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

[Adrian Klaver]

On 02/11/2017 10:51 AM, Alexander Farber wrote:

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}



To future proof your code follow the advice shown in the doc snippet in 
the first answer to your SO question. This is what I showed in my 
answers to your questions.


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Alexander Farber]

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}

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

[Alexander Farber]

words=> select version();
 version

--
 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

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

[Jack Christensen]

On 02/11/2017 11:36 AM, Adrian Klaver wrote:

On 02/11/2017 09:17 AM, Alexander Farber wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
  unnest

  a
  b
  c
  d
  e
  f
(6 rows)


postgres=> select version();
version
-
  PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 
64-bit
(1 row)

postgres=> select * from  unnest(ARRAY['a','b','c','d','e','f']) order by 
random();
  unnest

  d
  c
  a
  f
  e
  b
(6 rows)

postgres=> select * from  unnest(ARRAY['a','b','c','d','e','f']) order by 
random();
  unnest

  b
  d
  e
  c
  a
  f
(6 rows)





I can duplicate issue on 9.6.2.

jack=# select version();
 version
--
 PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit
(1 row)

jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

jack=# select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

Jack



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

On 02/11/2017 09:17 AM, Alexander Farber wrote:

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)



So back to your original question:

CREATE OR REPLACE FUNCTION public.words_shuffle(in_array character 
varying[])

 RETURNS character varying[]
 LANGUAGE sql
 STABLE
AS $function$
SELECT array_agg(letters.x) FROM
(SELECT * FROM  UNNEST(in_array) x ORDER BY RANDOM()) letters;
$function$


postgres=>  select * from  words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {d,f,a,e,c,b}
(1 row)

postgres=>  select * from  words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {c,d,a,e,f,b}
(1 row)


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

On 02/11/2017 09:17 AM, Alexander Farber wrote:
> I think ORDER BY RANDOM() has stopped working in 9.6.2:
> 
> words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
>  unnest 
> 
>  a
>  b
>  c
>  d
>  e
>  f
> (6 rows)
> 

postgres=> select version();
   version  
 
-
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.5, 
64-bit
(1 row)

postgres=> select * from  unnest(ARRAY['a','b','c','d','e','f']) order by 
random();
 unnest 

 d
 c
 a
 f
 e
 b
(6 rows)

postgres=> select * from  unnest(ARRAY['a','b','c','d','e','f']) order by 
random();
 unnest 

 b
 d
 e
 c
 a
 f
(6 rows)



-- 
Adrian Klaver
adrian.kla...@aklaver.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

I don't use an array, but perhaps you can adapt to this function which
works in 9.6.1

CREATE OR REPLACE FUNCTION public.scramble(text)
  RETURNS text AS
$BODY$
DECLARE
p_inALIAS FOR $1;
v_outTEXT DEFAULT '';
v_modTEXT;
v_lenINT4;
v_ctrINT4;
v_posINT4;
v_array CHAR[];

BEGIN

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP
  v_array[v_ctr] = NULL;
  v_ctr := v_ctr +1;
END LOOP;

v_ctr = 1;
WHILE v_ctr <= LENGTH(p_in) LOOP

v_pos := INT4(random() * 100);
IF v_pos > LENGTH(p_in) OR v_array[v_pos] IS NOT NULL THEN
CONTINUE;
END IF;

v_mod := substring(p_in from v_pos for 1);

v_array[v_pos] := '*';
v_out := v_out || v_mod;
v_ctr := v_ctr +1;

END LOOP;

RETURN v_out;
END;
-- SAMPLE CALL
-- SELECT scramble('Honor');
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.scramble(text)
  OWNER TO postgres;


On Sat, Feb 11, 2017 at 12:17 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> I think ORDER BY RANDOM() has stopped working in 9.6.2:
>
> words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
>  unnest
> 
>  a
>  b
>  c
>  d
>  e
>  f
> (6 rows)
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[Alexander Farber]

I think ORDER BY RANDOM() has stopped working in 9.6.2:

words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
 unnest

 a
 b
 c
 d
 e
 f
(6 rows)

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

[Pavel Stehule]

Hi

2017-02-11 17:37 GMT+01:00 Alexander Farber :

> Good evening,
>
> after switching to 9.6.2 from 9.5.3 the following custom function has
> stopped working:
>
> CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
> RETURNS varchar[] AS
> $func$
> SELECT array_agg(letters.x) FROM
> (SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
> $func$ LANGUAGE sql STABLE;
>
> In 9.5.3 it was shuffling characters:
>
> words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
>  words_shuffle
> ---
>  {c,d,b,a,e,f}
> (1 row)
>
> But in 9.6.2 it has stopped doing so:
> words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
>  words_shuffle
> ---
>  {a,b,c,d,e,f}
> (1 row)
>
> Any suggestions for a better shuffling function please?
>

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT x FROM UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

there was some optimisations for faster expression evaluation - probably
this is one effect of this optimisation.

generally SRF function should not be used in target list - now when we have
LATERAL join, it is not necessary

Regards

Pavel



>
> Regards
> Alex
>
>

[GENERAL] Custom shuffle function stopped working in 9.6

[Alexander Farber]

Good evening,

after switching to 9.6.2 from 9.5.3 the following custom function has
stopped working:

CREATE OR REPLACE FUNCTION words_shuffle(in_array varchar[])
RETURNS varchar[] AS
$func$
SELECT array_agg(letters.x) FROM
(SELECT UNNEST(in_array) x ORDER BY RANDOM()) letters;
$func$ LANGUAGE sql STABLE;

In 9.5.3 it was shuffling characters:

words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {c,d,b,a,e,f}
(1 row)

But in 9.6.2 it has stopped doing so:
words=> select words_shuffle(ARRAY['a','b','c','d','e','f']);
 words_shuffle
---
 {a,b,c,d,e,f}
(1 row)

Any suggestions for a better shuffling function please?

Regards
Alex