view release on metacpan or  search on metacpan

KB.pm  view on Meta::CPAN

=item B<pg_ready>

This method can be called as a database handle method or (for convenience) as a statement handle method. Both simply 
see if a previously issued asynchronous query has completed yet. It returns true if the statement has finished, in which 
case you should then call the L</pg_result> method. Calls to C<pg_ready()> should only be used when you have other 
things to do while the query is running. If you simply want to wait until the query is done, do not call pg_ready()
over and over, but simply call the pg_result() method.

  my $time = 0;
  while (!$dbh->pg_ready) {
    print "Query is still running. Seconds: $time\n";
    $time++;
    sleep 1;
  }
  $result = $dbh->pg_result;

=item B<pg_result>

This database handle method returns the results of a previously issued asynchronous query. If the query is still 
running, this method will wait until it has finished. The result returned is the number of rows: the same thing 
that would have been returned by the asynchronous L</do> or L</execute> if it had been called without an asynchronous flag.

  $result = $dbh->pg_result;

=back

=head3 Asynchronous Examples

Here are some working examples of asynchronous queries. Note that we'll use the B<pg_sleep> function to emulate a 
long-running query.

  use strict;
  use warnings;
  use Time::HiRes 'sleep';
  use DBD::KB ':async';

  my $dbh = DBI->connect('dbi:KB:dbname=postgres', 'postgres', '', {AutoCommit=>0,RaiseError=>1});

  ## Kick off a long running query on the first database:
  my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});
  $sth->execute(5);

  ## While that is running, do some other things
  print "Your query is processing. Thanks for waiting\n";
  check_on_the_kids(); ## Expensive sub, takes at least three seconds.

  while (!$dbh->pg_ready) {
    check_on_the_kids();
    ## If the above function returns quickly for some reason, we add a small sleep
    sleep 0.1;
  }

  print "The query has finished. Gathering results\n";
  my $result = $sth->pg_result;
  print "Result: $result\n";
  my $info = $sth->fetchall_arrayref();

Without asynchronous queries, the above script would take about 8 seconds to run: five seconds waiting 
for the execute to finish, then three for the check_on_the_kids() function to return. With asynchronous 
queries, the script takes about 6 seconds to run, and gets in two iterations of check_on_the_kids in 
the process.

Here's an example showing the ability to cancel a long-running query. Imagine two slave databases in 
different geographic locations over a slow network. You need information as quickly as possible, so 
you query both at once. When you get an answer, you tell the other one to stop working on your query, 
as you don't need it anymore.

  use strict;
  use warnings;
  use Time::HiRes 'sleep';
  use DBD::KB ':async';

  my $dbhslave1 = DBI->connect('dbi:KB:dbname=postgres;host=slave1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
  my $dbhslave2 = DBI->connect('dbi:KB:dbname=postgres;host=slave2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});

  $SQL = "SELECT count(*) FROM largetable WHERE flavor='blueberry'";

  my $sth1 = $dbhslave1->prepare($SQL, {pg_async => PG_ASYNC});
  my $sth2 = $dbhslave2->prepare($SQL, {pg_async => PG_ASYNC});

  $sth1->execute();
  $sth2->execute();

  my $winner;
  while (!defined $winner) {
    if ($sth1->pg_ready) {
      $winner = 1;
    }
    elsif ($sth2->pg_ready) {
      $winner = 2;
    }
    Time::HiRes::sleep 0.05;
  }

  my $count;
  if ($winner == 1) {
    $sth2->pg_cancel();
    $sth1->pg_result();
    $count = $sth1->fetchall_arrayref()->[0][0];
  }
  else {
    $sth1->pg_cancel();
    $sth2->pg_result();
    $count = $sth2->fetchall_arrayref()->[0][0];
  }

=head2 Array support

DBD::KB allows arrays (as arrayrefs) to be passed in to both 
the L</quote> and the L</execute> methods. In both cases, the array is 
flattened into a string representing a Postgres array.

When fetching rows from a table that contains a column with an 
array type, the result will be passed back to your script as an arrayref.

To turn off the automatic parsing of returned arrays into arrayrefs, 
you can set the attribute L<pg_expand_array|/pg_expand_array (boolean)>, which is true by default.

  $dbh->{pg_expand_array} = 0;