DBI, fork and IPC-Shareable - having problems

[Mr. Sunray]

Hi all,

Perl 5.6 on bughat 6.2, using DBI 1.14, DBD-Oracle 1.06.

Ok - I thought I'd try and optimize a series of sql SELECT statements by

forking each one and storing the results in a shared memory object.

Here's the code - I'm reading a series of sql select statements from a
file, fork'ing a child for each one, executing the 'fetch' within the
child and trying to store the results in an array for later retrieval.

The problem seems to be with the shared memory objects.  Either the
array of results grows each time I run the program or else I get "Can't
use an undefined value as an ARRAY reference at
/usr/local/lib/perl5/site_perl/5.6.0/IPC/Shareable.pm line 446, <GEN1>
chunk whatever".

Now, I've been told that part of the problem (at least) is that I'm
trying
to store references, which probably won't work (though it seems to
work sometimes).

I guess my big question is how can I use fork to speed up my queries
*and*
store the results in the parent (without using a text file)?  Should I
be
looking at threads instead?  Is there a *standard idiom*?

Thanks in advance for any help.

WARNING - be prepared to use 'ipcs' and 'ipcrm' if you want to play with

this script!  You may have stray memory objects floating around
afterwards.

Be sure to run this/your program at least twice to make sure your shared

memory objects aren't "collecting".

#!/usr/local/bin/perl -w

use strict;
use DBI;
use DBD::Oracle;
use IPC::Shareable;
use IO::File;
use POSIX qw(WNOHANG);

use constant DB => 'myDB';
use constant USER => 'user';
use constant PASSWD => 'passwd';
use constant DRIVER => 'Oracle';

#+++++++++++++++++++++++++++++++++++++++++++++
# File contains 4 sql SELECT statements.
# Use "select sysdate from dual" if you like, so long as it works.
#+++++++++++++++++++++++++++++++++++++++++++++
use constant SQLFILE => 'file.sql';

# Child handler
$SIG{CHLD} = \&reapChild;

my($dbh, $sth, @results, @allRecords);
my $if = IO::File->new(SQLFILE) or die "Couldn't open file: $!\n";

#+++++++++++++++++++++++++++++++++++++++++++++++++
# I am not entirely sure what the proper settings for these should be.
# I've tried various combinations with no (or bad) luck.
#+++++++++++++++++++++++++++++++++++++++++++++++++
my $glue = 'data';
my %options = (create => 'yes', exclusive => 0, destroy=>1)
tie @allRecords, 'IPC::Shareable', $glue, { %options } or die "Tie
failed: $!\n";

$/ = "";  # We're going to read each 'select' statement as an input
record
while(my $query = <$if>){
   chomp($query);

   my $child = fork();
   die "Can't fork: $!\n" unless defined $child;

   if($child == 0){

        # Not sure if I need 'InactiveDestroy' here or not
        $dbh = DBI->connect(DB,USER,PASSWD,DRIVER,
            {RaiseError=>1,PrintError=>1,InactiveDestroy=>1}
        );

        $sth = $dbh->prepare($query);
        $sth->execute();
        while( my $hash_ref = $sth->fetchrow_hashref() ){
            push(@results, $hash_ref);
        }

        #+++++++++++++++++++++++++++++++++++++++++++++
        # '@allRecords' should contain a reference to an array of hash
        # reference for each select statement (thus, four total).
        #+++++++++++++++++++++++++++++++++++++++++++++
        push(@allRecords, \@results);

        $sth->finish();
        $dbh->disconnect();
        exit 0;
    }
}

close($if) or die "Unable to close input file: $!\n";

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# I expect to see four array references here.  When I try this, I either

# get "Can't use undefined value as an array reference" warnings
# from the 'Shareable.pm' module or the list keeps growing and growing
# each time I run the program.  What do I need to do?
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
foreach my $ref(@allRecords){
    print "Ref is: $ref\n";
}

sub reapChild{ do {} while waitpid(-1, WNOHANG) > 0 }