On Fri, Feb 25, 2011 at 1:45 PM, Dave Crooke
<dcrooke@gmail.com> wrote:
Hi Dave
Yes, 100% the best solution .... I did the same thing a while back, I just have a separate copy of the data in a "latest" table and the Java code just runs a second SQL statement to update it when writing a new record (I've never been a trigger fan).
I found myself looking at the "find the latest" query again though in the process of building a "demo mode" into our application, which will replay a finite set of data on a rolling loop by moving it forward in time, and also has to simulate the continuous updating of the "latest" table so the the business logic will be appropriately fooled.
My next tweak will be to cache the "latest" table in the Java layer ;-)
Cheers
Dave
Our application has what sounds like a similar functionality that we call "playback". The way that we did it was to have a schema called "playback" with identical tables to those that we want to have repopulated. All the other tables exist in only the "public" schema and then we don't have to do any duplication of that data. Then during playback it just runs a query to copy from the "public" table to the "playback" table and the trigger will populate the "latest" table in the "playback" schema automatically just like when the program is running normally and populating the "public" version.
The secret sauce comes in by setting "SET search_path TO playback, public;" because then your application runs all the same queries to get the data and doesn't have to know that anything different is going on other than the copy coperation that it's doing. It's nice because it takes all of the data management burden off of the application and then allows the database to do the hard work for you. It's obviously not the perfect solution but it wasn't too hard to setup and we've really liked the way it works.
Dave