10

What is the best way to program an immediate reaction to an update to data in a database?

The simplest method I could think of offhand is a thread that checks the database for a particular change to some data and continually waits to check it again for some predefined length of time. This solution seems to be wasteful and suboptimal to me, so I was wondering if there is a better way.

I figure there must be some way, after all, a web application like gmail seems to be able to update my inbox almost immediately after a new email was sent to me. Surely my client isn't continually checking for updates all the time. I think the way they do this is with AJAX, but how AJAX can behave like a remote function call I don't know. I'd be curious to know how gmail does this, but what I'd most like to know is how to do this in the general case with a database.

Edit: Please note I want to immediately react to the update in the client code, not in the database itself, so as far as I know triggers can't do this. Basically I want the USER to get a notification or have his screen updated once the change in the database has been made.

Daniel
  • 1,231
  • 3
  • 15
  • 20
  • wow a lot of people appear to be responding to the topic title, without actually reading the question. Its not a trigger folks!! If I had any votes left today I'd down vote them all to teach them a lesson to read the damn question before responding. – Neil N Feb 25 '09 at 17:57
  • Well that's a bit harsh. The question was all but asking for magic. If he wants an immediate reaction, triggers are probably going to be involved somewhere in the process. – Dana Feb 25 '09 at 18:01
  • @Dana: Not its not harsh to expect people to read a question before attempting to answer. No he is hardly asking for magic, he uses real workd example (gmail) as a model. And no, triggers are probably NOT going to be involved. – Neil N Feb 25 '09 at 18:31
  • The title is very poor. Triggers are the correct answer to the question as posed by the title. Further, until the edit happened, triggers were *still* the answer to "how to do this in the general case with a database." Removing references to databases would probably help. – Jon 'links in bio' Ericson Feb 26 '09 at 01:47
  • Actually I agree the title is poor, but I do want to know how to react to changes in data in a database in particular, but the key is to react to them in the client application. I'll see if I can come up with a better title to capture this. – Daniel Feb 26 '09 at 13:18

4 Answers4

7

You basically have two issues here:

  1. You want a browser to be able to receive asynchronous events from the web application server without polling in a tight loop.

  2. You want the web application to be able to receive asynchronous events from the database without polling in a tight loop.

For Problem #1

See these wikipedia links for the type of techniques I think you are looking for:

EDIT: 19 Mar 2009 - Just came across ReverseHTTP which might be of interest for Problem #1.

For Problem #2

The solution is going to be specific to which database you are using and probably the database driver your server uses too. For instance, with PostgreSQL you would use LISTEN and NOTIFY. (And at the risk of being down-voted, you'd probably use database triggers to call the NOTIFY command upon changes to the table's data.)

Another possible way to do this is if the database has an interface to create stored procedures or triggers that link to a dynamic library (i.e., a DLL or .so file). Then you could write the server signalling code in C or whatever.

On the same theme, some databases allow you to write stored procedures in languages such as Java, Ruby, Python and others. You might be able to use one of these (instead of something that compiles to a machine code DLL like C does) for the signalling mechanism.

Hope that gives you enough ideas to get started.

Evan
  • 18,183
  • 8
  • 41
  • 48
6

I figure there must be some way, after all, web application like gmail seem to update my inbox almost immediately after a new email was sent to me. Surely my client isn't continually checking for updates all the time. I think the way they do this is with AJAX, but how AJAX can behave like a remote function call I don't know. I'd be curious to know how gmail does this, but what I'd most like to know is how to do this in the general case with a database.

Take a peek with wireshark sometime... there's some google traffic going on there quite regularly, it appears.

Depending on your DB, triggers might help. An app I wrote relies on triggers but I use a polling mechanism to actually 'know' that something has changed. Unless you can communicate the change out of the DB, some polling mechanism is necessary, I would say.

Just my two cents.

itsmatt
  • 31,265
  • 10
  • 100
  • 164
1

Well, the best way is a database trigger. Depends on the ability of your DBMS, which you haven't specified, to support them.

Re your edit: The way applications like Gmail do it is, in fact, with AJAX polling. Install the Tamper Data Firefox extension to see it in action. The trick there is to keep your polling query blindingly fast in the "no news" case.

chaos
  • 122,029
  • 33
  • 303
  • 309
  • Please see my edit above, as far as I know triggers can't do what I'm asking for. – Daniel Feb 25 '09 at 17:44
  • Re your edit: This seems crazy to me because gmail has tons of clients, to have all of them polling the server all the time seems so inefficient. There's got to be a better way... But thanks for that insight, I wouldn't have thought that's how it's actually done. – Daniel Feb 25 '09 at 17:55
  • The alternative, "server push", the Big New Thing That Will Revolutionize The Web of 1994 or so, involves keeping a live TCP connection for every client, which is an exponentially worse idea. – chaos Feb 25 '09 at 17:57
  • I remember the Netscape FishCam that used server push circa 1994. Very exciting. – Chris Farmer Feb 25 '09 at 18:05
1

Unfortunately there's no way to push data to a web browser - you can only ever send data as a response to a request - that's just the way HTTP works.

AJAX is what you want to use though: calling a web service once a second isn't excessive, provided you design the web service to ensure it receives a small amount of data, sends a small amount back, and can run very quickly to generate that response.

teedyay
  • 23,293
  • 19
  • 66
  • 73
  • What about for normal desktop applications with a database back-end. Is there a way to push data to the application from the database? Maybe some type of connection that stays open? – Daniel Feb 25 '09 at 22:52
  • Not actually true. Server push does work. It's just a colossally bad idea. – chaos Feb 26 '09 at 01:47
  • Thanks chaos - I wasn't aware of that. I guess because it's such a bad idea, I've never even heard of it! :-) – teedyay Feb 26 '09 at 07:20