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

Heikki Linnakangas napsal(a):
> 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?
>

Sorry I did not mention it. If we knew MV is fresh, there is no neeed to
refresh MV and so it would prevent useless rebuilding of MV. So I
thought there is room for saving some work.

Anyway, I realized, this idea do not cover all the cases how to find out
MV is stale or fresh. For example, when updating a row of source table
of MV, that do not participate in MV, in that case refreshing of MV
would be useless too.