Re: NOTIFY/LISTEN in Postgresql

[Sim Zacks <sim@xxxxxxxxxxxxxx>]

On 10/15/2012 02:54 PM, P. Broennimann wrote:

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.

Yes. I played around with this a while ago, and thought the possibility was beyond cool.
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.

Here is an example function that I sent to the list a year+ ago.
http://archives.postgresql.org/pgsql-general/2011-06/msg00322.php

On the client end, I had something like this:
class ListenThread(threading.Thread):    
    def __init__(self,frame):
        threading.Thread.__init__(self)
        self.frame=frame
       
    def run(self):
            HOST, PORT = "192.168.1.207", 8080
       
            # Create the server, binding to localhost on port 8080
            server = SocketServer.TCPServer((HOST, PORT), MyTCPHandler)
            server.frame=self.frame
            # Activate the server; this will keep running until you
            # interrupt the program with Ctrl-C
            server.serve_forever()