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 -- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
