Re: PostgreSQL Server Crash using plPHP or PL/Perl - Mailing list pgsql-general

Are you certain that it is the trigger that is crashing the process? If
that is true, then there may be a bug in plperl.

To debug, you could use gdb, but try this first:
Use the strict pragma. To do this in plperl (instead of plperlu), use:
    BEGIN { strict->import(); }
    or set strict mode to on in postgresql.conf [I don't understand why
this isn't the default.]
    You will need to declare all your variables using my $var. [You are
already half-way there because you declare a lot of empty strings.]

You already pepper your code with elog(NOTICE,"") so you can tell us
how far the code gets right? You can use more elogs to hone in on the
line that crashes.

Are you aware that your code will be very costly to execute?

On Jul 11, 2006, at 5:43 PM, Carl M. Nasal II wrote:

> We are writing a multi-master replication process for our Electronic
> Medical Records product.  We have written triggers in plPHP and then
> in PL/Perl to keep an audit trail of the changes as well as flags so
> the data can be replicated.  We started with plPHP, but then server
> started crashing, which reset all connections to the database
> (requiring our application to be restarted).  We then tried to rewrite
> the code using PL/Perl, but the same problem has occurred.  The code
> for the triggers are available at:
>
> http://medical.bmaenterprises.com/audit.plphp
> http://medical.bmaenterprises.com/audit.plperl
>
> We create the triggers by running the follow SQL statement for each
> table:
>
> CREATE TRIGGER config_audit AFTER INSERT OR UPDATE OR DELETE ON config
> FOR EACH ROW EXECUTE PROCEDURE audit();
>
> Any ideas of what is causing the server to crash will be helpful.
>
> Below are the lines from the PostgreSQL serverlog file when the crash
> occurs:
> -----------------------------------------------------------------------
> -----
> LOG:  server process (PID 29153) exited with exit code 255
> LOG:  terminating any other active server processes
> WARNING:  terminating connection because of crash of another server
> process
> DETAIL:  The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
> LOG:  all server processes terminated; reinitializing
> LOG:  database system was interrupted at 2006-07-11 16:01:32 EDT
> LOG:  checkpoint record is at 1/F413F26C
> LOG:  redo record is at 1/F413F26C; undo record is at 0/0; shutdown
> FALSE
> LOG:  next transaction ID: 7628670; next OID: 693120
> LOG:  next MultiXactId: 1; next MultiXactOffset: 0
> LOG:  database system was not properly shut down; automatic recovery
> in progress
> LOG:  redo starts at 1/F413F2B0
> LOG:  record with zero length at 1/F4186D3C
> LOG:  redo done at 1/F4186D14
> LOG:  database system is ready
> LOG:  transaction ID wrap limit is 2147484146, limited by database
> "postgres"
>
>
> Thank you,
> Carl M. Nasal II
> BMA Enterprises, Inc.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