Get immediate notification of Postgres NOTIFY

Question

Is there any way with DBD::Pg to do a blocking wait for a NOTIFY that will immediately return when a message is ready?

I have a simple test script that can send messages using the Postgres 'NOTIFY' mechanism:

#!/usr/bin/perl

use 5.018;
use strict;
use warnings;
use autodie;

use DBI qw();

$| = 1;  # Flush buffer on print
my $dsn = 'dbi:Pg:dbname=test';
my $attr = {
    AutoCommit  => 0,
    RaiseError  => 1,
    PrintError  => 0,
};
my $topic = 'test_topic';

my $dbh = DBI->connect($dsn, '', '', $attr);

while (1) {
    print "payload: ";
    chomp(my $payload = <>);
    $dbh->do("NOTIFY $topic, '$payload'");
    $dbh->commit;
}

I also have a simple receiver script that uses LISTEN to subscribe to the messages:

#!/usr/bin/perl

use 5.018;
use strict;
use warnings;
use autodie;

use DBI qw();

$| = 1;  # Flush buffer on print
my $dsn = 'dbi:Pg:dbname=test';
my $attr = {
    AutoCommit  => 0,
    RaiseError  => 1,
    PrintError  => 0,
};
my $topic = 'test_topic';

my $dbh = DBI->connect($dsn, '', '', $attr);
$dbh->do("LISTEN $topic");

while (1) {
    $dbh->commit();
    while(my $notify = $dbh->pg_notifies) {
        my($topic, $pid, $payload) = @$notify;
        say "Got message: $topic => $payload";
    }
    sleep(10);
}

The problem is that $dbh->pg_notifies does not block, so if there are no notifications queued, it immediately returns undef. I've put the sleep(10) so that it's not a busy loop, but of course this means I get a delay of up to 10 seconds after a NOTIFY message is sent but before my LISTEN receives it.

Some searching suggested that at the libpq level, you could do a select on the socket to be notified immediately of an incoming NOTIFY, so I tried this:

my $sock_fd = $dbh->{pg_socket};
my $read_set = '';
vec($read_set, $sock_fd, 1) = 1;

while (1) {
    $dbh->commit();
    while(my $notify = $dbh->pg_notifies) {
        my($topic, $pid, $payload) = @$notify;
        say "Got message: $topic => $payload";
    }
    select($read_set, undef, undef, 10);
}

But it doesn't seem to work and the select only seems to return when my 10 second timeout expires.

It always seemed to me that NOTIFY/LISTEN provided a way to avoid polling loops, but I don't seem to be able to make it work without a polling loop. Suggestions?

Use could just lower your sleep. I've testet with `Time::HiRes::sleep(0.1);` and polling a hundred times (0.1*100 = 10 seconds) and a negligible amount of CPU was spent on the client side (and probably on server side as well), just `0.93% cpu` according to the bash `time` command prefix. I know this wasn't what you asked, but I thought you should know how little such polling costs. — Kjetil S., Sep 03 '18 at 12:51

score 0 · Answer 1 · answered May 05 '23 at 11:47

The problem is that $read_set gets ruined after probably the very first select() call. You should replace it to something like:

select(my $read_out = $read_set, undef, undef, 10);

After the first call you will probably not get a notification yet so you get back an empty fd set and next time you call select with the empty set, that's why you need to copy $read_set into a different variable.

Get immediate notification of Postgres NOTIFY

1 Answers1