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

[GENERAL] Create Action for psql when NOTIFY Recieved

From
Jerry Regan
Date:
I have a 9.4 database. I think I’ve read all the LISTEN and NOTIFY entries in PostgreSQL documentation.

I’ve done internet searches, hopefully asking the correct question.

But I’m stumped.

I want psql to LISTEN for a NOTIFY that a trigger, yet to be defined, issues AFTER INSERT into the table. I think I know how to do all this.

My concern is how, after LISTENing in psql, I can tell it what to do when the NOTItFY is received.

In my internet searches, I found scripts to be used with Twisted. I don’t have Twisted, I don’t want to install Twisted. I just want psql to return a column from the table and hand it off to a process external to psql. I believe I know how to call external processes from psql.

My issue returns to, how do I tell psql what to do when it receives a NOTIFY? I don’t see anything in the docs for LISTEN except to specify the channel - nothing about what to do when the NOTIFY (with what ever parameters might be included) is detected.

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



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

From
"David G. Johnston"
Date:
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.  The docs for psql, and its feature set, with respect to LISTEN, are minimal and basically say psql will print out notifications to stdout (this I'm forced to assume or read the code) and will poll for notifications whenever it sends a query to the server.


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

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.

Given what we allow for "\copy" I don't see any technical or conceptual problems with such a feature.  Just needs someone to be its primary author.

David J.

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

From
Tom Lane
Date:
"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


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

From
Jerry Regan
Date:
David,

Thanks for your response!

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



On 28Aug, 2017, at 5:36 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

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.  The docs for psql, and its feature set, with respect to LISTEN, are minimal and basically say psql will print out notifications to stdout (this I'm forced to assume or read the code) and will poll for notifications whenever it sends a query to the server.

I’ve tested using LISTEN and NOTIFY in psql. First I issued the LISTEN, then when my prompt came back, issued the NOTIFY from psql. This is an example from the psql docs, I believe. In any case, that the NOTIFY had occurred was reported in the psql window.
​"​Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY."

I suspect the feature request would be something like:

\set NOTIFY_PROGRAM './process-notify-request.bash'  (or an equivalent meta-command)
Since the database would issue the NOTIFY, not sure \set would be used, but otherwise, yes. Or, I may just send information about the inserted row. It depends on what psql would need.

And psql would invoke said program and pass the content of the notification payload to it via stdin.

I’d rather have psql know the program name to invoke, but since I don’t really know how all this works, I may have no choice.

Given what we allow for "\copy" I don't see any technical or conceptual problems with such a feature.  Just needs someone to be its primary author.

David J.

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

From
Jerry Regan
Date:
Tom,

I understand all you’ve said. I was hoping for a different answer. C’est la vie.

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.

At least I have an answer.

Thanks!

/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".

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

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

From
Jerry Regan
Date:
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.


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

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

From
"David G. Johnston"
Date:
On Mon, Aug 28, 2017 at 6:42 PM, Jerry Regan <jerry.regan@concertoglobalresources.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.

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

From
Stuart Bishop
Date:
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/


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

From
Jerry Regan
Date:
Stuart,

Thank you!

I will investigate.

/s/jr
Consultant
Concerto GR
Mobile: 612.208.6601

Concerto - a composition for orchestra and a soloist



On 29Aug, 2017, at 7:52 AM, Stuart Bishop <stuart@stuartbishop.net> wrote:

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/

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

From
"Daniel Verite"
Date:
    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


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

From
John McKown
Date:
On Tue, Aug 29, 2017 at 10:21 AM, Daniel Verite <daniel@manitou-mail.org> wrote:
        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.

​Sounds like a job for "expect".

 


Best regards,
--
Daniel Vérité


--
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown

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

From
Nico Williams
Date:
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
--


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

From
Nico Williams
Date:
[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
--


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

From
Nico Williams
Date:
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
--


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

From
Steve Atkins
Date:
> 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



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

From
Nico Williams
Date:
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 pipe into 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 comfortable using the payload at all.  Besides, the payload is hardly necessary given that there's a database on which you can scribble the payload :)  It suffices that you receive a notification, and you can then check if there's anything to do.

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 would be 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 manpage plus Makefile changes) enough, or would a psql \wait command be better?

Nico
-- 

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

From
Steve Atkins
Date:
> 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

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

From
Nico Williams
Date:
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
--