Re: Runaway backend at 100% CPU PostgreSQL v8.3.5

Tom Lane wrote:
>"Stephen R. van den Berg" <srb(at)cuci(dot)nl> writes:
>> It seems that the backend is stuck in some kind of endless loop. Since
>> it's a production Debian server, the backend is not compiled with debugging
>> turned on. The best I can do is ltrace it, in hopes of someone recognising
>> the infinite sequence.

>Well, it seems to be repeatedly executing a SPI call, which suggests but
>doesn't prove that it's in a PL function ... but how would it get into
>one while in PARSE state?

>Anyway there's not much data here. You might try attaching to the
>backend process with gdb and trying "bt" and "p debug_query_string";

Interesting, this is what I get:
(gdb) bt
#0 0xb5b3fd54 in ?? () from /usr/lib/postgresql/8.3/lib/plpgsql.so
#1 0x085e9228 in ?? ()
#2 0x00000048 in ?? ()
#3 0xbfec723b in ?? ()
#4 0x083cdb24 in ?? ()
#5 0xbfec74b4 in ?? ()
#6 0x0840d858 in ?? ()
#7 0xbfec6ff8 in ?? ()
#8 0x082e5102 in RevalidateCachedPlan ()
#9 0xb5b4077c in ?? () from /usr/lib/postgresql/8.3/lib/plpgsql.so
#10 0x00000017 in ?? ()
#11 0x085e9248 in ?? ()
#12 0x085e9240 in ?? ()
#13 0x085e9244 in ?? ()
#14 0xccdf2081 in ?? ()
#15 0x2066f5ee in ?? ()
#16 0x00000000 in ?? ()
(gdb) p debug_query_string
$1 = 138920681
(gdb) p (char*)debug_query_string
$2 = 0x847c2e9 "SELECT csi.sid,csi.name\n FROM cmssite AS csi\n WHERE csi.sid=cmsfindsid('506435')\n LIMIT 1;"

Whereas:

CREATE OR REPLACE FUNCTION
cmsfindsid(INT) RETURNS INT AS '
DECLARE
vfrom ALIAS FOR $1;
vcsid INT;
vold INT;
vsid INT;
BEGIN
vcsid:=vfrom;
LOOP
vold:=vcsid;
SELECT INTO vcsid,vsid parent,sid
FROM cmsstruct
WHERE csid=vcsid
LIMIT 1;
EXIT WHEN vsid IS NOT NULL OR vcsid=vold;
END LOOP;
RETURN vsid;
END;
' LANGUAGE PLPGSQL STABLE STRICT;

Where cmsstruct is a table where csid is a PRIMARY KEY INT column.

And, no, there have not been any function or table redefinitions.
--
Sincerely,
Stephen R. van den Berg.

"Very funny, Mr. Scott. Now beam down my clothes!"