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






Reply via email to