DBD::Pg::st execute failed: Cannot execute until previous async query has finished

[email protected] (Alexander Farber)
Newsgroups perl.dbd.pg
Message-ID <[email protected]>
Hello,

I hope this is correct mail address - I've found it at
http://svn.perl.org/modules/DBD-Pg/trunk/README and
couldn't find any other mailing list for DBD::Pg users.

I'm using CentOS 5.5 Linux (same as Redhat 5.5)
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 and friends.

I have prepared a simple test case demonstrating
my problem - it is listed at the bottom.

My code works ok, when I remove  {pg_async => PG_ASYNC}.

My background is that I have a small Facebook game
running as a non-forking Unix daemon with IO::Poll.
I would like to add some statistics for the players,
but I don't want to throttle my poll-loop, so I would
like to send mostly INSERT/UPDATE commands
asynchronously and I don't need any return values
from the database - because for reading/displaying
statistics I will have separate web-scripts.

I get the error message
DBD::Pg::st execute failed: Cannot execute until previous async query
has finished
even though I'm not using PG_OLDQUERY_WAIT.

Here is my code (I know, that I don't need to create
tables every time I start my program, but I've decided
to do it anyway. I'm trying to make my daemon reconnect
to PostgreSQL whenever connection is lost,
that is why I use *_cached methods and eval { .... } ).

#!/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;
};

# this runs once at daemon startup
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});
};

# this is supposed to run inside the poll-loop
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});

        my $sth = $dbh->prepare_cached(
            q{select pref_update_users(?, ?, ?, ?, ?, ?, NULL)},
{pg_async => PG_ASYNC});

        $sth->execute('ID123', 'Alexander', 'Farber', undef, undef, undef);
};


Thank you!
Alex
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.