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

Tom Lane wrote:
>"Stephen R. van den Berg" <srb@cuci.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 FUNCTIONcmsfindsid(INT) RETURNS INT AS '
DECLAREvfrom ALIAS FOR $1;vcsid INT;vold INT;vsid INT;
BEGINvcsid:=vfrom;LOOP vold:=vcsid; SELECT INTO vcsid,vsid parent,sid  FROM cmsstruct  WHERE csid=vcsid  LIMIT 1; EXIT
WHENvsid 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!"