Re: Discovering time of last database write

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.

Thanks,

Andy

On 08/01/07, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
>
> On Mon, Jan 08, 2007 at 09:22:05 +0100,
> Andy Dale <andy(dot)dale(at)gmail(dot)com> wrote:
> > Hi,
> >
> > Sorry for the slight delay in my response.
> >
> > I am using 3 PostgreSQL databases and writing to them using an SQL
> proxy.
> > These databases have a high write volume. On rebooting all 3 servers
> for
> > OS/Software updates, i would like to figure out which was the last
> written
> > to DB (this is assuming the DB/Servers are not all taken down at the
> same
> > time), the times are kept in sync with NTP.
> >
> > I imagine it is possible to get this behaviour with after triggers, but
> this
> > means i have to attach the same trigger to each table ??
>
> I think what Scott was suggesting was that you tell us what you are
> planning
> to do with the time. Depending on what you are trying to do, there may be
> better ways of doing things.
>
> Also the time of last update for an MVCC is a bit nebulous and to get it
> in the database might not be possible with the semantics you want. For
> example
> getting the time a transaction is committed is going to be hard without
> modifying the backend, as any triggers will run before a transaction is
> committed and can't know the precise time of the commit.
>
> >
> > Thanks,
> >
> > Andy
> >
> > On 04/01/07, Scott Marlowe <smarlowe(at)g2switchworks(dot)com> wrote:
> > >
> > >On Thu, 2007-01-04 at 11:11, Andy Dale wrote:
> > >> Hi,
> > >>
> > >> I need to be able to determine the last time (and date) that a
> > >> database was written to. I know it could be possible just to check
> > >> the last modified dates in the PGDATA directory, but i need to
> compare
> > >> the last write time of 3 databases (connecting via JDBC). Hopefully
> > >> the last write date is contained somewhere in a system table
> > >> (information schema) but i have no idea of the table(s) i would need
> > >> to query.
> > >
> > >Bad news, it's not generally stored.
> > >
> > >Good news, it's not that hard to implement.
> > >
> > >Perhaps if you give us the bigger picture we can make more logical
> > >suggestions on how to accomplish it.
> > >
>