Re: async queries in Perl and poll()/select() loop - how to make them work together? - Mailing list pgsql-general

On 11/1/2010 4:29 AM, Alexander Farber wrote:
> Hello,
>
> I'm using CentOS 5.5 Linux with stock perl v5.8.8
> and have installed DBD-Pg-2.17.1 via CPAN shell
> and I'm using postgresql-server-8.4.5-1PGDG.rhel5.
>
> I've prepared a simple test case demonstrating
> my problem - it is listed at the bottom. It works ok,
> when I remove {pg_async =>  PG_ASYNC}.
>
> I have a small multiplayer game, a non-forking daemon
> reading/writing to sockets and running in a IO::Poll loop.
>
> I'd like to add player statistics to it, so I was hoping
> that I could call the simple INSERT/UPDATE
> statements asynchronously from the loop and
> I don't need to fetch any results of those queries,
> because displaying statistics will be done by
> web scripts, not by my game daemon.
>
> I.e. I would like to "fire and forget" queries.
>
> But unfortunately I get the error:
> DBD::Pg::st execute failed: Cannot execute
> until previous async query has finished
> even though I'm not using PG_OLDQUERY_WAIT
>
> Does anybody know what is wrong and
> how would you use async queries with
> poll()/select() loops anyway?
>
> I can't even call "$dbh->pg_result if $sth->pg_ready",
> on every loop iteration, because I can have
> several queries running at that moment,
> I don't want to iterate through a list of my $sth's...
> This defeats my target of quick poll()-looping.
>
> Regards
> Alex
>
> P.S. Here is my test case and it is also listed at
>
http://stackoverflow.com/questions/4065758/async-queries-with-dbdpg-fail-with-cannot-execute-until-previous-async-query-h
>
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
> use DBD::Pg qw(:async);
>
> use constant DBNAME =>  'snake';
> use constant DBUSER =>  'snake';
> use constant DBPASS =>  'snake';
>
> use constant SQL_CREATE_TABLES =>  q{
>          /*
>          create table pref_users (
>                  id varchar(32) primary key,
>                  first_name varchar(32),
>                  last_name varchar(32),
>                  female boolean,
>                  avatar varchar(128),
>                  city varchar(32),
>                  lat real check (-90<= lat and lat<= 90),
>                  lng real check (-90<= lng and lng<= 90),
>                  last_login timestamp default current_timestamp,
>                  last_ip inet,
>                  medals smallint check (medals>  0)
>          );
>
>          create table pref_rate (
>                  obj varchar(32) references pref_users(id),
>                  subj varchar(32) references pref_users(id),
>                  good boolean,
>                  fair boolean,
>                  nice boolean,
>                  about varchar(256),
>                  last_rated timestamp default current_timestamp
>          );
>
>          create table pref_money (
>                  id varchar(32) references pref_users,
>                  yw char(7) default to_char(current_timestamp, 'YYYY-WW'),
>                  money real
>          );
>          create index pref_money_yw_index on pref_money(yw);
>
>          create table pref_pass (
>                  id varchar(32) references pref_users
>          );
>
>          create table pref_misere (
>                  id varchar(32) references pref_users
>          );
>          */
>
>          create or replace function pref_update_users(_id varchar,
>              _first_name varchar, _last_name varchar, _female boolean,
>              _avatar varchar, _city varchar, _last_ip inet) returns
> void as $BODY$
>                  begin
>
>                  update pref_users set
>                      first_name = _first_name,
>                      last_name = _last_name,
>                      female = _female,
>                      avatar = _avatar,
>                      city = _city,
>                      last_ip = _last_ip
>                  where id = _id;
>
>                  if not found then
>                          insert into pref_users(id, first_name,
>                              last_name, female, avatar, city, last_ip)
>                          values (_id, _first_name, _last_name,
>                              _female, _avatar, _city, _last_ip);
>                  end if;
>                  end;
>          $BODY$ language plpgsql;
> };
>
> eval {
>          my $dbh = DBI->connect_cached('dbi:Pg:dbname=' .
>              DBNAME, DBUSER, DBPASS, {
>              AutoCommit =>  1,
>              PrintWarn =>  1,
>              PrintError =>  1,
>              RaiseError =>  1,
>              FetchHashKeyName =>  'NAME_lc',
>              pg_enable_utf8 =>  1
>          }, {pg_async =>  PG_ASYNC});
>
>          $dbh->do(SQL_CREATE_TABLES, {pg_async =>  PG_ASYNC});
> };
> warn $@ if $@;
>
> for my $i (1..10) {
>          eval {
>                  my $dbh = DBI->connect_cached('dbi:Pg:dbname=' .
>                      DBNAME, DBUSER, DBPASS, {
>                      AutoCommit =>  1,
>                      PrintWarn =>  1,
>                      PrintError =>  1,
>                      RaiseError =>  1,
>                      FetchHashKeyName =>  'NAME_lc',
>                      pg_enable_utf8 =>  1
>                  }, {pg_async =>  PG_ASYNC});
>
>                  #$dbh->pg_result;
>
>                  my $sth = $dbh->prepare_cached(
>                      q{select pref_update_users(?, ?, ?, ?, ?, ?,
> NULL)}, {pg_async =>  PG_ASYNC});
>
>                  $sth->execute('ID123', 'Alexander', 'Farber', undef,
> undef, undef);
>          };
>          warn $@ if $@;
> }
>

I believe one database connection can have one async query going at a time.

I dont see anyplace in the docs that connect (or connect_cached)
supports PG_ASYNC.

Each iteration of your loop is blowing away the previous values, which
should cause problems.  I assume this is just test code?  Is your real
code really going to connection 10 times per person?  You wont be able
to support very many concurrent users that way.  The code above might
work if you switched it arrays (@dbh and @sth).

Async queries gives you the ability to fire one query, let the db work
on it while you do something else, and them come back to it.  You need
to think about your layout (cuz I'm betting your example code does not
reflect what you really want to do).

Even with async querys, you eventually have to call $dbh->pg_result, so
its not going to be fire and forget.  To really do fire and forget, and
totally take the stats processing away from game play processing, I'd
suggest an event queue (or rpc), like zeromq, PGQ or gearman.

-Andy