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

2017-09-05 Thread Nico Williams
On Tue, Sep 05, 2017 at 08:19:13AM -0700, Steve Atkins wrote:
> > On Sep 4, 2017, at 10:25 PM, Nico Williams  wrote:
> > On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins  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
-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-09-05 Thread Steve Atkins

> On Sep 4, 2017, at 10:25 PM, Nico Williams  wrote:
> 
> On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins  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?

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 query is another question).

Cheers,
  Steve

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-09-04 Thread Nico Williams
On Mon, Sep 4, 2017 at 4:21 PM Steve Atkins  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

2017-09-04 Thread Steve Atkins

> On Sep 3, 2017, at 3:32 PM, Nico Williams  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 into xargs. (Also the bare bones of a 
notify-based scheduler).

Cheers,
  Steve



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-09-03 Thread Nico Williams
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
-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-09-03 Thread Nico Williams
[I meant to send this to the list]

On Mon, Aug 28, 2017 at 07:08:28PM -0400, Tom Lane wrote:
> "David G. Johnston"  writes:
> > On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> > jerry.re...@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
-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-09-03 Thread Nico Williams

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-08-29 Thread Jerry Regan
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  wrote:
> 
> On 29 August 2017 at 08:42, Jerry Regan
>  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  wrote:
>> 
>> "David G. Johnston"  writes:
>> 
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@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 
> http://www.stuartbishop.net/



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

2017-08-29 Thread John McKown
On Tue, Aug 29, 2017 at 10:21 AM, Daniel Verite 
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".
https://www.tcl.tk/man/expect5.31/expect.1.html​



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

2017-08-29 Thread Daniel Verite
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-08-29 Thread Stuart Bishop
On 29 August 2017 at 08:42, Jerry Regan
 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  wrote:
>
> "David G. Johnston"  writes:
>
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry.re...@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 
http://www.stuartbishop.net/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-08-29 Thread Jerry Regan
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  wrote:
> 
> "David G. Johnston"  writes:
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@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

2017-08-29 Thread Jerry Regan
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  
> wrote:
> 
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan 
>  > 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.
> 
> https://www.postgresql.org/docs/current/static/app-psql.html 
> 
> 
> ​"​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

2017-08-29 Thread Jerry Regan
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  wrote:
> 
> "David G. Johnston"  writes:
>> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
>> jerry.re...@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

2017-08-28 Thread David G. Johnston
On Mon, Aug 28, 2017 at 6:42 PM, Jerry Regan <
jerry.re...@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) 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

2017-08-28 Thread Tom Lane
"David G. Johnston"  writes:
> On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
> jerry.re...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


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

2017-08-28 Thread David G. Johnston
On Mon, Aug 28, 2017 at 1:28 PM, Jerry Regan <
jerry.re...@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.

https://www.postgresql.org/docs/current/static/app-psql.html

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