view release on metacpan or  search on metacpan

Pg.pm  view on Meta::CPAN

  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;

=item B<pg_send_cancel>

Send a request to cancel a running asynchronous query to the
server. Returns true if this succeeded, false otherwise. The actual
outcome of the query still needs to be determined in the ordinary
way. If a running query was actually cancelled, C<pg_result> will
return zero and the C<state> method will return 57014.

=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::Pg ':async';

  my $dbh = DBI->connect('dbi:Pg: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 replica 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::Pg ':async';

  my $dbhrep1 = DBI->connect('dbi:Pg:dbname=postgres;host=replica1', 'postgres', '', {AutoCommit=>0,RaiseError=>1});
  my $dbhrep2 = DBI->connect('dbi:Pg:dbname=postgres;host=replica2', 'postgres', '', {AutoCommit=>0,RaiseError=>1});

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

  my $sth1 = $dbhrep1->prepare($SQL, {pg_async => PG_ASYNC});
  my $sth2 = $dbhrep2->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];
  }

=head3 Asynchronous Connect

Passing a true value for the attribute pg_async_connect to the DBI
connect method, e.g.

  $dbh = DBI->connect('dbi:Pg:...', $username, $password,
                      { pg_async_connect => 1 });

starts an asynchronous connect. The B<pg_continue_connect> method must
be used afterwards to complete the connection establishment process. If
the attribute is present but its value is false, an ordinary
synchronous connect will be done instead.

=head2 Array support