Hi Greg,
Thanks for your reply.
> I'm not sure I follow here: how would this possibly work if not through
> the asynchronous interface? If you are not using async, then
$sth->execute()
> will not return control until the query is done. Being able to cancel the
> query is one of the two main advantages of async queries (the other being
the
> fact that you can continue to do other things once you kick off the
execute).
I shouldn't have said "...we don't need asynchronous code...", as that was
confusing. I meant to say that we wouldn't need to use pg_async if
if $sth->cancel() was implemented. To be more specific, we are doing
something very close to what the DBI docs show in their "Signal Handling
and Canceling Operations" section. Here is our abridged code that works
on DBD::Oracle:
my $sth = $dbh->prepare('SELECT id FROM user_sessions FOR UPDATE');
my $aborted = 0;
my $action = POSIX::SigAction->new(
sub {$sth->cancel; $aborted = 1},
POSIX::SigSet->new(SIGALRM),
);
my $oldaction = POSIX::SigAction->new();
POSIX::sigaction(SIGALRM,$action,$oldaction);
alarm(30); # wait 30 seconds before timing out
$sth->execute;
alarm(0); # cancel alarm (if execute worked fast)
POSIX::sigaction(SIGALRM,$oldaction); # restore original SIG handler
die "Timed out waiting for locked session" if $aborted;
This is attractive to us because it requires no forking and
POSIX::SigAction is pretty safe.
> First thing I would try is solving the problem in some other fashion: a
> 30 second timeout is a very rough tool. Good venues for describing the
> problem in more details is pgsql-gene...@postgresql.org and #postgresql
> on freenode. Second thing would be to figure out why pg_cancel is not
> working correctly for you. It should in theory have no destabilizing
effects;
> if it does, I'd like to know about it so it can be fixed. :)
Again, I think $sth->cancel() would be the cleanest solution. I'll see
what I can do about narrowing down the destabilizing affect that pg_async
had
in our test suite, although narrowing that down will take quite some time
(because I don't want to just shove a ton of code at you).
--
Eric Simon
The IQ Group, Inc.
