Re: Create Action for psql when NOTIFY Recieved

On Mon, Aug 28, 2017 at 6:42 PM, Jerry Regan <
jerry(dot)regan(at)concertoglobalresources(dot)com> wrote:

> 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.
>
>
​I've been curious about having a long-running psql instance that could be
controlled by an external process (named pipes/fifos I believe). It seems
like you might actually have a chance to get that working if you, 1, intend
to perform the notification polling automatically​ and, 2, don't wish to
lose any notifications (i.e. you must keep the psql process that issues
LISTEN running continuously). It seems you'd actually need two of these
since you don't actually want the output to be sent to stdout or a normal
file but rather a file that is linked to the stdin of yet another long
running process.

I believe *nix provides sufficient tools but whether psql is written to a
sufficient level of compatibility to leverage them is something I don't
know and, last time I mentioned this though, got one other person in the
same boat (thoughtful but not worth the effort to investigate and R&D) and
no one speaking up to claim they've done it already.

Given your rough description I'm not seeing why you wouldn't just have the
trigger write a record out to some kind of journal table and poll that
table for new records whenever you wish instead of depending upon
LISTEN/NOTIFY.

David J.