Re: Create Action for psql when NOTIFY Recieved

[Jerry Regan <jerry.regan@xxxxxxxxxxxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:

On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
jerry.regan@xxxxxxxxxxxxxxxxxxxxxxxxxxx> 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.

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