Thread: selecting a random record

selecting a random record

From

Culley Harrelson

Date:

06 February 2001, 18:11:11

Any suggestions on how to select a random record from
any given table?

Culley

__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/

Re: selecting a random record

From

Warren Vanichuk

Date:

06 February 2001, 18:32:19

Greetings.

I have a smallish sized database that's getting alot of update transactions
to it.  It's been running fine over the past several weeks, but suddenly I'm
starting to see :

NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.

appear in my apache error logs from my PHP statements.  At the same time the
following messagse appear in Postgres logs :

NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
ERROR:  WaitOnLock: error on wakeup - Aborting this transaction
DEBUG:  Last error occured while executing PL/pgSQL function add_impression
DEBUG:  line 16 at SQL statement
NOTICE:  current transaction is aborted, queries ignored until end of transaction block
NOTICE:  current transaction is aborted, queries ignored until end of transaction block

This is PostgreSQL 7.0.3 running on a Debian Stable system.  All
transactions to the database are via PHP 4.0.4pl1 under Apache 1.3.17.  I've
made no modifications to the system itself for several weeks (outside of
upgrading apache) and it was running fine under similiar loads in the past.

Any suggestions one how I can rectify this situation would be appriecated.  :)

For reference, the function in question is declared as :

CREATE function add_impression( int4, int4, text ) RETURNS int4 AS '

        DECLARE
                tempvar record;
                linkidx ALIAS FOR $1;
                linkcat ALIAS FOR $2;
                linkloc ALIAS FOR $3;

        BEGIN
                SELECT INTO tempvar *
                FROM linkdaystatsdetail
                WHERE linkid = linkidx
                AND categorynumber = linkcat
                AND location = linkloc;

                IF FOUND THEN
                        UPDATE linkoverviewstatsdetail
                        SET impressions=impressions+1
                        WHERE linkid = linkidx
                        AND categorynumber = linkcat
                        AND location = linkloc;
                ELSE
                        INSERT INTO linkoverviewstatsdetail ( linkid, categorynumber, location, clicks, impressions )
                                VALUES ( linkidx, linkcat, linkloc, 0, 1 );
                END IF;

                UPDATE linkoverviewstats
                SET impressions=impressions+1
                WHERE linkid = linkidx;

                SELECT INTO tempvar *
                FROM linkdaystatsdetail
                WHERE linkid = linkidx
                AND categorynumber = linkcat
                AND location = linkloc;

                IF FOUND THEN
                        UPDATE linkdaystatsdetail
                        SET impressions=impressions+1
                        WHERE linkid = linkidx
                        AND categorynumber = linkcat
                        AND location = linkloc;
                ELSE
                        INSERT INTO linkdaystatsdetail ( linkid, categorynumber, location, clicks, impressions )
                                VALUES ( linkidx, linkcat, linkloc, 0, 1 );
                END IF;

                UPDATE linkdaystats
                SET impressions=impressions+1
                WHERE linkid = linkidx;

                RETURN 1;

        END;
' LANGUAGE 'PLPGSQL';

linkoverviewstatsdetail and linkdaystatsdetail are very similiar in composition, the schema being :

freehost=# \d linkdaystatsdetail
       Table "linkdaystatsdetail"
   Attribute    |    Type    | Modifier
----------------+------------+----------
 linkid         | integer    |
 categorynumber | integer    |
 location       | varchar(1) |
 clicks         | integer    |
 impressions    | integer    |
Indices: linkdaystatsdetail_linkid_idx,
         linkdsd_linkid_cat_loc_idx

freehost=# \d linkdsd_linkid_cat_loc_idx
Index "linkdsd_linkid_cat_loc_idx"
   Attribute    |    Type
----------------+------------
 linkid         | integer
 categorynumber | integer
 location       | varchar(1)
btree

freehost=# \d linkdaystatsdetail_linkid_idx
Index "linkdaystatsdetail_linkid_idx"
 Attribute |  Type
-----------+---------
 linkid    | integer
btree

Thanks in advance,

Sincerely, Warren

Re: selecting a random record

From

Culley Harrelson

Date:

06 February 2001, 19:25:58

Can this be done in the framework of plpgsql?  I know
I can do it in the front end (java) but it would be
nice to aviod having to grab the rowcount first.  I
haven't seen a random function in the documentation.
I could install another language but boy am I lazy :)

--- Mark Lane <mlane@mynewthing.com> wrote:
> On Tuesday 06 February 2001 13:11, you wrote:
> > Any suggestions on how to select a random record
> from
> > any given table?
> >
> > Culley
> >
> Set the range of the table index to a random number
> generator.
>
> if the index ranges from 100 to 10000 then you would
> multiply you random
> number by 9900 and then 100 to generate a random
> index number. Just remember
> to round the number to an integer.
>
> Mark

__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - Buy the things you want at great prices.
http://auctions.yahoo.com/

Re: selecting a random record

From

Tom Lane

Date:

06 February 2001, 22:42:06

Warren Vanichuk <pyber@street-light.com> writes:
> I have a smallish sized database that's getting alot of update transactions
> to it.  It's been running fine over the past several weeks, but suddenly I'm
> starting to see :

> NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.

You've probably added another function that updates the same rows in a
different, conflicting order.  For example, suppose you have two
transactions, one of which does

    UPDATE table SET f1 = 42 WHERE id = 'foo';

while the other is doing

    UPDATE table SET f1 = 23 WHERE id = 'bar';

This is fine, but now suppose the first one does

    UPDATE table SET f2 = 11 WHERE id = 'bar';

It's got to wait to see if the second one commits or not, to know which
version of the 'bar' row is relevant and should be updated.  Finally,
suppose the second transaction does

    UPDATE table SET f2 = 22 WHERE id = 'foo';

Now you have two transactions waiting for each other --- ie, deadlock.

            regards, tom lane

Re: Deadlock issues (was: Re: [GENERAL] selecting a random record)

From

Warren Vanichuk

Date:

06 February 2001, 23:09:26

> Warren Vanichuk <pyber@street-light.com> writes:
> > I have a smallish sized database that's getting alot of update transactions
> > to it.  It's been running fine over the past several weeks, but suddenly I'm
> > starting to see :
>
> > NOTICE:  Deadlock detected -- See the lock(l) manual page for a possible cause.
>
> You've probably added another function that updates the same rows in a
> different, conflicting order.  For example, suppose you have two
> transactions, one of which does
>
>     UPDATE table SET f1 = 42 WHERE id = 'foo';
>
> while the other is doing
>
>     UPDATE table SET f1 = 23 WHERE id = 'bar';
>
> This is fine, but now suppose the first one does
>
>     UPDATE table SET f2 = 11 WHERE id = 'bar';
>
> It's got to wait to see if the second one commits or not, to know which
> version of the 'bar' row is relevant and should be updated.  Finally,
> suppose the second transaction does
>
>     UPDATE table SET f2 = 22 WHERE id = 'foo';
>
> Now you have two transactions waiting for each other --- ie, deadlock.

Hrmm.  There are only two functions that affect those tables.  The
add_impressions does a :

update table set f1+1 where f3 = 'foo' and f4 = 'bar' and f5 = 'baz';

The add_click does a :

update table set f2+1 where f3 = 'foo' and f4 = 'bar' and f5 = 'baz';

All transactions are inside a BEGIN/COMMIT block.

You are saying that will be cause the issue that I have been seeing?  If so,
how exactly does one work around this, given that I need real-time or near
real-time reporting of the data collected by these functions...

Sincerely, Warren

Re: selecting a random record

From

Alexey Borzov

Date:

07 February 2001, 08:09:41

Greetings, Culley!

At 06.02.2001, 13:11, you wrote:

CH> Any suggestions on how to select a random record from
CH> any given table?
    SELECT * FROM any_given_table ORDER BY random() LIMIT 1;

--
Yours, Alexey V. Borzov, Webmaster of RDW

Re: selecting a random record

From

"Arun Malhotra - Roll No.99007"

Date:

11 February 2001, 07:00:41

Hi
I dont know exactly why u need this but this can be done in MySQL/PERL

create a file called random.pl
i select a random reecord from Temp table with PK temp_id

#! /usr/bin/perl -w

use Mysql;
$i   = (10*rand())%10;

$dbh = Mysql->Connect('localhost','DBNAME','root');

$sth = $dbh->Query("Select * from Temp");
$no  = $dbh->Query("Select COUNT(temp_id) from Temp");
@nr  = $sth->FetchRow;

for($n=0; $n<@nr[0]; $n++)
{
    @row = $sth->FetchRow;
    if($n == $i)
    {
        print @row;
    }
}



On Tue, 6 Feb 2001, Culley Harrelson wrote:

> Any suggestions on how to select a random record from
> any given table?
>
> Culley
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - Buy the things you want at great prices.
> http://auctions.yahoo.com/
>