Re: Picking out the most recent row using a time stamp column

On Fri, Feb 25, 2011 at 1:45 PM, Dave Crooke <dcrooke(at)gmail(dot)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