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

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry.regan@concertoglobalresources.com> wrote:
>> My concern is how, after LISTENing in psql, I can tell it what to do when
>> the NOTItFY is received.

> ​As far as I am aware you cannot.

Yes, and psql is not designed to do anything of its own accord,
so I think the answer is really "use another program".

> ​"​Whenever a command is executed, psql also polls for asynchronous
> notification events generated by LISTEN and NOTIFY."

Exactly.  If you don't feed it a command, it just sits there.

> I suspect the feature request would be something like:
> \set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent
> meta-command)
> And psql would invoke said program and pass the content of the notification
> payload to it via stdin.

Such a program could only execute after the next time you give a command
to psql.  You could maybe imagine feeding it a continuous stream of dummy
commands, but that's pretty silly (and rather defeats the point of LISTEN,
which is to *not* eat cycles while waiting).

This isn't something that could be easily fixed, AFAICS.  Even if we
wanted to make psql pay attention to asynchronous data arrival, how
would we get control back from libreadline?  And what would happen
if the user had typed a partial line of input?

You really are much better off creating a program that opens its own
connection to the DB and sits there listening.  psql cannot help you
meaningfully with this request, and I can't see a way to make it do
so that wouldn't be a monstrous kluge.

            regards, tom lane

On 29 August 2017 at 08:42, Jerry Regan
<jerry.regan@concertoglobalresources.com> wrote:
> Tom,
>
> After a few minutes thought…..
>
> /s/jr
> Consultant
> Concerto GR
> Mobile: 612.208.6601
>
> Concerto - a composition for orchestra and a soloist
>
>
>
> On 28Aug, 2017, at 6:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry.regan@concertoglobalresources.com> wrote:
>
> My concern is how, after LISTENing in psql, I can tell it what to do when
> the NOTItFY is received.
>
>
> As far as I am aware you cannot.
>
>
> Yes, and psql is not designed to do anything of its own accord,
> so I think the answer is really "use another program”.
>
>
> psql would be running on *nix.
>
> Let’s suppose for a moment that I piped the output of a psql instance to awk
> or some similar program, configured to detect the NOTIFY. That program would
> then spawn a process to actually perform the work, parameters being whatever
> is part of the NOTIFY. Both this psql instance and the awk script would be
> dedicated to this task.
>
> Given this is not intended in any way to be production quality code - in
> fact, it’s intended to deliver XML to the client server for validation
> (xmllint) in a development/test environment - do you see anything that
> clearly won’t work?  Also, this would be a very low volume connection.
> Perhaps one NOTIFY in five minutes - or longer.
>
> Yes, it’s a hack.

Or crib some code from
http://initd.org/psycopg/docs/advanced.html#async-notify or
https://godoc.org/github.com/lib/pq/listen_example , which is probably
less effort than assembling this collection of hacks and trying to
make it reliable. Most PostgreSQL APIs have support for notifications.


--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

    Jerry Regan wrote:

> I think I could justify the effort to ‘script’ psql. I’m not so sure I can
> justify the effort to write a standalone program.

As a hack around psql, you could have a script that feeds psql
with "SELECT 1" from time to time and capture only the
notifications output:

 (echo "listen foo;"; while true; do echo "select 1;"; sleep 1; done) | \
    psql | grep notification

When another session issues NOTIFY foo,  'bar'
 that output filtered by the above command is, for example:

  Asynchronous notification "foo" with payload "bar" received from
  server process with PID 20033.

which just needs to be piped into another step that runs your custom
action.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

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
--

[I meant to send this to the list]

On Mon, Aug 28, 2017 at 07:08:28PM -0400, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> > jerry.regan@concertoglobalresources.com> wrote:
> >> My concern is how, after LISTENing in psql, I can tell it what to do when
> >> the NOTItFY is received.
>
> > ​As far as I am aware you cannot.
>
> Yes, and psql is not designed to do anything of its own accord,
> so I think the answer is really "use another program".
>
> > ​"​Whenever a command is executed, psql also polls for asynchronous
> > notification events generated by LISTEN and NOTIFY."
>
> Exactly.  If you don't feed it a command, it just sits there.
>
> > I suspect the feature request would be something like:
> > \set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent
> > meta-command)
> > And psql would invoke said program and pass the content of the notification
> > payload to it via stdin.
>
> Such a program could only execute after the next time you give a command
> to psql.  You could maybe imagine feeding it a continuous stream of dummy
> commands, but that's pretty silly (and rather defeats the point of LISTEN,
> which is to *not* eat cycles while waiting).

