Thread: LISTEN/NOTIFY and python

Hi -

Does anyone have any suggestions for how to write client python code
that uses LISTEN?  The only thing I can find is the older PyGreSQL
module (import pg), which has getnotify(), but this seems to require
busy-waiting.  Is there any way to do this that will use select() or
something under the covers, so my client doesn't spin up the cpu?
(Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if
there's nothing cleaner.)

- John D. Burger
   MITRE

"John D. Burger" <john@mitre.org> writes:
> Does anyone have any suggestions for how to write client python code
> that uses LISTEN?  The only thing I can find is the older PyGreSQL
> module (import pg), which has getnotify(), but this seems to require
> busy-waiting.  Is there any way to do this that will use select() or
> something under the covers, so my client doesn't spin up the cpu?
> (Yes, I know I could poll-sleep-poll-sleep - that's what I'll do if
> there's nothing cleaner.)

The standard approach when using libpq directly is to get the file
descriptor number of the backend connection with PQsocket(), then
include that in the set of FDs that the client app's idle loop
select()s or poll()s on.  Read-ready on the socket doesn't necessarily
mean that a NOTIFY has arrived, but it's at least sufficient info
to justify waking up and checking libpq's status.

Dunno if you can translate that into python readily ...

            regards, tom lane

Tom Lane wrote:

> The standard approach when using libpq directly is to get the file
> descriptor number of the backend connection with PQsocket(), then
> include that in the set of FDs that the client app's idle loop
> select()s or poll()s on.

And Tino Wildenhain, in off-list mail, described getting the socket-
fd from the PyGreSQL connection object and doing something analogous.

It turns out that Python's listen() takes ints =or= objects with a
fileno() method, whence it gets the int, and PyGreSQL's connection
objects qualify.  So I can do this:

   import pg, select

   con = pg.connect(...)
   con.query("listen foo")

   while True:
     select.select([con], [], [])  # Wait for it ...
     print con.getnotify()

I wish I could do this with the more "standard" pgdb module, but,
then again, LISTEN/NOTIFY aren't standard.  Thanks, Tino and Tom, for
the pointers toward this solution.

- John D. Burger
   MITRE

John D. Burger schrieb:
> Tom Lane wrote:
>
>> The standard approach when using libpq directly is to get the file
>> descriptor number of the backend connection with PQsocket(), then
>> include that in the set of FDs that the client app's idle loop
>> select()s or poll()s on.
>
> And Tino Wildenhain, in off-list mail, described getting the socket-fd
> from the PyGreSQL connection object and doing something analogous.
>
> It turns out that Python's listen() takes ints =or= objects with a
> fileno() method, whence it gets the int, and PyGreSQL's connection

Well actually fileno() just returns an int and thats what the syscalls
expect as filehandle. Sockets have that method too. And the PyGreSQL
connection has a (tcp-) socket under the hood.

> objects qualify.  So I can do this:
>
>   import pg, select
>
>   con = pg.connect(...)
>   con.query("listen foo")
>
>   while True:
>     select.select([con], [], [])  # Wait for it ...
>     print con.getnotify()
>
> I wish I could do this with the more "standard" pgdb module, but, then
> again, LISTEN/NOTIFY aren't standard.  Thanks, Tino and Tom, for the
> pointers toward this solution.

Ah, here is the summery of my solution as promised:

import select
from pyPgSQL import PgSQL

db=PgSQL.connect( ... )

cur=db.cursor()
cur.execute("LISTEN baskets") # if baskets is the table name
db.commit()

while True:
     rlist,wlist,xlist=select.select([db.conn.socket],[],[],20*60)
     if rlist:
         if db.conn.socket in rlist:
             db.conn.consumeInput() # <- thats the important bit
             n=db.conn.notifies()
             if n:
                print "Backend with pid %s asks for us." % n.be_pid
                ... do something usefull ...


HTH.
Tino