NOTIFY/LISTEN in Postgresql
Hi there
1) Can a Pg/SQL function "listen" for a notification sent from an external
instance?
I would like my stored function to pause/wait and continue its execution
once an external event (NOTIFY event) occurs.
2) In Pg/SQL I can implement a loop (until something happens) to
pause/wait. This costs CPU time -> Is there another solution? Actually I
would need something like a trigger to give my stored function the signal
to continue.
Thx & cheers,
Peter
On 10/15/2012 08:54 PM, P. Broennimann wrote:
Hi there
1) Can a Pg/SQL function "listen" for a notification sent from an
external instance?
No, it's the other way around. A client can `LISTEN` for a `NOTIFY` sent
by another client, either directly or via a PL/PgSQL function.
What you want is a NOTIFY callback or NOTIFY trigger, something that
invokes a function without any client action when a NOTIFY comes in. No
such feature exists.
I would like my stored function to pause/wait and continue its execution
once an external event (NOTIFY event) occurs.
Use an advisory lock, they're ideal for that job:
http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS
2) In Pg/SQL I can implement a loop (until something happens) to
pause/wait. This costs CPU time -> Is there another solution?
Depends on what you're waiting for. Details?
Again, an advisory lock may be a candidate.
--
Craig Ringer
Thx for the feedback I will take a look.
Here some details. Basically what I'd like to achieve:
Internet <-> AppliA <-> PostgreSQL <-> AppliB
1) AppliA receives a request from the internet and calls a Pg/SQL function.
2) The Pg/SQL function informs AppliB that there is some work waiting
(NOTIFY).
3) AppliB does the work and posts the result back to PostgreSQL.
... and now here's the problem -> The initial Pg/SQL function should give
back the result computed by AppliB -> It has to wait somehow and get
informed when the work from AppliB is done.
Thx & cheers,
Peter
2012/10/15 Craig Ringer <ringerc@ringerc.id.au>
Show quoted text
On 10/15/2012 08:54 PM, P. Broennimann wrote:
Hi there
1) Can a Pg/SQL function "listen" for a notification sent from an
external instance?No, it's the other way around. A client can `LISTEN` for a `NOTIFY` sent
by another client, either directly or via a PL/PgSQL function.What you want is a NOTIFY callback or NOTIFY trigger, something that
invokes a function without any client action when a NOTIFY comes in. No
such feature exists.I would like my stored function to pause/wait and continue its execution
once an external event (NOTIFY event) occurs.
Use an advisory lock, they're ideal for that job:
http://www.postgresql.org/**docs/current/static/explicit-**
locking.html#ADVISORY-LOCKS<http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS>2) In Pg/SQL I can implement a loop (until something happens) to
pause/wait. This costs CPU time -> Is there another solution?
Depends on what you're waiting for. Details?
Again, an advisory lock may be a candidate.
--
Craig Ringer
On Mon, Oct 15, 2012 at 6:44 AM, P. Broennimann <peter.broennimann@gmail.com
wrote:
Thx for the feedback I will take a look.
Here some details. Basically what I'd like to achieve:
Internet <-> AppliA <-> PostgreSQL <-> AppliB
1) AppliA receives a request from the internet and calls a Pg/SQL function.
2) The Pg/SQL function informs AppliB that there is some work waiting
(NOTIFY).
3) AppliB does the work and posts the result back to PostgreSQL.... and now here's the problem -> The initial Pg/SQL function should give
back the result computed by AppliB -> It has to wait somehow and get
informed when the work from AppliB is done.
two options:
1) advisory locks as Craig suggested. Beware of the fact that this will
result in delays however.
2) Return an interrim result to AppliA and the notify AppliA that the new
results are waiting using Listen/Notify (or maybe even pg_message_queue
with an xml payload).
Best wishes,
Chris Travers
Show quoted text
Thx & cheers,
Peter2012/10/15 Craig Ringer <ringerc@ringerc.id.au>
On 10/15/2012 08:54 PM, P. Broennimann wrote:
Hi there
1) Can a Pg/SQL function "listen" for a notification sent from an
external instance?No, it's the other way around. A client can `LISTEN` for a `NOTIFY` sent
by another client, either directly or via a PL/PgSQL function.What you want is a NOTIFY callback or NOTIFY trigger, something that
invokes a function without any client action when a NOTIFY comes in. No
such feature exists.I would like my stored function to pause/wait and continue its execution
once an external event (NOTIFY event) occurs.
Use an advisory lock, they're ideal for that job:
http://www.postgresql.org/**docs/current/static/explicit-**
locking.html#ADVISORY-LOCKS<http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS>2) In Pg/SQL I can implement a loop (until something happens) to
pause/wait. This costs CPU time -> Is there another solution?
Depends on what you're waiting for. Details?
Again, an advisory lock may be a candidate.
--
Craig Ringer
<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" text="#000000"
bgcolor="#FFFFFF">
<div class="moz-cite-prefix">On 10/15/2012 02:54 PM, P. Broennimann
wrote:<br>
</div>
<blockquote
cite="mid:CACm4aU-eFNaDiYw9uH1n9VPgHN5SqseJ3HqsbgXd7-Cw++NF-g@mail.gmail.com"
type="cite"><font face="courier new, monospace">Hi there</font>
<div><font face="courier new, monospace"><br>
</font></div>
<div><font face="courier new, monospace">1) Can a Pg/SQL function
"listen" for a notification sent from an external instance?</font></div>
<div><span style="font-family:'courier new',monospace">I would
like my stored function to pause/wait and continue its
execution once an external event (NOTIFY event) occurs.</span></div>
</blockquote>
<blockquote
cite="mid:CACm4aU-eFNaDiYw9uH1n9VPgHN5SqseJ3HqsbgXd7-Cw++NF-g@mail.gmail.com"
type="cite"><br>
<div><font face="courier new, monospace">2) In </font><span
style="font-family:'courier new',monospace">Pg/SQL I can
implement a loop (until something happens) to pause/wait. This
costs CPU time -> Is there another solution? Actually I
would need something like a trigger to give my stored function
the signal to continue.</span></div>
</blockquote>
<br>
Yes. I played around with this a while ago, and thought the
possibility was beyond cool.<br>
However, after playing around with it and getting some constructive
criticism, I decided that it would be better to keep the different
application layers completely separated, so that the database layer
would not be talking directly to the GUI.<br>
<br>
Here is an example function that I sent to the list a year+ ago.<br>
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
<a
href="http://archives.postgresql.org/pgsql-general/2011-06/msg00322.php">http://archives.postgresql.org/pgsql-general/2011-06/msg00322.php</a>
<br>
<br>
On the client end, I had something like this:<br>
class ListenThread(threading.Thread): <br>
def __init__(self,frame):<br>
threading.Thread.__init__(self)<br>
self.frame=frame<br>
<br>
def run(self):<br>
HOST, PORT = "192.168.1.207", 8080<br>
<br>
# Create the server, binding to localhost on port 8080<br>
server = SocketServer.TCPServer((HOST, PORT),
MyTCPHandler)<br>
server.frame=self.frame<br>
# Activate the server; this will keep running until you<br>
# interrupt the program with Ctrl-C<br>
server.serve_forever() <br>
<br>
</body>
</html>
On Mon, Oct 15, 2012 at 8:44 AM, P. Broennimann
<peter.broennimann@gmail.com> wrote:
Thx for the feedback I will take a look.
Here some details. Basically what I'd like to achieve:
Internet <-> AppliA <-> PostgreSQL <-> AppliB
1) AppliA receives a request from the internet and calls a Pg/SQL function.
2) The Pg/SQL function informs AppliB that there is some work waiting
(NOTIFY).
3) AppliB does the work and posts the result back to PostgreSQL.... and now here's the problem -> The initial Pg/SQL function should give
back the result computed by AppliB -> It has to wait somehow and get
informed when the work from AppliB is done.
I have done this exact workload many times and it's 100% doable. It
relies on dblink and read committed hacks but it's very robust.
In step step 2, you write out a record that is going to hold the
response from AppliB. It can be a simple text field or whatever you
need. Then you spawn a dblink call and issue the notify (it has to be
dblink, because notifications are not delivered till end of
transaction. After you spawn, you start sleep looping, waiting for
AppliB to set the response field. You can see it because AppliB is
writing it in a different transaction, and in ReadCommitted mode you
are allowed to see other transactions that complete. Once the
response is set (make sure you have a timeout), you can return the
reponse or optionally grab more data.
merlin