FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbd-pg @
categoriesperl
postedOct 31, '10 at 10:44p
activeOct 31, '10 at 10:44p
posts1
users1
websiteperl.org

1 user in discussion

Alexander Farber: 1 post

People

Translate

site design / logo © 2022 Grokbase