Synchronous LISTEN/NOTIFY?

Synchronous LISTEN/NOTIFY?

Post by Lincoln Ye » Sat, 06 Jan 2001 12:22:02



Hi,

Say I have two applications A and B communicating via a postgresql
database. A sends data for B to work on.

How can I get B to _wait_ for a signal from A before proceeding?

Right now it looks like B has to keep polling regularly. So in order for
things to occur in a timely fashion the polling interval has to be
ridiculously short. This means using more CPU than I would like.

Is there a postgresql equivalent of a unix select/accept?

Basically LISTEN doesn't wait. It'll really be nice to have one which does
- lots of cool stuff can be done.

For example, I could have a webapp write trigger another app running as
root- the webapp writes high level stuff to the database, and a run-as-root
app does stuff based on it, this seems to be a more secure. Or I could have
a small app watch some logs for some event, send the data to a database.
Then another small app waits for a trigger and then uses some of that data
to do something else. Or we could even have a database level trigger, so
when a certain criteria is met, the waiting application is triggered.
Basically you now can have apps use the database for instant messaging :).

Is this sort of thing possible with postgresql or even other RDBMs?

Thanks,
Link.

 
 
 

Synchronous LISTEN/NOTIFY?

Post by Tom La » Sat, 06 Jan 2001 12:53:28



> Basically you now can have apps use the database for instant messaging :).

My former company was doing that since Postgres 6.4 or so.

Quote:> Right now it looks like B has to keep polling regularly.

No, it just has to use select() to wait on the postgres connection
(plus any other input files it wants to pay attention to).

Quote:> Basically LISTEN doesn't wait.

LISTEN has nothing to do with waiting.  It merely informs the backend
of your interest in subsequently receiving notices of a particular type.
Perhaps you should think of it as like signal(2).

See
http://www.postgresql.org/devel-corner/docs/postgres/libpq-notify.htm
but in brief the idea is:

1. The outer event loop of your application uses select() to wait on
the PQsocket() fd as well as any other interesting fds.

2. When you see input ready on the PQsocket() fd, call PQconsumeInput(),
then check PQnotifies().

3. Also check PQnotifies() after any PQexec(), to see if notify messages
came in during the query.

Keep in mind also that notifications are only delivered when you are
not within a transaction block (BEGIN/END).

                        regards, tom lane

 
 
 

Synchronous LISTEN/NOTIFY?

Post by Lincoln Ye » Sat, 06 Jan 2001 13:22:35




>> Basically LISTEN doesn't wait.

>LISTEN has nothing to do with waiting.  It merely informs the backend
>of your interest in subsequently receiving notices of a particular type.
>Perhaps you should think of it as like signal(2).

My fault - poor phrasing.

Quote:>See
>http://www.veryComputer.com/
>but in brief the idea is:

>1. The outer event loop of your application uses select() to wait on
>the PQsocket() fd as well as any other interesting fds.

>2. When you see input ready on the PQsocket() fd, call PQconsumeInput(),
>then check PQnotifies().

>3. Also check PQnotifies() after any PQexec(), to see if notify messages
>came in during the query.

>Keep in mind also that notifications are only delivered when you are
>not within a transaction block (BEGIN/END).

Uhoh, since I'm using perl does that mean I have to patch DBI and Pg::DBD?
And worse - turn off transactions.

Would it be viable idea to add a WAIT command/statement for higher level
access (e.g. "SQL" level access to this feature)?

e.g.
WAIT "blahblahblah";
 where it will wait until a NOTIFY "blahblahblah".

That'll be real nice to have :). If I start messing about with PQstuff I'll
probably*up somewhere.

Thanks anyway,
Link.

 
 
 

Synchronous LISTEN/NOTIFY?

Post by Tom La » Sat, 06 Jan 2001 13:36:23



> Uhoh, since I'm using perl does that mean I have to patch DBI and Pg::DBD?

Couldn't say.  I didn't have much trouble setting up a Tcl application
to work smoothly with NOTIFY events, but I've never tried such a thing
in Perl.  Any Perl gurus out there who can pontificate about how to
set up an event loop in Perl?

Quote:> Would it be viable idea to add a WAIT command/statement for higher level
> access (e.g. "SQL" level access to this feature)?

Strikes me as pretty useless.  If you were to PQexec("WAIT foo") then
your application would be totally locked up until and unless a foo
signal gets delivered.  You won't be satisfied with that for long.

                        regards, tom lane

 
 
 

Synchronous LISTEN/NOTIFY?

Post by Lincoln Ye » Sat, 06 Jan 2001 14:16:30




>> Would it be viable idea to add a WAIT command/statement for higher level
>> access (e.g. "SQL" level access to this feature)?

>Strikes me as pretty useless.  If you were to PQexec("WAIT foo") then
>your application would be totally locked up until and unless a foo
>signal gets delivered.  You won't be satisfied with that for long.

I'll actually be very happy, that's exactly what I want, and I can't see
why it would be a bad thing. I personally like the idea of programs doing
specific tasks, if there's nothing to do, they don't do anything else. That
way there is very little that can go wrong.

Cheerio,
Link.

 
 
 

1. DBD::Pg and NOTIFY (was Re: [GENERAL] Synchronous LISTEN/NOTIFY?)

Very easy to do with old Pg interface (you just register a notify handler,
like in libpq), but currently DBD::Pg is not notify-aware. I wanted to fix
that (should be simple), but I think it'll have to wait a month or two
till I get a spare minute...

-alex

2. Setting up 6.5 SQL with 7.0 already on the machine

3. LISTEN/NOTIFY

4. ESQL with SQL Anywhere.

5. LISTEN/NOTIFY benchmarks?

6. SQL Statement not working.

7. problem with notify/listen

8. FPW2.6 long list

9. listen/notify argument (old topic revisited)

10. LISTEN/NOTIFY

11. NOTIFY/LISTEN Using Embedded SQL (ecpg)

12. LISTEN/NOTIFY benchmarks?