Stop me if you've heard this before.
I'm looking at fast calculation of aggregates (sum(), max(), count()) across large tables, or across fairly simply defined subsets of those tables.
Lets say that, for a given aggregate function on a given table (with a given where clause, perhaps), each postgres process maintains a state variable (stype, in aggregate terms) and there's a also a single state variable available to all backends via shared memory.
Each time a transaction starts the process initialises its local state variable to the initcond of the aggregate. Each time a row is inserted into the table the local state variable is updated, using the aggregate update function. Each time a row is removed then the local state variable is either updated, or invalidated, using a "reverse-update" function, again specific to the aggregate.
If the transaction is rolled back, the local state variable is thrown away. If the transaction is commited and the local state variable has been invalidated then the global state variable is invalidated, otherwise the global state variable is updated using a a state merge function, specific to the aggregate.
Isn't this going to have visibility issues wrt other backends? How do I know what transactions have updated the global and what haven't and which I should currently be seeing?
I'm not sure that there is a solution simpler than the "insert +1/-1 into summary table" that gets discussed.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
