GSoC - Materialized Views - is stale or fresh?
in [PgSql]
|
Prev: visibility map
Next: [HACKERS] Typo in plperl doc ?
From: =?ISO-8859-2?Q?Pavel_Baro=B9?= on 14 Jun 2010 08:47 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Josh Berkus on 14 Jun 2010 14:23
> At the risk of sounding obsessed, this is an area where predicate > locks might be usefully extended, if and when the serializable patch > makes it in. Yes, we see your patch in 9.1-first. ;-) -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |