PostgreSQL SQL - Re: consistent random order

Author

Re: consistent random order

Andreas Kretschmer

2006-11-29, 7:20 pm

Jeff Herrin <jeff@alternateimage.com> schrieb:

> I am returning results ordered randomly using 'order by random()'. My issue has
> to do with page numbers in our web application. When I hit the 2nd page and
> retrieve results with an offset, ordering by random() isn't really what I want
> since I will often receive results that were on the 1st page (they get re-
> randomized).
>
> I'm looking for a way to order in a controled random order. Maybe a UDF.

I think you are searching for CURSORs.

18:25 < akretschmer> ??cursor
18:25 < rtfm_please> For information about cursor
18:25 < rtfm_please> see http://www.postgresql.org/docs/curr...ql-cursors.html
18:25 < rtfm_please> or http://www.postgresql.org/docs/curr...ql-declare.html

With a CURSOR, you get one result-set and can walk through this result.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Jeff Herrin

2006-11-29, 7:20 pm

I don't think cursors are going to help in this case. The order by random() is still going to give different result sets on different pages.

Jeff

----- Original Message -----
From: Andreas Kretschmer < akretschmer@spamfenc
e.net>
To: pgsql-sql@postgresql.org
Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern
Subject: Re: [SQL] consistent random order

Jeff Herrin <jeff@alternateimage.com> schrieb:

> I am returning results ordered randomly using 'order by random()'. My issue has
> to do with page numbers in our web application. When I hit the 2nd page and
> retrieve results with an offset, ordering by random() isn't really what I want
> since I will often receive results that were on the 1st page (they get re-
> randomized).
>
> I'm looking for a way to order in a controled random order. Maybe a UDF.

I think you are searching for CURSORs.

18:25 < akretschmer> ??cursor
18:25 < rtfm_please> For information about cursor
18:25 < rtfm_please> see http://www.postgresql.org/docs/curr...ql-cursors.html
18:25 < rtfm_please> or http://www.postgresql.org/docs/curr...ql-declare.html

With a CURSOR, you get one result-set and can walk through this result.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082Â°, E 13.56889Â°

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Michael Fuhr

2006-11-30, 5:34 am

On Wed, Nov 29, 2006 at 12:32:56PM -0500, Jeff Herrin wrote:
> I don't think cursors are going to help in this case. The order
> by random() is still going to give different result sets on different
> pages.

Have you tried using setseed() to seed the random number generator
to the same value before each query?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Shane Ambler

2006-11-30, 5:34 am

Jeff Herrin wrote:
> I don't think cursors are going to help in this case. The order by random() is still going to give different result sets on different pages.
>
> Jeff

A cursor will maintain the order it was created with until it is
disposed of.

It won't work with a web app though as each page will come from a
different connection in the available pool (or created for each page)
meaning you will loose the cursor between pages.

I would think you want to look at having a sort column that has a random
number in it that is used for sorting.

mysortcol integer default random()

or maybe update the column a couple of times a day to keep the variety
you seem to be after.

> ----- Original Message -----
> From: Andreas Kretschmer < akretschmer@spamfenc
e.net>
> To: pgsql-sql@postgresql.org
> Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern
> Subject: Re: [SQL] consistent random order
>
> Jeff Herrin <jeff@alternateimage.com> schrieb:
>
>
> I think you are searching for CURSORs.
>
> 18:25 < akretschmer> ??cursor
> 18:25 < rtfm_please> For information about cursor
> 18:25 < rtfm_please> see http://www.postgresql.org/docs/curr...ql-cursors.html
> 18:25 < rtfm_please> or http://www.postgresql.org/docs/curr...ql-declare.html
>
> With a CURSOR, you get one result-set and can walk through this result.
>
>
> Andreas

--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jeff Herrin

2006-11-30, 7:20 pm

Michael,

I think you may have solved my problem. We're still experimenting with it but I think setseed is going to work. Thank you very, very much!

Jeff Herrin

----- Original Message -----
From: Michael Fuhr <mike@fuhr.org>
To: Jeff Herrin <jeff@alternateimage.com>
Cc: Andreas Kretschmer < akretschmer@spamfenc
e.net>, pgsql-sql@postgresql.org
Sent: Thursday, November 30, 2006 2:58:23 AM GMT-0500 US/Eastern
Subject: Re: [SQL] consistent random order

On Wed, Nov 29, 2006 at 12:32:56PM -0500, Jeff Herrin wrote:
> I don't think cursors are going to help in this case. The order
> by random() is still going to give different result sets on different
> pages.

Have you tried using setseed() to seed the random number generator
to the same value before each query?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links