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

On 11/1/2010 11:58 AM, Alexander Farber wrote:
> Hello Andy and others,
>
> On Mon, Nov 1, 2010 at 3:33 PM, Andy Colson<andy@squeakycode.net>  wrote:
>> On 11/1/2010 4:29 AM, Alexander Farber wrote:
>>> I have a small multiplayer game, a non-forking daemon
>>> reading/writing to sockets and running in a IO::Poll loop.
>>>
>>> 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
>
>> I believe one database connection can have one async query going at a time.
>
> why are there 3 contants
> http://search.cpan.org/dist/DBD-Pg/Pg.pm#Asynchronous_Constants
> then? They suggest you can fire a query and forget
>
>> I dont see anyplace in the docs that connect (or connect_cached) supports
>> PG_ASYNC.
>
> True, I've removed it (the problem still persists).
>
>> 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).
>
> No I just need one connection, because I have
> 1 process (without any forked processes or threads),
> which loops in a poll() loop.
>
>> 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.
>
> Thanks I'll look at it or maybe I'll fork 1 more process,
> and open a pipe to it (then I can poll() it too).
>
> Regards
> Alex
>

Consider the Pg architecture:  On the server a postmaster runs,
listening for connections.  On the client, you connect to the server.
The postmaster will spin up a child process to handle the new
connection.  One postmaster child processes one client connection, and
it can only do one query at a time.

So:
Postmaster
  |
  |--> child 1
  |--> child 2

Each child runs one query at a time.  Your client program has two options:
1) fire off a query and wait for the response and collect it.
2) fire off a query, do something else for a bit, collect the response.


 > why are there 3 contants
 > http://search.cpan.org/dist/DBD-Pg/Pg.pm#Asynchronous_Constants
 > then? They suggest you can fire a query and forget

I'm not sure what you mean fire and forget.  To me, I'd say no because
you have to collect the results at some point via $dbh->pg_result.
(Even if you fire an update or insert, I think you still have to "finish
off the process" via $dbh->pg_result)

I dont think you can start a second query until you have called
$dbh->pg_result.  These constants just give you neat ways of waiting...
its still just one at a time.

Our definitions of fire and forget might be different, and thats ok, but
in your example code, it looked to me like you wanted to run 10
simultaneous queries asynchronously, and that cannot be done without 10
separate database connections.  One connection can only run one query at
a time.

You still have the option, however, of using async queries in your game,
for example:

code to calc stats...
start query to update db stats
code to process game play, etc
finish off the db stats query
final bit of game code and respond to player... etc

-Andy