I have a number of Perl programs of similar form to this:
$dbh=DBI->connect("dbi:Pg:dbname=$dbname;host=${dbserver};", $dbuser, $dbpasswd,
{PrintError => 0, PrintWarn => 0, AutoCommit => $autocommit}) or
errexit( "Unable to connect to dbname $dbname, err: $DBI::errstr");
errexit("No db handle") unless ($dbh);
#update statement definition here
my $update_info_sth=$dbh->prepare($stmt) or errexit("Cannot prepare handle for
$stmt; ", $DBI::errstr);
#stmt=select statement definition here; selects some data ordered by date,
limit n, where n is about 300 or so, depending on the exact program
my $select_info_sth=$dbh->prepare($stmt) or errexit("Cannot prepare handle for
$stmt; ", $DBI::errstr);
trace_output("after prepare of select stmt");
$select_info_sth->execute() or errexit("Cannot execute select_info_sth;
",$select_info_sth->errstr);
trace_output("after execute of select stmt");
my (%info, @data);
trace_output("fetching domain info");
while (@data = $select_info_sth->fetchrow_array) {
foreach (@data) { $_='' unless defined}
next if ($data[0] eq '');
$info{$data[0]}=$data[1];
$update_sth->execute($data[0]) or errexit("Cannot update table processing
column for id $data[0]; ",$update_sth->errstr);
trace_output("processing set true for id $data[0], dom: $data[1]");
}
##check for problems with premature termination
errexit("Error in fetching:", $select_info_sth->errstr) if
$select_info_sth->err;
#not really an error, just nothing to process:
if ((scalar keys %info) == 0) {
trace_output("No ids returned");
$dbh->disconnect;
exit 0;
}
The trace_output and errexit subroutines are standard logging-type things.
After the SELECT runs, the program should take the ids returned, and process
each, doing whatever it is supposed to do. The SELECT, in this case, is
ordering data by a date, so that we are processing the oldest data. Therefore,
data should always be returned.
This is a pg cluster installation, using version 8.3.5.
Many instances of these programs run all day long, some on a regular Debian
Lenny server, others through exec hosts in a Sun Grid. Most of the time, data
is returned, and the program proceeds along its way, no problem.
Periodically (I see no pattern to the times), the program will exit with the
"No ids returned" message in the log. No errors or anything are in the
database log, that I can find. I have seen in the log processes connecting and
running the main SELECT at apparently the appropriate time, then a "rollback"
(presumably due to the disconnect), and disconnect.
I don't really understand why the query returns nothing periodically, then
works fine again seconds later. The database server is quite busy, doing
thousands of queries all the time.
Any explanations or ideas? The processing works, because other iterations of
the program are constantly running, so the next attempt returns data, and runs
as normal. However, it bugs me that sometimes a query that should work is
returning no results, for no discernable reason.
Thanks,
Susan
