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!"