Prev: pg_upgrade output directory
Next: [HACKERS] Patch to show individual statement latencies in pgbench output
From: Pavel Baros on 12 Jun 2010 10:17 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. 3. When refreshing, as described above, MV rellastxid compared to source tables rellastxid could answer if MV is stale or still fresh. Decision, if to run refreshing, would be as simple as it can. a) Is the idea right? b) Could appear some cases when it is not true? (except xid wraparound). c) I was looking for some help with it in pg_stat*, but there is no information about transactions, last changes in relations or anything. d) or there are other mechanisms or ideas how to check if MV source tables are changed from last refresh? Thanks for replies Pavel Baros |