Re: Create Action for psql when NOTIFY Recieved

On 29 August 2017 at 08:42, Jerry Regan
<jerry(dot)regan(at)concertoglobalresources(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry(dot)regan(at)concertoglobalresources(dot)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(at)stuartbishop(dot)net>
http://www.stuartbishop.net/