Thread: Anyone working on asynchronous NOTIFY reception?

Hi folks,
  Hope I'm not making a fool of myself by posting to a list I just
joined ... but I couldn't find much about this in the list archives.

I'm looking at an application that involves several client processes
communicating in real time via a pgsql database.   "Real time" means
that when one client writes something, any other clients that are
interested need to know about it within a few seconds at most.
The other clients can use LISTEN/NOTIFY to detect updates --- but
I don't think I can accept the notion of continuously doing empty
queries to receive the notifies.  That'll drive performance into the
ground.  What I want is for a client to be able to sleep until
something interesting happens.

As near as I can tell from backend/commands/async.c, notify messages
actually are sent out to the frontends asynchronously, as soon as
possible (either immediately or at the end of the current transaction).
The problem is simply that libpq is designed in such a way that it can't
read in the notify message except while processing a new query.

I am thinking about revising libpq so that it doesn't force synchronous
reading, but can be called from an application's main loop whenever the
backend connection is ready for reading according to select().  This
would seem to be a major win for Tcl and other environments, as well as
for my problem: an app waiting for a server response would not have to
be dead to the rest of the world.

Is this a correct description of the situation?  Has anyone already
started to work on this issue?  If not, would someone who knows the
code be willing to give me guidance?  I'm entirely new to Postgres
and am likely to make some dumb choices without advice...

            regards, tom lane

>
> Hi folks,
>   Hope I'm not making a fool of myself by posting to a list I just
> joined ... but I couldn't find much about this in the list archives.
>
> I'm looking at an application that involves several client processes
> communicating in real time via a pgsql database.   "Real time" means
> that when one client writes something, any other clients that are
> interested need to know about it within a few seconds at most.
> The other clients can use LISTEN/NOTIFY to detect updates --- but
> I don't think I can accept the notion of continuously doing empty
> queries to receive the notifies.  That'll drive performance into the
> ground.  What I want is for a client to be able to sleep until
> something interesting happens.

The person who knows the most about this is:

    dz@cs.unitn.it (Massimo Dal Zotto)


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I don't know how many clients you're running, but sending an empty
query every second or three isn't too much of a performance hit (I
know it still sucks though)..

could you cc copies of mail to the list?  I'm sure we're very
interested..

On Wed, 15 April 1998, at 16:57:43, Bruce Momjian wrote:

> > I'm looking at an application that involves several client processes
> > communicating in real time via a pgsql database.   "Real time" means
> > that when one client writes something, any other clients that are
> > interested need to know about it within a few seconds at most.
> > The other clients can use LISTEN/NOTIFY to detect updates --- but
> > I don't think I can accept the notion of continuously doing empty
> > queries to receive the notifies.  That'll drive performance into the
> > ground.  What I want is for a client to be able to sleep until
> > something interesting happens.
>
> The person who knows the most about this is:
>
>     dz@cs.unitn.it (Massimo Dal Zotto)
>
>
> --
> Bruce Momjian                          |  830 Blythe Avenue
> maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
>   +  If your life is a hard drive,     |  (610) 353-9879(w)
>   +  Christ can be your backup.        |  (610) 853-3000(h)

Brett McCormick <brett@work.chicken.org> writes:
> I don't know how many clients you're running, but sending an empty
> query every second or three isn't too much of a performance hit (I
> know it still sucks though)..

Well, raw performance is only part of it.  Some of the clients will
be accessing the database server across interstate dial-on-demand
ISDN links.  Popping up the link for a minute whenever something
happens (which is likely to be only a few times a day) is cool.
Nailing it up 24x7 to pass a steady flow of empty queries is not cool.

> could you cc copies of mail to the list?  I'm sure we're very
> interested..

Sure, I'll keep you posted.  If anything comes of this I'll be
submitting the mods, of course.

            regards, tom lane