DBD::PgSPI crashes the database server (SIGSEGV)?

From: Zhamak Dehghani (zhamak_at_foxid.com)
Date: 10/26/04

  • Next message: Tassilo v. Parseval: "Re: Namespace and comments on usefulness sought" 
    Date: 26 Oct 2004 00:08:19 -0700

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

  • Next message: Tassilo v. Parseval: "Re: Namespace and comments on usefulness sought"

    Relevant Pages

    • Re: webservice performance data missing
      ... If this is a Win2003 server, how come you don't have the reg.exe utility? ... >>> Networking for Costly counters failed ... >>> is terminating ... >>> compliant or invalid Linkage Key ...
      (microsoft.public.win32.programmer.wmi)
    • Re: Identifying the Closed Client..,
      ... Depending upon the situation, it may close the connection with a FIN packet(which will be received by the server as a call to recv() that succesfully receives zero bytes) or a RST packet, which will cause recv to fail with WSAECONNRESET. ... I contend that it ought to close with a RST in the event of an application terminating through an unhandled exception. ... I would assume that this would also happen if the client operating system crashed while the connection was open, but, I've never tested this. ...
      (microsoft.public.win32.programmer.networks)
    • Re: AAAAAAHHHHHH This problem is killing me!!!
      ... > The program .exe is located on a database server and they connect via UNC. ... > How do I end the session on the database server whenever the connection is lost on the Terminal server? ... terminating the session rather than letting it remain open on the server. ... So if the settings are already as Arno suggests you set them, ...
      (microsoft.public.win2000.termserv.apps)
    • Re: remote desktop sessions not terminating?
      ... If the server is as new as ... > MS support told me the delay in userinit terminating is caused by 2003 ... > policy setting they told us to change in order to stop the autoenroll is ... > the policy is missing b/c something went wrong with the SP1 install and we ...
      (microsoft.public.windows.terminal_services)
    • os.system(), HTTPServer, and finishing HTTP requests
      ... be waiting for more input until the child that is forked in the server ... allowing the original parent to return and leaving an orphaned child behind ... pid = os.fork ... I'm exiting. ...
      (comp.lang.python)