This is fine and great, except if the results of the actual SELECT
Not really.
I do this extensively, and it's really only useful when you can assume that no external clients will modify the cached data, OR if stale data is okay (in which case you can expire your cache every hour or every N hits and reload it).
You could write a trigger to do this, but doing this for you isn't a proper function for SA to attempt.
The good news is, if you do have data that's getting modified frequently by multiple clients, the database is going to be better about serving that data up than trying to keep your cache up-to-date anyway. Query for the specific parts you need, and trust your database. (And add more RAM if necessary. :)
query change, if a record is added or deleted for example. If I can
ensure that all interaction with the table itself go through my
MyTable class, then I can add some smarts to that class that know
when to update the global list when records change. However, it is
possible that records will change outside of SA (another client
poking at the db through `psql`, for example).
Is there something more clever I can do to know when to re-run the
query and re-set the global?
Not really.
I do this extensively, and it's really only useful when you can assume that no external clients will modify the cached data, OR if stale data is okay (in which case you can expire your cache every hour or every N hits and reload it).
Can I get a "last-modified" timestamp
from the DB, can SA do this automatically?
You could write a trigger to do this, but doing this for you isn't a proper function for SA to attempt.
The good news is, if you do have data that's getting modified frequently by multiple clients, the database is going to be better about serving that data up than trying to keep your cache up-to-date anyway. Query for the specific parts you need, and trust your database. (And add more RAM if necessary. :)
--
Jonathan Ellis
http://spyced.blogspot.com
