Asynchrony in DBD :: Pg

We are all used to working with the base in the style:

1. complete request
2. wait for an answer
3. continue execution

But while the long query is running, we can do something useful in the application. Do not idle the processor time.



For PostgreSQL in DBD :: Pg there is some semblance of asynchrony. And sometimes it does help us.



You can continue to run the application without waiting for the request. This is enabled by the pg_async parameter to the prepare-request:

')

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});



##

my $sth = $dbh->prepare("SELECT pg_sleep(?)", {pg_async => PG_ASYNC});

$sth->execute(5);



## , -

print "Your query is processing. Thanks for waiting\n";

check_on_the_kids();



while (!$dbh->pg_ready) {

check_on_the_kids();

## -

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();





For pg_async there are three constants:

• PG_ASYNC - request execution in asynchronous mode
• PG_OLDQUERY_CANCEL - if the previous request worked at that moment, it is canceled
• PG_OLDQUERY_WAIT - we block to wait for the previous request and only then we start to perform a new one.

There are also three auxiliary methods -

• pg_cancel - cancel the request. One more connection actually opens, in which SELECT is sent pg_cancel_backend (?);
• pg_ready - returns true if the request has been executed.
• pg_result - is blocked until the execution of the query, and then returns the same as -> execute in standard mode.

Of the minuses - you can not set the callback at the time of execution of the request. Need to constantly check.

Also at a time in one connection, you can perform only one request. But what prevents to open a dozen connections and in turn to send requests to them?)



I will try to talk about the applications of this technology:

• heavy queries + complex logic. Evenly load your server and database server.

   while () {
   my $ foo = compute_foo (); # heavy function
   # block until the previous request is executed
   $ dbh-> do ('UPDATE stats SET foo =?', {pg_async => PG_ASYNC + PG_OLDQUERY_WAIT}, $ foo);
   }
  

• work with several database servers

   $ first_dbh-> do ('DELETE FROM old_data', {pg_async => PG_ASYNC});
   $ second_dbh-> do ('UPDATE new_data SET status = 0', {pg_async => PG_ASYNC}) 

• timeouts you can kill the request by timeout if it has not yet completed
• competitive inquiries. we send the same request to two servers and give data from the server that responded faster.
• requests, the result of which you are absolutely indifferent