2013/5/17 Kevin Grittner <kgri...@ymail.com>: > During calculation of the deltas to apply to the matviews, it must > be possible to query the referenced tables from the perspective of > both the "before" and "after" versions of the data.
[..] > I don't think the process applying the deltas needs to do > anything unusual or special about acquiring snapshots as it > processes the delta from each transaction, unless we see some way > to optimize things once we're hip-deep in the details. Note that the basic count algorithm assumes real-serializable transactions for correctness. Example: * The example matview’s defining query is a simple join: SELECT * FROM A JOIN B ON A.a = B.b. * For simplicity, assume that the matview is initially empty. * Assume a non real-serializable isolation mode, e.g., REPEATABLE READ. * Assume that there are two transactions T1 and T2 running in parallel, that don’t see each other’s changes. * Both T1 and T2 insert a row in A and B, respectively, in such a way that the combination of the rows is matched by the join condition (i.e., the row inserted by T1 has A.a equal to the B.b of the row inserted by T2). * The count algorithm will not add anything to the matview as part of the execution of T1 (because the new row in A doesn’t join with any rows in B that T1 observes). * Idem for T2 (nothing will be added). * After T1 and T2 completed, a new transaction will see an inconsistency: A and B contain rows that are supposed to result in a row in the matview, but it doesn’t contain any. Each of the following would fix this problem: (1) Real-serializability must be required for all transactions that update any base table of a matview’s that is maintained using the count algorithm. (2) The matview’s defining query and the corresponding base tables must adhere to certain (very commonly satisfied) conditions (for example, if there is an FK between A.a and B.b, the example cannot be made to fail; The FK must not be deferred in the case of right-after-each-statement matview maintenance). (3) The count algorithm must be implemented in a way that understands MVCC internals: Reading the base tables must be done using a technique that reads all rows (i.e., also the ones not visible to the current transaction), and the xmin/xmax/etc of the updated rows in the matview must be set in a smart way, so as to yield visibility results that correspond to the visibility of the “originating” rows in the base tables. Calculation of the matview deltas (especially the part where the base tables are inspected for joining rows) must in this case probably be done in a serialized manner. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
