|
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 = "" {
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 |
