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
>
>
