Thread: weird empty return from select problem; periodically get no data returned - could it be a network issue?

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

 

 

 

 

 

 

On 9/07/2010 2:58 AM, Susan Cassidy wrote:

> This is a pg cluster installation, using version 8.3.5.

"Pg cluster"?

There are quite a few different clustering setups for Pg.

Do you mean PgCluster from http://pgfoundry.org/projects/pgcluster/ ? If
so, which version and how is it set up?

Or some other Pg-based cluster using Bucardo, Slony-II, etc?


As for your issue: have you excluded the possibility that there is no
data to return? Issues sometimes arise where the data you're expecting
to retrieve hasn't been committed by another transaction yet, so it's in
the database but not yet visible. As you haven't provided your queries
or schema it's hard to know what's going on there.

--
Craig Ringer

Please reply to the list, not just to me. "reply all" or (in smarter
mail clients) "reply to list" will do the trick.

I've cc'd the list.

On 10/07/10 00:15, Susan Cassidy wrote:
> I didn't set up the cluster (just started working here a few months ago), so I don't know for sure.  A comment in one
ofthe scripts in the bin directory for the pg_* cluster commands says something about the postgresql-common package.
Thisis Debian 4.0 (etch).  dpkg -l has a line with: 
> postgresql-common        91       PostgreSQL database-cluster manager

Oh, you're probably not using clustering at all, then, just being thrown
by the terminology.

PostgreSQL uses the term "cluster" to refer to a group of databases
managed by a postmaster. The chosen terminology is becoming increasingly
confusing as real clustering increases in prevalence.

It'd still be helpful to confirm that it's just a vanilla install of
PostgreSQL from debian packages on etch, as it sounds like.

As for the empty row sets ... in your position, I'd be increasing my
tracing/logging levels both on the database backend and in the dbi
driver, then trying to match up empty row return incidents with those
trace logs. When you can't reproduce a problem on demand tracing is
often the only option unless you can figure it out with the information
at hand.

I'd also want to verify that my indexes were in good condition, as a
damaged index can cause all sorts of wacky results. They shouldn't
happen, but in reality have been known to whether due to
hardware/filesystem issues or the occasional PostgreSQL bug. The easiest
way to make sure your indexes are good - if you can schedule some
downtime - is to REINDEX. If you can't, there are other alternatives.

--
Craig Ringer

    Susan Cassidy wrote:

> 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.

In a producer-consumer model, at some point in time there should be between 0
and N items to consume. 0 item is no more weird than 1, or 2, or any other
particular value.
Personally, I would find it weird if a consumer process never had nothing to
do. I would see this as a hint that the producer is over-producing.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org