Re: Listen and notify in psql process

On Thu, Jun 17, 2021 at 1:04 PM Sakshi Jain <sakshijain10388(at)gmail(dot)com>
wrote:

How to listen from within a psql process and get the payloads?
>
> Do Postgresql have any such a mechanism where in a session a process send
> a "listen <name>" sql command and then gets a message if someone in the
> other session issued a "notify <name>".
>
> Please provide an example of how to do this.
>
> I am looking for listen to return some thing when a notify has been issued.
>

Here is an example in perl. Basically you issue the LISTEN command. Then
you sit and watch the file descriptor to become ready. You do that outside
of a transaction. Once the descriptor is ready you call your driver's
version of notification read function until it comes back empty. That is
when you go back and wait for the next arrival.

$dbh->do("LISTEN channel");
my $sel = IO::Select->new;
$sel->add($dbh->{pg_socket});
while ($sel->can_read) {
while (my $notify = $dbh->pg_notifies) {
my ($name, $pid, $payload) = @$notify;
do_something($payload);
}
}

>