Right, I don't disagree in principal, Yonathon.
Parsing the "from" clause is non-trivial to the point of probably not being useful (think "subselect") and of course tables whacked by PL/SQL can't be tracked by the db api running at the tcl level. This latter issue is the real killer when it comes to automatically tracking down which cache items to flush.
I don't have any brilliant ideas myself. Perhaps segregration of database changes into two classes - "must take place now" (flush cache immediately) or "can be delayed" (flush at next timeout) - is a compromise simple solution that could work??? Changes to permissions should take place immediately, perhaps addition of content can wait.
Of course any caching policy will have to be configurable and we'll always want to make it easy to turn off (in fact "off" should probably be the default). Only relatively busy sites need to cache. A big healthy dual-AMD DDR-based system with well-written queries should be able to run the majority of sites out there. We're really talking the top few percentile of busy sites when we talk about the need for caching.
Another issue with cache schemes more complex than "flush every N minutes" is the synchronization of cache between multiple AOLserver front-ends. The fact that Greenpeace runs two webserver front-ends was really fortunate in the sense that it kept me from even thinking about trying to implement any fancy cache scheme in the last day or so before launch :)
