Re: ORDER BY random() LIMIT 1 slowness

Gabor,

You are right about the missing 'r', but I think you missed my point.
You should modify your table so that it has a serial field and reload
it.

JLL

P.S. I run 7.2 so ALTER TABLE ADD rand SERIAL; does not work, but it may
work under 7.3


SZUCS Gábor wrote:
>
> ----- Original Message -----
> From: "Jean-Luc Lachance" <jllachan@nsd.ca>
> Sent: Tuesday, December 17, 2002 5:04 PM
>
> > Gavin,
> >
> > Assuming that you have a serial column rand on poetry and you did not
> > delete any row,
> > here is my suggestion:
> >
> > CREATE TABLE poetry ( rand SERIAL, ... );
> >
> > SELECT * FROM poetry WHERE rand = (
> >   SELECT int8( curval( 'poetry_rand_seq') * random()));
>
> Mmmm... It usually doesn't work for me. Isn't currval (NOTE: with two r's)
> bound to session and has no meaning before the first call to nextval()?
> 7.2.1 says the following; has it changed in 7.3(.*)?
>
> ---------------------------- cut here ------------------------------
> tir=> create sequence test_seq;
> CREATE
> tir=> select currval('test_seq');
> ERROR:  test_seq.currval is not yet defined in this session
> tir=> select nextval('test_seq');
>  nextval
> ---------
>        1
> (1 row)
>
> tir=> select currval('test_seq');
>  currval
> ---------
>        1
> (1 row)
> ---------------------------- cut here ------------------------------
>
> G.
> --
> while (!asleep()) sheep++;
>
> ---------------------------- cut here ------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org