What would it take to have pqasyncnotifier [0] adopted by PostgreSQL?

pqasyncnotifier solves all the problems that psql has regarding
LISTENing for notifications.  Note too that pqasyncnotifier doesn't
poll, rather, it blocks in PQconsumeInput().

[0] https://github.com/twosigma/postgresql-contrib/blob/master/pqasyncnotifier.c

(Oy, I just noticed that the PQfinnish() call needs to move up to the end
of the for (;;) loop...)

Nico
--

On Sun, Sep 03, 2017 at 05:37:57PM -0500, Nico Williams wrote:
> What would it take to have pqasyncnotifier [0] adopted by PostgreSQL?

Maybe it should be named pqasynclisterner.

A \wait command for psql could do the same thing.  I could probably
write such a patch at some point if there's interest, something like:

  \wait [[N] [statement]]

that waits for N NOTIFYies (or forever if N is -1), perhaps always
printing the payload, but with newlines escaped (or truncated at
newlines) to avoid needing options, and runs a statement if provided.

Nico
--

> On Sep 3, 2017, at 3:32 PM, Nico Williams <nico@cryptonector.com> wrote:
>
>
> 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

Me too.

https://github.com/wttw/pgsidekick

Select-based, sends periodic keep-alives to keep the connection open, outputs payloads in a way that's friendly to pipe
intoxargs. (Also the bare bones of a notify-based scheduler). 

Cheers,
  Steve

> On Sep 4, 2017, at 10:25 PM, Nico Williams <nico@cryptonector.com> wrote:
>
> On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins <steve@blighty.com> wrote:
> >
>
> Me too.
>
> https://github.com/wttw/pgsidekick
>
> Select-based, sends periodic keep-alives to keep the connection open, outputs payloads in a way that's friendly to
pipeinto xargs. (Also the bare bones of a notify-based scheduler). 
>
> Without any kind of access controls on NOTIFY channels, nor any kind of payload validation, i just don't feel
comfortableusing the payload at all.  Besides, the payload is hardly necessary given that there's a database on which
youcan scribble the payload :)  It suffices that you receive a notification, and you can then check if there's anything
todo. 
>
> My version of this doesn't have connection keepalives, but that's ok because that can be added in the form of....
notifications,and the consumer of pqasyncnotifier can implement timeouts.  But i agree that timeouts and keepalives
wouldbe nice, and even invoking a given SQL function would be nice. 
>
> But the question i have is: how to get such functionality integrated into PostgreSQL?  Is a standalone program (plus
manpageplus Makefile changes) enough, or would a psql \wait command be better? 

There's not really any need to integrate it into postgresql at all. It doesn't rely on any details of the core
implementation- it's just a normal SQL client, a pretty trivial one. 

(Whether psql could usefully be reworked to listen for activity on the connection when it's not actively executing a
queryis another question). 

Cheers,
  Steve

On Tue, Sep 05, 2017 at 08:19:13AM -0700, Steve Atkins wrote:
> > On Sep 4, 2017, at 10:25 PM, Nico Williams <nico@cryptonector.com> wrote:
> > On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins <steve@blighty.com> wrote:
> > > https://github.com/wttw/pgsidekick

[BTW, I must say I like pgsidekick, but for the use of the payload bit.]

> > But the question i have is: how to get such functionality integrated
> > into PostgreSQL?  Is a standalone program (plus manpage plus
> > Makefile changes) enough, or would a psql \wait command be better?
>
> There's not really any need to integrate it into postgresql at all. It
> doesn't rely on any details of the core implementation - it's just a
> normal SQL client, a pretty trivial one.

It's a bit of an FAQ though, isn't it.  I do think it odd that PG has
this functionality on the server side and in the client-side API, but
its client-side utility functionality for it is very limited.

> (Whether psql could usefully be reworked to listen for activity on the
> connection when it's not actively executing a query is another
> question).

A \wait would simply wait for notifications from the server.  It would
be interruptible by ^C, but it would not listen for input on stdin.  I
think that should be a simple-enough patch to psql.

Nico
--