DBD::PgSPI crashes database server (SIGSEGV)?

Hi,

I have tried to use "plperlu" to write server functions to encapsulate
some
common functionality such as "copy an entity"(and all its associated
records).

I'm getting a database server crash calling the function from psql or
a perl client. Where the crash happens is kind of random and happens
on different sql
statements.

Manipulating (i.e. inserting) one table seems to be fine but as soon
as I try
to manipulate other related tables in the same function it crashes the
database server.

I was wondering if anyone has had problems (database server crashing)
using
plperlu, where the server function manipulates multipe associated
tables?

I really appreciate your help.

Here is some more detail:
====================================
The versions are perl modules are:
DBI: I have tried 1.35, 1.37, 1.44, etc. they all the same.
DBD::Pg: 1.32
DBD::PgSPI:0.01
-------------------------------------
%rpm -q -i postgresql-server-7.4.5-1PGDG
Name        : postgresql-server            Relocations: (not
relocateable)
Version     : 7.4.5                             Vendor: (none)
Release     : 1PGDG                         Build Date: Thu 19 Aug
2004 03:20:55 EST
Install Date: Tue 07 Sep 2004 11:00:22 EST      Build Host: onpanew
Group       : Applications/Databases        Source RPM:
postgresql-7.4.5-1PGDG.src.rpm
Size        : 7911644                          License: BSD
--------------------------------------
% uname -a
Linux sparkhost 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003
i686 i686 i386 GNU/Linux

*** (redhat 9)
----------------------------------------------------------------------------
%perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 0)
configuration:
  Platform:
    osname=linux, osvers=2.4.20-2.48smp,
archname=i386-linux-thread-multi
    uname='linux str'
    config_args='-des -Doptimize=-O2 -march=i386 -mcpu=i686 -g
-Dmyhostname=localhost -Dperladmin=root@localhost -Dcc=gcc -Dcf_by=Red
Hat, Inc. -Dinstallprefix=/usr -Dprefix=/usr -Darchname=i386-linux
-Dvendorprefix=/usr -Dsiteprefix=/usr
-Dotherlibdirs=/usr/lib/perl5/5.8.0 -Duseshrplib -Dusethreads
-Duseithreads -Duselargefiles -Dd_dosuid -Dd_semctl_semun -Di_db
-Ui_ndbm -Di_gdbm -Di_shadow -Di_syslog -Dman3ext=3pm -Duseperlio
-Dinstallusrbinperl -Ubincompat5005 -Uversiononly
-Dpager=/usr/bin/less -isr'
    hint=recommended, useposix=true, d_sigaction=define
    usethreads=define use5005threads=undef'
 useithreads=define usemultiplicity=
    useperlio= d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=un uselongdouble=
    usemymalloc=, bincompat5005=undef
  Compiler:
    cc='gcc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS
-DDEBUGGING -fno-strict-aliasing -I/usr/local/include
-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm',
    optimize='',
    cppflags='-D_REENTRANT -D_GNU_SOURCE -DTHREADS_HAVE_PIDS
-DDEBUGGING -fno-strict-aliasing -I/usr/local/include
-I/usr/include/gdbm'
    ccversion='', gccversion='3.2.2 20030213 (Red Hat Linux 8.0
3.2.2-1)', gccosandvers=''
gccversion='3.2.2 200302'
    intsize=e, longsize= , ptrsize=p, doublesize=8, byteorder=1234
    d_longlong=define, longlongsize=8, d_longdbl=define,
longdblsize=12
    ivtype='long'
k', ivsize=4'
ivtype='long'
known_ext, nvtype='double'
o_nonbl', nvsize=, Off_t='', lseeksize=8
    alignbytes=4, prototype=define
  Linker and Libraries:
    ld='gcc'
l', ldflags =' -L/usr/local/lib'
ldf'
    libpth=/usr/local/lib /lib /usr/lib
    libs=-lnsl -lgdbm -ldb -ldl -lm -lpthread -lc -lcrypt -lutil
    perllibs=
    libc=/lib/libc-2.3.1.so, so=so, useshrplib=true, libperl=libper
    gnulibc_version='2.3.1'
  Dynamic Linking:
    dlsrc=dl_dlopen.xs, dlext=so', d_dlsymun=undef,
ccdlflags='-rdynamic
-Wl,-rpath,/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE'
    cccdlflags='-fPIC'
ccdlflags='-rdynamic -Wl,-rpath,/usr/lib/perl5', lddlflags='s
Unicode/Normalize XS/A'


