Re: GSoC - Materialized Views - is stale or fresh?

Pavel Baros wrote:
> After each INSERT, UPDATE, DELETE statement (transaction)
> pg_class.rellastxid would be updated. That should not be time- or
> memory- consuming (not so much) since pg_class is cached, I guess.

An update in PostgreSQL is essentially an INSERT followed a later DELETE
when VACUUM gets to the dead row no longer visible. The problem with
this approach is that it will leave behind so many dead rows in pg_class
due to the heavy updates that the whole database could grind to a halt,
as so many operations will have to sort through all that garbage. It
could potentially double the total write volume on the system, and
you'll completely kill people who don't have autovacuum running during
some periods of the day.

The basic idea of saving the last update time for each relation is not
unreasonable, but you can't store the results by updating pg_class. My
first thought would be to send this information as a message to the
statistics collector. It's already being sent updates at the point
you're interested in for the counters of how many INSERT/UPDATE/DELETE
statements are executing against the table. You might bundle your last
update information into that existing message with minimal overhead.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us