Hoping someone could clarify a section of this article "scalable-incremental-data-aggregation" and the corresponding question.
Assume Postgres 10 and up.
In Postgres 10, you can use the pg_sequence_last_value function to check the most recently issued sequence number. However, it would not be safe to simply aggregate all events up to the most recent sequence value. There might still be in-progress writes to the events table that were assigned lower sequence values, but are not yet visible when the aggregation runs.
Are there alternatives to pg_sequence_last_value that guarantee the correct seq id?
In a table (call it events) with a BIGSERIAL id, is there a preferred way to fetch the latest seq id for aggregation purposes? It's fine if there are pending inserts in a different transaction (they'll have higher sequence ids) and be picked up on subsequent runs.
The goal is to use a seq id to run aggregation (incremental rollups) on already committed inserts.
Are the following three methods guaranteed to return the latest seq id for committed inserts and avoid the pitfall mentioned in the article relating to pg_sequence_last_value?
Are there other techniques one might suggest?
SELECT max(id) FROM events;
SELECT last_value FROM pg_sequences WHERE sequencename='events_id_seq';
SELECT id FROM events ORDER BY id DESC LIMIT 1;
read committed isolation level