Postgres latest sequence id and uncommitted writes

All we need is an easy explanation of the problem, so here it is.

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;

Assume read committed isolation level

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

select max(id) is the most reliable way.

It’s also quite fast if you have a b-tree index on id (performance is virtually independent of the size of the table).

Method 2

One of ways to do this is to associate updates with a transaction id.
In this case all updates below xmin can be considered committed or rolled back. See the documentation:

txid_current() bigint get current transaction ID, assigning a new one
if the current transaction does not have one

txid_current_snapshot() txid_snapshot get current snapshot

txid_snapshot_xmin(txid_snapshot) bigint get xmin of snapshot

xmin Earliest transaction ID (txid) that is still active. All earlier
transactions will either be committed and visible, or rolled back and
dead.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply