PostgreSQL – Latest Sequence ID and Uncommitted Writes

postgresqlsequence

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

Best Answer

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).

Related Solutions

Polling for new records in a table

Some of the implementation specifics depend on exactly what type of behaviour you need from this table (e.g. FIFO), but a typical design for a queue table uses DELETE with an OUTPUT clause:

DELETE TOP (n)
FROM QueueTable WITH (ROWLOCK, READPAST)
OUTPUT deleted.RecordID;

Or if ordered removal is required:

DELETE FROM
(
    SELECT TOP (n) RecordID
    FROM QueueTable WITH (ROWLOCK, READPAST)
    ORDER BY RecordID
) AS DF
OUTPUT deleted.RecordID;

If the records should remain in the table after processing, the table would have an extra columns to record the status (e.g. New, Processing, Processed) and probably the process id owning the row (if Processing or Processed). The de-queuing code is then modified to choose only records with the desired status. An alternative design is to DELETE rows and move them to a separate audit/archive table at the same time. As I mentioned, the specific solution depends on the precise requirements.

Postgresql – Postgres sequence usage

The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

Either execute the GRANT command as the role owning the sequence.
Or do the same as superuser (default superuser is postgres).
Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:

GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...

Best Answer

Related Solutions

Polling for new records in a table

Postgresql – Postgres sequence usage

Related Question