On: Materialized views vs. Rollup tables in Postgres

[zerd]

You have to be careful if you use the "rollup" way, and the event_id is assigned by a sequence. If you have two transactions, T1 and T2, and T1 inserts event_id 1000, T2 inserts event_id 1001, but T2 commits first and you run the rollup before T1 has committed you'll miss the row.

We store the txid_current() on each row, and when getting new rows we get from the previous txid up to txid_snapshot_xmin(txid_current_snapshot()).

[ehfeng → zerd]

They actually link to another blog post (https://www.citusdata.com/blog/2018/06/14/scalable-increment...) that solves this with pg_sequence_last_value().

I personally couldn't find this function, but I believe they might be talking about lastval (https://www.postgresql.org/docs/current/functions-sequence.h...).