List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:March 22 2005 8:54am
Subject:Re: Wait for data to change
View as plain text  
Shawn,

I like your idea very much.  Unfortunately I am not using version 5 yet, 
but this may hasten my upgrade.

My initial thought was to try and find a function like PostgreSQL's 
LISTEN command 
(http://www.postgresql.org/docs/8.0/interactive/sql-listen.html).  But I 
think this method is better.

As an extension to your idea, my UDF function can broadcast messages 
using the RSS protocol.  I am sure there are some easy to use libs out 
there.  My listening thread(s) can feed off the RSS listening for 
trigger events.

This negates the use of a polling thread, and indeed the entire thread. 
I don't need a connection to the database at all until I know there is 
data there.  Magic :)

Thanks,

Ben.

SGreen@stripped wrote:
> Ben Clewett <Ben.Clewett@stripped> wrote on 03/21/2005 11:36:45 
> AM:
> 
> 
>>Dear MySQL,
>>
>>I have an application which need to wait for some data to change, then 
>>act on this change.
>>
>>I am polling the MySQL once a second using "SELECT ..."
>>
>>I believe there is an alternate method where a thread can be made to 
>>wait for some change, therefore avoiding the expensive polling and 
>>improving performance.
>>
>>If any member knows what this may be, I would be very interested.
>>
>>Regards, Ben Clewett.
>>
> 
> 
> I can't think of any way to do this "efficiently" without a trigger, a 
> UDF, and a custom daemon. I assume based on your description that you have 
> some sort of "client" application that needs to know if some data value 
> has changed. However, you don't want to keep "pinging" the server to 
> continuously ask "has it changed yet, has it changed yet, has it changed 
> yet..."
> 
> Here's how I think you can make this work with some sort of efficiency. 
> You need to setup your application to connect to a daemon on some server 
> somewhere. The only purpose of this daemon is to broadcast to those 
> clients who connect to it a message to the effect that "some data has 
> changed on the table you are interested in". If you wanted to get really 
> fancy, the message could say what data changed and possibly what the new 
> value is. That way your client could possibly act on the data without 
> needing to poll the server.
> 
> How does the daemon know that data just changed? That's where the trigger 
> and UDF come in. Triggers are fired during certain database events (adding 
> records, changing records, or deleting records). I don't know for certain 
> but I believe triggers can use UDFs in their code. The UDF that is called 
> by the trigger detecting a change in the table you are interested in is 
> what notifies the daemon (described earlier) that the data has changed. 
> And, because the UDF is only called from within a trigger, you only bother 
> the daemon when a change actually occurs.
> 
> The downside to this design is that triggers are not supported until 5.x 
> (still in testing) so I don't know if you have that option. User-defined 
> functions (UDFs) may be able to cover the job by themselves IF and only if 
> you can control data changes to ALWAYS use your UDF. Otherwise some 
> changes may slip through the cracks.
> 
> Anyway, that was my idea. Is there any other way for you to communicate a 
> change in data than a "passive detect" like you are doing? How much 
> control do you have over what can and cannot change your data 
> (specifically the field you keep polling)? What other application-based 
> options are open to you?
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 
> 

Thread
Wait for data to changeBen Clewett21 Mar
  • Re: Wait for data to changeSGreen21 Mar
    • Re: Wait for data to changeEric Bergen21 Mar
    • Re: Wait for data to changeBen Clewett22 Mar