Re: [GENERAL] Create Action for psql when NOTIFY Recieved

My principal problem with psql(1) relative to NOTIFY/LISTEN is that
psql(1) won't check for them until it has had some input on stdin.  So
it will appear to do nothing when it's idle, even if there millions of
notifies for it to respond to!

So I wrote a program to just LISTEN: https://github.com/twosigma/postgresql-contrib/blob/master/pqasyncnotifier.c

With that you will get a line of output per-notification (unless you
request printing the payload and the payload has embedded newlines, so
watch out!).  You can then use this to drive actions in a script.  For
example:

#!/bin/bash

if (($# != 1)); then
    printf 'Usage: %s POSTGRESQL-URI\n' "${0##*/}"
    exit 1
fi

pqasyncnotifier "$1" notify_channel1 channel2 | while read junk; do
    printf 'SELECT do_thing();\n'
done | psql -f - "$1"

You can listen on one or more channels, print the channel name, PID,
timestamp (local to the pqasyncnotifier), and/or NOTIFICATION payload:

#!/bin/bash

if (($# < 2)); then
    printf 'Usage: %s POSTGRESQL-URI CHANNEL [CHANNEL ...]\n' "${0##*/}"
    exit 1
fi

pqasyncnotifier -c "$@" | while read junk channel; do
    printf 'SELECT do_thing(%s);\n' "$channel"
done | psql -f - "$1"

Be _very_ careful about using the NOTIFY payload (option -d) though:
it's completely unconstrained in form and contents, and anyone can
NOTIFY on any channel as there are no access controls on channels (you
don't even have to create them, and there's no CREATE for them anyways).

The right thing to do is to not bother with the payload at all -- just
the mere fact that a NOTIFY was done on some channel should be all
that's required for any processes LISTENing on that channel.

I might modify pqasyncnotifier to either truncate payloads at newlines,
or escape/remove newlines so that it could be safer to use the payloads.

I would like to see PostgreSQL adopt this program!

Nico
--