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

On 12/06/10 17:18, Pavel Baros wrote:
> I am curious how could I solve the problem:
>
> During refreshing I would like to know, if MV is stale or fresh? And I
> had an idea:
>
> In fact, MV need to know if its last refresh (transaction id) is older
> than any INSERT, UPDATE, DELETE transaction launched against source
> tables. So if MV has information about last (highest) xmin in source
> tables, it could simply compare its own xmin to xmins (xmax for deleted
> rows) from source tables and decide, if is stale or fresh.
>
> Whole realization could look like this:
> 1. Make new column in pg_class (or somewhere in pg_stat* ?):
> pg_class.rellastxid (of type xid)
>
> 2. 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.

rellastxid would have to be updated at every insert/update/delete. It
would become a big bottleneck. That's not going to work.

Why do you need to know if a MV is stale?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com