pl/perl error

Поиск
Список
Период
Сортировка
От Frank
Тема pl/perl error
Дата
Msg-id 20060408215319.GA78596@forbidden.dough.net
обсуждение исходный текст
Ответы Re: pl/perl error  (Douglas McNaught <doug@mcnaught.org>)
Re: pl/perl error  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I have a perl script running as a daemon.  It's using DBD::Pg (1.43) to
connect to my Postgres server (8.0.7) running on the same box and talking
over a socket.  When I start the server, it runs fine for about a day, and
then at some point I start getting this error repeatedly:

DBD::Pg::db do failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

There are only two calls to DBI->do in my script which amount to:

update foo set ipaddr = null where ipaddr = '$ip' and mac != '$mac'

and

update foo set ipaddr = '$ip' where mac = '$mac'

The first statement is run fairly often and I believe it's the one
resulting in the error.  Usually when it is run, it doesn't actually modify
the database, i.e. mac will == $mac.

As soon as I restart my script, it runs fine again for about a day and then
it blows up in the same fashion.

My table is set up like:

CREATE TABLE foo (
    mac    macaddr UNIQUE,
    ipaddr inet    UNIQUE
);

There are two triggers on this table:

create trigger new_mac
 AFTER INSERT OR UPDATE ON foo
 FOR EACH ROW EXECUTE PROCEDURE new_foo_fx();

create trigger remove_mac
 BEFORE DELETE ON macs
 FOR EACH ROW EXECUTE PROCEDURE remove_foo_fx();

Neither of these procedures modify what the statement does to the database.
They make some external changes and then "return;".  The only hit I see in
Google for this error message refers to the trigger documentation which
talks about the return value of triggers.  However, section 32.1 (Overview
of Trigger Behavior) and section 37.6 (PL/Perl Triggers) seem to disagree.
I went with the PL/Perl example assuming that it does the Right Thing
behind the scenes.

At first I thought this was my script losing connection to the database, so
I started using DBI->connect_cached but that didn't change anything.
Neither of my calls to DBI->do() seem to throw an error, i.e.

$dbh->do($statement) or  { warn $dbh->errstr }

doesn't output anything.

I just increased my postgres debug to -d 2 to see if that provides any
useful information.  Does anyone else have any debugging suggestions or
know what might be causing this problem?

Thanks,
Frank

В списке pgsql-general по дате отправления:

Предыдущее
От: Chris Travers
Дата:
Сообщение: Re: Debian package for freeradius_postgresql module
Следующее
От: "zagman"
Дата:
Сообщение: Postgresql with Access 97