Re: ORDER BY random() LIMIT 1 slowness

OK Gabor,

I'm the one who misunderstood.

To me, it seem to be a bug (or at least a mis-feature) that one cannot
call currval() before calling nextval().

Does anyone know why it should be like this?

JLL


SZUCS Gábor wrote:
>
> Dear Jean-Luc,
>
> I don't think my simplified example missed any of your solution's features.
> The essence, in my eyes, is that it has nothing to do with tables. It's only
> related to sequences.
>
> In short, you _cannot_ use currval() in any single _session_ until you use
> nextval() in the same session, even if you created the sequence in the very
> same session. Using a serial field in a table or using the sequence directly
> is indifferent.
>
> Or I'm missing something here.
>
> As for Tom's solution:
>
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> Sent: Wednesday, December 18, 2002 4:56 PM
>
> > Personally though, I'd skip the sequence entirely and do
> >
> > create table poetry (...,
> >      rand float8 default random());
> > create index on poetry.rand
> >
> > select * from poetry where rand > random() order by rand limit 1;
>
> I'm not sure it's as flat as a random number should be. I have some relation
> to mathematics but can't see it clearly right now. I fear it's more likely a
> normal distribution, not linear (or whatsits called). But if I needed
> something like this, I'd be happy with this solution anyway.
>
> G.
> --
> while (!asleep()) sheep++;
>
> ---------------------------- cut here ------------------------------
> ----- Original Message -----
> From: "Jean-Luc Lachance" <jllachan@nsd.ca>
> Sent: Wednesday, December 18, 2002 5:55 PM
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly