Re: Discovering time of last database write

Hi,

I am still not so certain about adding a timestamp column to each table, as
within a few months the table will be quite big. My current thinking is to
have a trigger per table that overwrties a single value in a single utility
table after every write, this will be far quicker to select when working
with large tables ??? HA-JDBC does not care about the individual table last
write date/time as it is currently not spphisticated enough to do per table
sync with different masters per table, it just loops through each table in
the first db activated treating it as being the most up to date. So HA-JDBC
(i) just need the last write time of the database on a whole, as this will
hopefully mean it is the most up to date.

Andy

On 08/01/07, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
>
> On Mon, 2007-01-08 at 03:26, Andy Dale wrote:
> > Ok.
> >
> > The SQL Proxy i am using (HA-JDBC) has some limitations with regard to
> > getting it's "cluster" back into sync. If ha-jdbc uses the wrong DB
> > (one that has been out of action for a while) as the starting point
> > for the cluster it will then try and delete stuff from the other DB's
> > on their introduction to the cluster.
> >
> > I thought the easiest way to control a complete "cluster" restart
> > would be to extract the last write date and introduce the one with the
> > last write date first, this will make certain the above scenario does
> > not happen.
>
> Sorry, I hadn't seen this post when I wrote my lost one.
>
> Yeah, I think having a timestamp column with a rule so it has the
> current timestamp when written to and then selecting for the max in each
> table would work out. You could probably get fancier, but I'm guessing
> that cluster startup is a pretty rare thing, so it's probably easier to
> write a script that selects all the tablenames from pg_tables (???) in
> your schema and checks for the highest time in each table and selects
> the master from that.
>