hi,
Although I do a lot with postgres, I am quite new to functions. So
yesterday I was playing around with a very simple one. Of course I did
something very stupid and created an "infinite loop" in the end.
My problem is, that this crashed one of my postgres server, but the
other just through an error with
"LOG: autovacuum: processing database "adidas_myrunning"
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth".
CONTEXT: SQL statement ..."
The one that crashed through out this:
"[2007-07-03 19:03:14 JST mtool@mailing_tool_live idle] LOG: statement:
insert into mail_log (mailid, log_time_raw) values ('l617kuTu072790',
'Jul 2 17:31:33');
[2007-07-03 19:03:15 JST @ ] LOG: server process (PID 10345) was
terminated by signal 11
[2007-07-03 19:03:15 JST @ ] LOG: terminating any other active server
processes"
after that it restarted itself again.
The function I created was this:
CREATE OR REPLACE FUNCTION insert_mail_log() RETURNS "trigger"
AS $$
DECLARE
myrec RECORD;
BEGIN
IF TG_OP = 'INSERT' THEN
SELECT INTO myrec t.* FROM mail_log t WHERE t.log_time_raw =
NEW.log_time_raw AND t.mailid = NEW.mailid;
IF NOT FOUND THEN
INSERT INTO mail_log (ident, email, log_time_raw, log_time,
mail_host, dsn, status, relay, mailid, exported) VALUES (NEW.ident,
NEW.email, NEW.log_time_raw, NEW.log_time, NEW.mail_host, NEW.dsn,
NEW.status, NEW.relay, NEW.mailid, NEW.exported);
RAISE EXCEPTION 'NOT FOUND INSERT';
RETURN NEW;
ELSE
RAISE EXCEPTION 'FOUND UPDATE';
RETURN NULL;
END IF;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
of course my logic flow was wrong, and an insert into the table where I
call this as an trigger is totaly stupid.
both servers are debian packages 8.1.9-1, but on the one that crashed I
increased the stack depth to 8192. Could it be that postgres ran out of
memory and terminated itself?
--
[ Clemens Schwaighofer -----=====:::::~ ]
[ TEQUILA\ Japan IT Group ]
[ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp ]
Attachment:
signature.asc
Description: OpenPGP digital signature
