Re: count(*) and bad design was: Experiences with extensibility

On Jan 15, 2008, at 3:03 PM, Ivan Sergio Borgonovo wrote:

> On Tue, 15 Jan 2008 14:43:35 +0100
> Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> wrote:
>
>> You need to scroll to the last row to find the size of the result
>> set, but after that it's pretty easy to return random rows by
>> scrolling to them (and marking them 'read' in some way to prevent
>> accidentally returning the same row again).
>
> Could you post a snippet of code or something giving a more detailed
> idea of it?
>
> BTW since cursors support offset if you're not interested if the
> order of the retrieved rows is random too you don't even have to
> remember which one you read I think.

I posted it on this list a while ago when I came up with this
solution. I had some trouble finding my old post in the pgsql-general
archives though - I could find the thread, just not my final posting,
and searching didn't even turn up the thread.

I did find it here: http://www.mail-archive.com/pgsql-
general(at)postgresql(dot)org/msg103670.html
The thread contains several other approaches to the problem, it
really depends on your problem domain which one fits your bill.

I think the function in my original posting could do with clearer
comments though, so here's the function again:

/*
* Return $limit random rows from the result set of SQL query $query
*/
function randomSet(
$query, // The query to execute
$limit // The (max) number of random rows required
) {
// SQL to declare the cursor
query("DECLARE _cur SCROLL CURSOR WITHOUT HOLD FOR $query");

/* Get the range for random(1, n)
*
* Determined by scrolling the cursor to the last row.
* Equivalent to select count(*), but without a separate query.
*/
query("MOVE FORWARD ALL IN _cur");
$count = pg_affected_rows();

$uniques = array(); // A list of used cursor offsets
$resultSet = array();

// Fetch random rows until we have enough or there are no more
while ($limit > 0 && count($uniques) < $count) {
// Determine random scroll offset
$idx = random(1, $count);

// Skip records with an index we already used
if (in_array($idx, $uniques))
continue;

//Fetch the random row
$record = query("FETCH ABSOLUTE $idx FROM _cur");

// Add the row offset to the list of used offsets
$uniques[] = $idx;

$resultSet[] = $record;
$limit--;
}

// query
query("CLOSE _cur");

return $resultSet;
}

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,478f32e59497683469944!