Re: Random not so random

"Arnau Rebassa" <arebassa@hotmail.com> writes:

>    select * from messages order by random() limit 1;
>
> in the table messages I have more than 200 messages and a lot of times, the
> message retrieved is the same. Anybody knows how I could do a more "random"
> random?

What OS is this? Postgres is just using your OS's random()/srandom() calls. On
some platforms these may be poorly implemented and not very random.

However of the various choices available I think random/srandom are a good
choice. I'm surprised you're finding it not very random.

Incidentally, are you reconnecting every time or is it that multiple calls in
a single session are returning the same record? It ought not make a difference
as Postgres is careful to seed the random number generator with something
reasonable though.

In a quick test of my own on linux with glibc 2.3.2.ds1 (no, I have no idea
what the ds1 means) It seems fairly random to me:

 test=> create table test4 as (select (select case when b.b then a else a end from test  order by random() limit 1) as
bfrom b limit 1000); 
 SELECT
 test=> select count(*),b from test4 group by b;
  count | b
 -------+---
    210 | 5
    195 | 4
    183 | 3
    203 | 2
    209 | 1
 (5 rows)

 And the same thing holds if I test just the low order bits too:

 test=> create table test4 as (select (select case when b.b then a else a end from test  order by random() limit 1) as
bfrom b limit 1000); 
 SELECT
 test=> select count(*),b from test4 group by b;
  count | b
 -------+---
    249 | 4
    241 | 3
    259 | 2
    251 | 1
 (4 rows)

--
greg