Re: selecting a random record

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