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,versionnum ber,efbmaxtransactions,efbonlinerecheckidletime,ho tkey3,ecrinterface,terminalmodel,hotkey2,tradingpo stcode,tradingaddress,tradingaddress2,lanaddress,c urrentversionnumber,hotkey1,timeouttrainingoff,con nectiontype,efbmaxrefundamount,efbmaxpurchaseamoun t,timeoutstandby,tradingstate,lastsessionstatus,lo g_terminal_id,offlinereentrymode,blinddial,sendmes
age,stationaryorder,terminalmanufacturer,serialnum ber,timeouterrorscreen,efbonlinerechecknooftransac tions,dialprefix,terminal_id,log_user_id)values('C hatswood','45',NULL,'100','5','0','0',NULL,'0','20 60','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
---------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org