Thread: Inter-app communication via DB
Hi list. One pattern I've used is for apps to communicate events to each other through the database. ie: - App 1 sents a boolean value to True - App 2 queries the field every 10s, sets the value to False, and does something. Is this reasonable, or should apps avoid this pattern? I have seen the NOTIFY and LISTEN SQL statements. However: 1) App 2 might not be running at the time (eg: it's launched from cron, or it was temporarily stopped), and the expectation is that App 2 will run the special logic when it is started. 2) App 2 is usually single-threaded, and needs to do other things in it's main thread besides wait for a DB notification. I also know of RPC, but haven't used it before, and don't see a need if you can use the above pattern. I would use RPC (or unix signals if on the same host) if App 2 needed to respond quickly, and I didn't want to hammer the DB & network with constant polling. Any comments? David.
On Thu, Jun 19, 2008 at 11:09:12AM +0200, David wrote: > One pattern I've used is for apps to communicate events to each other > through the database. Works nicely with LISTEN/NOTIFY. We use it a lot in GNUmed. > - App 1 sents a boolean value to True > - App 2 queries the field every 10s, sets the value to False, and does > something. > > Is this reasonable, or should apps avoid this pattern? Add more app instances and you'll have a lot of polling. > I have seen the NOTIFY and LISTEN SQL statements. However: > > 1) App 2 might not be running at the time (eg: it's launched from > cron, or it was temporarily stopped), and the expectation is that App > 2 will run the special logic when it is started. That will happen anyway, no matter what the message transport is like. Apps will have to read state at startup anyway, no ? > 2) App 2 is usually single-threaded, and needs to do other things in > it's main thread besides wait for a DB notification. Well, threads in Python aren't *that* hard to get right. But you could also write a small listener demon which gets started on behalf of the local app instance which writes to a local watch file which is being polled by the local app instance. Takes the poll pressure off the database and avoids having to thread the app, too. There's a bunch of Python code here http://cvs.savannah.gnu.org/viewvc/gnumed/gnumed/client/pycommon/gmBackendListener.py?root=gnumed&view=markup that might be of interest. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
hello look to orafce package http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29 regards Pavel 2008/6/19 David <wizzardx@gmail.com>: > Hi list. > > One pattern I've used is for apps to communicate events to each other > through the database. > > ie: > > - App 1 sents a boolean value to True > - App 2 queries the field every 10s, sets the value to False, and does > something. > > Is this reasonable, or should apps avoid this pattern? > > I have seen the NOTIFY and LISTEN SQL statements. However: > > 1) App 2 might not be running at the time (eg: it's launched from > cron, or it was temporarily stopped), and the expectation is that App > 2 will run the special logic when it is started. > > 2) App 2 is usually single-threaded, and needs to do other things in > it's main thread besides wait for a DB notification. > > I also know of RPC, but haven't used it before, and don't see a need > if you can use the above pattern. > > I would use RPC (or unix signals if on the same host) if App 2 needed > to respond quickly, and I didn't want to hammer the DB & network with > constant polling. > > Any comments? > > David. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Jun 19, 2008 at 11:25 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > On Thu, Jun 19, 2008 at 11:09:12AM +0200, David wrote: [...] > >> One pattern I've used is for apps to communicate events to each other >> through the database. > > Works nicely with LISTEN/NOTIFY. We use it a lot in GNUmed. > >> - App 1 sents a boolean value to True >> - App 2 queries the field every 10s, sets the value to False, and does >> something. >> >> Is this reasonable, or should apps avoid this pattern? > Add more app instances and you'll have a lot of polling. > Good point. >> I have seen the NOTIFY and LISTEN SQL statements. However: >> >> 1) App 2 might not be running at the time (eg: it's launched from >> cron, or it was temporarily stopped), and the expectation is that App >> 2 will run the special logic when it is started. > > That will happen anyway, no matter what the message > transport is like. Apps will have to read state at startup > anyway, no ? Another good point. I have a small problem with this. If app1 wants to tell app2 to perform an expensive operation (which you don't want app2 to do each time it starts up), in the original pattern it could just set a boolean variable. Now it needs to both set a boolean variable (in case app2 isn't running at the moment) and use NOTIFY (for when it is), which seems a bit redundant. > >> 2) App 2 is usually single-threaded, and needs to do other things in >> it's main thread besides wait for a DB notification. > Well, threads in Python aren't *that* hard to get right. But > you could also write a small listener demon which gets > started on behalf of the local app instance which writes to > a local watch file which is being polled by the local app > instance. Takes the poll pressure off the database and > avoids having to thread the app, too. I don't mind threads in Python. It's mostly for C and C++ apps where I don't want to add threading and an extra db connection to the code just to be able to receive notifications from other apps. For cases like that, your idea of a separate listener daemon will be useful if there are a lot of instances that want to poll at the same time :-) David.
On Thu, Jun 19, 2008 at 11:46:42AM +0200, David wrote: > > That will happen anyway, no matter what the message > > transport is like. Apps will have to read state at startup > > anyway, no ? > > I have a small problem with this. If app1 wants to tell app2 to > perform an expensive operation (which you don't want app2 to do each > time it starts up), in the original pattern it could just set a > boolean variable. Now it needs to both set a boolean variable (in case > app2 isn't running at the moment) and use NOTIFY (for when it is), > which seems a bit redundant. You could add a trigger to the table with the boolean flag. The trigger sends the NOTIFY all by itself. So when the app is listening it gets the signal. When it isn't it will read the flag at startup. All the other app does is set the flag. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Le jeudi 19 juin 2008, David a écrit : > One pattern I've used is for apps to communicate events to each other > through the database. > > ie: > > - App 1 sents a boolean value to True > - App 2 queries the field every 10s, sets the value to False, and does > something. > > Is this reasonable, or should apps avoid this pattern? The PGQ package from skytools allows you to produce events into a queue from the provider side and retrieve them at the consumer(s) side. The consumer(s) needs not be running while the provider produces them, this will simply cause lag. The consuming of events has the options to set any event processing as failed or retry. PGQ comes with several APIs, the common one is its SQL one, atop of it reside both a python one and (recently) a PHP one. You might want to read those URLs to get more ideas about it, but I'd definitely consider using PGQ in your case: http://skytools.projects.postgresql.org/doc/pgq-sql.html http://www.pgcon.org/2008/schedule/events/79.en.html http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ Regards, -- dim