Characteristics of this binary (from libperl):
  Compile-time options: DEBUGGING MULTIPLICITY USE_ITHREADS
USE_LARGE_FILES PERL_IMPLICIT_CONTEXT
  Locally applied patches:
        MAINT18379
  Built under linux
  Compiled at Feb 18 2003 22:19:53
  @INC:
    /usr/lib/perl5/5.8.0/i386-linux-thread-multi
    /usr/lib/perl5/5.8.0
    /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi
    /usr/lib/perl5/site_perl/5.8.0
    /usr/lib/perl5/site_perl
    /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi
    /usr/lib/perl5/vendor_perl/5.8.0
    /usr/lib/perl5/vendor_perl
    /usr/lib/perl5/5.8.0/i386-linux-thread-multi
    /usr/lib/perl5/5.8.0

**** I got some warnings on installation of DBI complaining about
using
     multi-threaded version of perl. mmm... I just ignored it!
-----------------------------------------------------------------------------
strace of the server before crash:

%tail -100 /tmp/postmaster-strace
_llseek(35, 114688, [114688], SEEK_SET) = 0
read(35, "\0\0\0\0l\354[!\37\0\0\0\370\0\4\17\360\37\1
\244\237\230"..., 8192) = 8192
_llseek(20, 16384, [16384], SEEK_SET)   = 0
read(20, "\0\0\0\0\360Kh!\37\0\0\0<\5x\20\360\37\1 \344\237\30\0"...,
8192) = 8192
_llseek(22, 73728, [73728], SEEK_SET)   = 0
read(22, "\0\0\0\0P\266\33!\37\0\0\0004\4p\17\360\37\1 \340\237 "...,
8192) = 8192
_llseek(23, 270336, [270336], SEEK_SET) = 0
read(23, "\0\0\0\0\230\301\30!\37\0\0\0\10\1\200\1\0 \1 \200\237"...,
8192) = 8192
_llseek(23, 262144, [262144], SEEK_SET) = 0
read(23, "\0\0\0\0\314\276\27!\37\0\0\0\10\1\200\1\0 \1 \200\237"...,
8192) = 8192
read(37, "\0\0\0\0\20\1\27!\37\0\0\0\210\0\310\0\0 \1 p\237\32\1"...,
8192) = 8192
read(37, "\0\0\0\0\300E\31!\37\0\0\0\200\0\354\0\0 \1 \34\236\306"...,
8192) = 8192
_llseek(39, 57344, [57344], SEEK_SET)   = 0
read(39, "\0\0\0\0\344\250R!\37\0\0\0\310\0\20\1\0 \1
P\237\\\1\240"..., 8192) = 8192
_llseek(39, 16384, [16384], SEEK_SET)   = 0
read(39, "\0\0\0\0t\261\31!\37\0\0\0H\0,\2\0 \1  \235\274\5P\232"...,
8192) = 8192
_llseek(4, 24576, [24576], SEEK_SET)    = 0
read(4, "\0\0\0\0t3P!\37\0\0\0\200\0D\1\0 \1 \340\236@\2\270\235"...,
8192) = 8192
_llseek(4, 49152, [49152], SEEK_SET)    = 0
read(4, "\0\0\0\0\20\244R!\37\0\0\0|\0\240\0\0 \1 \314\236f\2\230"...,
8192) = 8192
_llseek(21, 90112, [90112], SEEK_SET)   = 0
read(21, "\0\0\0\0D\273Z!\37\0\0\0\324\0\20\1\0 \1
l\237(\1\250\236"..., 8192) = 8192
open("/var/lib/pgsql/data/base/483494/483690", O_RDWR|O_LARGEFILE) =
50
_llseek(50, 0, [24576], SEEK_END)       = 0
_llseek(50, 0, [0], SEEK_SET)           = 0
read(50, "\0\0\0\0\220\250=!\37\0\0\0\300\0\210\1\0 \1
L\237h\1\230"..., 8192) = 8192
read(50, "\0\0\0\0,\314=!\37\0\0\0\260\0\274\0\0 \1 $\237\270\1H"...,
8192) = 8192
read(50, "\0\0\0\0T\343\320!\37\0\0\0@\0\240\26\0 \1  \237\300\1"...,
8192) = 8192
--- SIGSEGV (Segmentation fault) @ 0 (0) ---
-----------------------------------------------------------------------
*** ltrace just before the crash on the server:
%tail -20 /tmp/postmaster-ltrace

__strdup(0x085200e0, 0x0851ff90, 0xbfffc598, 0x081691f3, 0x0830aa18) =
0x08329010
open64("/var/lib/pgsql/data/base/483494/"..., 2, 0600) = 50
lseek64(50, 0, 0, 2, 0x0851ffd8)                 = 24576
strncpy(0x0852015c, "syncdatetime", 64)          = 0x0852015c
strlen("SPI TupTable")                           = 12
strcpy(0x0830ab78, "SPI TupTable")               = 0x0830ab78
malloc(8192)                                     = 0x08521a40
memcpy(0x40cdbe64, "\246`\007", 12)              = 0x40cdbe64
lseek64(50, 0, 0, 0, 0x404ca2a8)                 = 0
read(50, "", 8192)                               = 8192
memcpy(0x40cdbe4c, "\246`\007", 12)              = 0x40cdbe4c
read(50, "", 8192)                               = 8192
memcpy(0x40cdbe34, "\246`\007", 12)              = 0x40cdbe34
read(50, "", 8192)                               = 8192
free(0x0851fa38)                                 = <void>
free(0x0853b900)                                 = <void>
free(0x085318e0)                                 = <void>
--- SIGSEGV (Segmentation fault) ---
+++ killed by SIGSEGV +++
-------------------------------------------------------------------------
*** the body of the function. I should add that any function with
similar
nature crashes:

**** from psql prompt I called "select fn_copy_terminal(10, 20);"
**** or tried to execute the above statement from a perl script using
DBD::Pg.
**** Sometimes crashes on section "copy terminal application".
     I have many more similar functions that try to insert into one
table
     and then update other related tables rows that get either
automatically
     inserted by a trigger or I insert the new related rows.
     In all  of them any consequent call to an "insert" crashes!

DROP    FUNCTION fn_copy_terminal   (integer, integer);
CREATE  FUNCTION fn_copy_terminal   (integer, integer) RETURNS integer
AS '

    #
    # Use server side pl/perl postgres module
    #
    use DBD::PgSPI;
    use strict;

    #
    # Read input arg - terminal id to be copied
    #
    my  ($terminal_id, $log_user_id) = @_;

    my $fn = "fn_copy_terminal";

    our $pg_dbh;    # database handle
    my $sql;        # sql statement
    my $data_ref;   # last fetched row(s) reference
    my $vals;       # values
    my %nocopy;     # hash of fields that should not be copied in this
operation

    ########################################################
    #                                                      #
    #               COPY TERMINAL TABLE                    #
    #                                                      #
    ########################################################


    #
    # Find the terminal matching the input id
    #
    $sql = "select * from terminal where terminal_id = $terminal_id;";
    $data_ref = $pg_dbh->selectrow_hashref($sql);
    if (!defined($data_ref) or $pg_dbh->err)
    {
        # Failed to find the terminal with that id
        elog ERROR, "$fn:$sql:terminal not found";
        return undef;
    }
    elog INFO, "$fn:$sql:success";
#
    # Copy all the terminal fields except the following
    #
    %nocopy = ();
    %nocopy = (
                "terminal_id"           => 1,
                "lastsessionstarttime"  => 1,
                "lastsessionendtime"    => 1,
                "multimerchantid"       => 1,
                "log_user_id"           => 1
              );

    #
    # Produce the newly copied terminal id sequence
    #
    $sql = "select nextval(''terminal_terminal_id_seq'') from
terminal;";
    my ($cp_terminal_id) = $pg_dbh->selectrow_array($sql);
    if (!defined($cp_terminal_id) or $pg_dbh->err)
    {
        elog ERROR, "$fn:$sql:failed:$pg_dbh->errstr";
        return undef;
    }
    elog INFO, "$fn:$sql:success";

    $sql  = "insert into terminal(";
    $vals = "values(";
    my $key;
    my $comma = 0;
    foreach $key(keys %$data_ref)
    {
        # Copy all values unless one of the fields we should not copy
(unique, etc)
        if ($nocopy{$key} != 1)
        {
            if ($comma) {
                $sql    .= ",";
                $vals   .= ",";
            } else {
                $comma   = 1;
            }

            $sql  .= "$key";
            $vals .= defined($data_ref->{$key}) ?
"''$data_ref->{$key}''" : "NULL";
        }
    }
    # Add the log user, performing this operation and the generated
terminal id
$sql .= ",terminal_id";
    $vals.= ",''$cp_terminal_id''";
    $sql .= ",log_user_id)";
    $vals.= ",''$log_user_id'');";
    $sql .= "$vals";

    my $rv = $pg_dbh->do($sql);
    if (!defined($rv) or $rv != 1 or $pg_dbh->err) {
        elog ERROR, "$fn:$sql:execute failed". $pg_dbh->errstr;
        return undef;
    }
    elog INFO, "$fn:$sql:success:$cp_terminal_id";

    ########################################################
    #                                                      #
    #               COPY TERMAPPL TABLE                    #
    #                                                      #
    ########################################################

    #
    # Copy the terminal applications (there could be multiple)
    #

    $sql = "select * from termappl where terminal_id = $terminal_id;";
    $data_ref = $pg_dbh->selectall_hashref($sql, "termappl_id");
    if ($data_ref == undef or $pg_dbh->err)
    {
        # failed to execute the statement
        elog ERROR, "$fn:$sql:failed:".$pg_dbh->errstr;
        return undef;
    }
    elog INFO, "$fn:$sql:success";

    # copy all fields except the following (
    %nocopy = ();
    %nocopy = (
                "termappl_id"   => 1,
                "terminal_id"   => 1,
                "log_user_id"   => 1
              );
    # loop through all the rows found (keyed by id)
    my $row;
    foreach $row (keys %$data_ref)
$comma = 0;
        $sql   = "insert into termappl(";
        $vals  = "values(";

        # loop through all the fields - except the ones should not be
copied
        foreach $key (keys %{$data_ref->{$row}})
        {
            if ($nocopy{$key} != 1)
            {
                if ($comma)
                {
                    $sql  .= ",";
                    $vals .= ",";
                } else
                {
                    $comma = 1;
                }
                $sql  .= "$key";
                $vals .= defined($data_ref->{$row}->{$key}) ?
"''$data_ref->{$row}->{$key}''" : "NULL";
            }
        }
        # Add the log user, performing this operation and the copied
terminal id
        $sql .= ",terminal_id";
        $vals.= ",''$cp_terminal_id''";
        $sql .= ",log_user_id)";
        $vals.= ",''$log_user_id'');";
        $sql .= "$vals";

        #inser the application
        $rv = $pg_dbh->do($sql);
        if (!defined($rv) or $pg_dbh->err or $rv != 1)
        {
            # failed to add the terminal application
            elog ERROR, "$fn:$sql:failed".$pg_dbh->errstr;
            return undef;
        }
        elog INFO, "$fn:$sql:success";
    }

    #
    # Return the newly copied terminal id
    #
    return $cp_terminal_id;

 ' LANGUAGE plperlu;

-------------------------------------------------------------------------
*** The output of -d 2 at crash
% tail -20 /var/log/pgsql

 fn_copy_terminal:insert into

terminal(tradingsuburb,timeoutuserentry,versionnumber,efbmaxtransactions,efbonlinerecheckidletime,hotkey3,ecrinterface,terminalmodel,hotkey2,tradingpostcode,tradingaddress,tradingaddress2,lanaddress,currentversionnumber,hotkey1,timeouttrainingoff,connectiontype,efbmaxrefundamount,efbmaxpurchaseamount,timeoutstandby,tradingstate,lastsessionstatus,log_terminal_id,offlinereentrymode,blinddial,sendmes

age,stationaryorder,terminalmanufacturer,serialnumber,timeouterrorscreen,efbonlinerechecknooftransactions,dialprefix,terminal_id,log_user_id)values('Chatswood','45',NULL,'100','5','0','0',NULL,'0','2060','43
Help
St','','0',NULL,'0','5',NULL,'0','0','99','1','0',NULL,'0','0','0','0',NULL,NULL,'5','20',NULL,'3607','100');:success:3607
LOG:  statement: select * from termappl where terminal_id = 52;
DEBUG:  child process (PID 4339) was terminated by signal 11
LOG:  server process (PID 4339) was terminated by signal 11
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2004-10-26 16:11:07 EST
LOG:  checkpoint record is at 0/22769544
LOG:  redo record is at 0/22769544; undo record is at 0/0; shutdown
FALSE
LOG:  next transaction ID: 100790; next OID: 789423
LOG:  database system was not properly shut down; automatic recovery
in progress
LOG:  redo starts at 0/22769584
LOG:  unexpected pageaddr 0/1F77A000 in log file 0, segment 34, offset
7839744
LOG:  redo done at 0/22777954
LOG:  database system is ready
DEBUG:  child process (PID 4365) exited with exit code 0
DEBUG:  child process (PID 4395) exited with exit code 0
DEBUG:  child process (PID 4422) exited with exit code 0
DEBUG:  child process (PID 4472) exited with exit code 0
DEBUG:  child process (PID 4501) exited with exit code 0
DEBUG:  child process (PID 4538) exited with exit code 0
---------------------------------------------------------------