Selecting a random row

Hi!

I have to select a random row from a table where primary key isn't
continuous (some rows have been deleted). Postgres just seems to do
something strange with my method.

--
-- Use the order by desc limit 1 -trick to get maximum value
--
CREATE OR REPLACE FUNCTION max_uid() RETURNS int4 AS
  'SELECT uid FROM users WHERE status = ''a'' ORDER BY uid DESC LIMIT 1'
  LANGUAGE 'sql';


--
-- Choose a random point between 0 and max_uid and select the first
-- value from the bigger part
--
CREATE OR REPLACE FUNCTION random_uid() RETURNS int4 AS
  'SELECT uid FROM users u WHERE u.status = ''a'' AND uid >=
   cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER) ORDER BY uid
   ASC LIMIT 1'
  LANGUAGE 'sql';

--
-- testing and looks good
--
galleria=> SELECT max_uid();
 max_uid
---------
  126263

--
-- testing...
--
galleria=> SELECT random_uid(), random_uid(), random_uid(), random_uid(), random_uid();
 random_uid | random_uid | random_uid | random_uid | random_uid
------------+------------+------------+------------+------------
        322 |        601 |        266 |        427 |        369

... but what is this? Values seem to vary from 0 to ~1000.
Not from 0 to 126263!!

How about doing some manual work...

--
-- Testing split point selection
--
galleria=> SELECT cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER);
 int4
-------
 43279

--
-- And inserting split point manually
--
galleria=> SELECT uid FROM users u WHERE u.status = 'a' AND uid >= 43279
           ORDER BY uid ASC LIMIT 1;
  uid
-------
 43284

Works just fine!


Is there any explanation for this strange behavior or are there better
ways to select a random row?

I'm using PG 8.0 b2. Plan for the query is:

 Limit  (cost=0.00..5.19 rows=1 width=4)
   ->  Index Scan using users_pkey on users u  (cost=0.00..69145.26 rows=13329 width=4)
         Filter: ((status = 'a'::bpchar) AND (uid >= ((((max_uid() - 1))::double precision * random()))::integer))

    |\__/|
    ( oo )    Kari Lavikka - tuner@bdb.fi
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
      ""