Thread: Server->Client notification (without polling)?
Hello, I wonder whether there is a "standard" "built-in" mechanism for a PostgreSQL client to register with the database for notification in case of modifications to a given table. As I understand from the manual, the LISTEN and NOTIFY SQL commands require to poll the database to retrieve the notification events...? Would a trigger implemented in PL/Python allow to implement such a notification mechanism? Would this be an efficient way to do so? TIA, Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove ".nospam" to reply.
Wolfgang Keller <wolfgang.keller.nospam@gmx.de> writes:
> As I understand from the manual, the LISTEN and NOTIFY SQL commands require
> to poll the database to retrieve the notification events...?
No, they don't.
regards, tom lane
>> As I understand from the manual, the LISTEN and NOTIFY SQL commands require >> to poll the database to retrieve the notification events...? > > No, they don't. Thanks for the reply. :-) I read the manual again and now I understand that it's the libpq client library which requires polling a function to retrieve the notifications received from the server. Is there no way to tell the library "please call me when a notification arrives"? P.S.: I'm accessing PostgreSQL from Python if this matters, any of the Python modules for PostgreSQL access is ok for me instead of libpq. TIA, Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove ".nospam" to reply.
Wolfgang Keller <wolfgang.keller.nospam@gmx.de> writes:
> I read the manual again and now I understand that it's the libpq client
> library which requires polling a function to retrieve the notifications
> received from the server. Is there no way to tell the library "please call me
> when a notification arrives"?
No, because it's only a library, it doesn't have its own thread of
control.
If you are so inclined you can set up your application's main loop to
watch for input on the database connection's socket (along with whatever
other event sources it watches) and then call the library to see what it
was.
regards, tom lane
On Wed, Sep 20, 2006 at 09:54:03PM +0200, Wolfgang Keller wrote: > I read the manual again and now I understand that it's the libpq client > library which requires polling a function to retrieve the notifications > received from the server. Is there no way to tell the library "please call me > when a notification arrives"? > > P.S.: I'm accessing PostgreSQL from Python if this matters, any of the Python > modules for PostgreSQL access is ok for me instead of libpq. Psycopg2 supports LISTEN/NOTIFY; see examples/notify.py in the psycopg2 source code. http://www.initd.org/projects/psycopg2 -- Michael Fuhr
>> I read the manual again and now I understand that it's the libpq client >> library which requires polling a function to retrieve the notifications >> received from the server. Is there no way to tell the library "please >> call me when a notification arrives"? > > No, because it's only a library, it doesn't have its own thread of > control. Just to learn something new: Is this because the library implementor has chosen to do it this way or because there is no way to spawn a thread from this library? I'm just a poor Python scripting dilettant for whom threads and such things are deep black voodoo, so please excuse me if this question is utterly braindead. :-) The reason why I am asking for notification without polling is that I would like to use a shared PostgreSQL database for data exchange with other applications, and the data exchange is likely to happen very intermittently, i.e. the data from other applications it will typically arrive in large batches, with long pauses (sometimes hours) in between. But I still want to get the data as soon as it is available, to not let the end-users wait for minutes. > If you are so inclined you can set up your application's main loop to > watch for input on the database connection's socket (along with whatever > other event sources it watches) and then call the library to see what it > was. Thanks, that's exactly what the example for Psycopg2 does apparently. Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove ".nospam" to reply.
> Psycopg2 supports LISTEN/NOTIFY; see examples/notify.py in the psycopg2 > source code. > > http://www.initd.org/projects/psycopg2 Thanks a lot for the hint, this looks really useful. :-) Sincerely, Wolfgang Keller -- My email-address is correct. Do NOT remove ".nospam" to reply.
On 21/9/2006 23:19, "Wolfgang Keller" <wolfgang.keller.nospam@gmx.de> wrote: >>> I read the manual again and now I understand that it's the libpq client >>> library which requires polling a function to retrieve the notifications >>> received from the server. Is there no way to tell the library "please >>> call me when a notification arrives"? >> >> No, because it's only a library, it doesn't have its own thread of >> control. > > Just to learn something new: > > Is this because the library implementor has chosen to do it this way or > because there is no way to spawn a thread from this library? > A library is just a static collection of functions stored in a file. Although some may be used by the system, a library is not 'running' as a program would be so can't respond to activity of any sort. Some of the functions in the pg library would provide the functionality that a program would use to connect to, monitor and respond to notifications from the pg server but you would need to write or have someone else write a program that runs constantly and takes actions when something happens. This program would be running constantly as PostgreSQL is. > The reason why I am asking for notification without polling is that I would > like to use a shared PostgreSQL database for data exchange with other > applications, and the data exchange is likely to happen very intermittently, > i.e. the data from other applications it will typically arrive in large > batches, with long pauses (sometimes hours) in between. But I still want to > get the data as soon as it is available, to not let the end-users wait for > minutes. Maybe you could look at adding this to your PostgreSQL server - start up one of your python scripts after the data has been added. Not sure how you are 'sharing' your data but you could maybe add in a step after it has been added to start your script. The other option is to write a trigger for the table that receives the data, any time a row is inserted to that table you could have it start up your python script which could tell everyone that it is there. I haven't tried this myself but I do believe hearing that pl/perl could run an external script. -- Shane Ambler Postgres@007Marketing.com Get Sheeky @ http://Sheeky.Biz