Guarantee monotonicity of PostgreSQL serial column values by commit order

Question

My team needs a serial column to increase monotonically with each commit.

There are cases in which two transactions get values 100 and 101 from a sequence, then the 100 transaction takes longer to commit, so the value 101 gets committed first, followed by 100. This situation is problematic for our needs, and we need to solve for it.

This is exactly the problem described by this question. We need a solution that does not require changes to the database configuration, unlike the accepted answer to that question.

A comment on that solution, as well as this post, suggest using an exclusive transactional advisory lock starting just before the value is acquired.

Is there a way to have Postgres automatically acquire this lock and fetch a value from a sequence when it gets an INSERT to the table in question?

Note: gaps in the committed values are OK and expected.

EDIT: I have dug into this question enough to be able to ask it well, but I am not very experienced with Postgres. I'm hoping for a pointer to a specific trigger or whatever specific PG setup will accomplish this.

Answer 1

I know you wanted an automatic locking, I would advise against that, you might be able to use stored procedures or triggers for that, but what else is a stored procedure than code. Please see also my comment.

My solution would be to:

My team needs a serial column to increase monotonically with each commit.

Does that mean,

• that the insertion of a value less then the maximum value already stored is not allowed?
• gaps in the values of that column are not allowed

I suppose you use a sequence for creation of this value. Then immediately before the commit you should acquire a specific advisory lock see 13.3.4 and now do your insertion either use the sequence implicitly in your schema or explicitly by querying in your insertion. No other commit of a transaction trying to acquire the same lock can get between the locking and the commit so the insertion must be sequential. Doing the locking and incrementing at the end of the transaction helps in that, to keep the time short and prevent deadlocks. The lock will be released together with the commit and the next transaction may acquire it, and will get the next value of the sequence.

Answer 2

We've had to deal this as well, and we went for the stored procedure solution:

CREATE OR REPLACE FUNCTION proc_set_primary_key()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    base_index BIGINT;
    update_count INTEGER;
BEGIN
    PERFORM pg_advisory_xact_lock(42);
    base_index := NEXTVAL('id_sequence');
    UPDATE event SET id = base_index - id - 1 WHERE id < 0;
    GET DIAGNOSTICS update_count = ROW_COUNT;
    PERFORM SETVAL('id_sequence', base_index + update_count - 1);

    RETURN NULL;
END;
$$;

CREATE CONSTRAINT TRIGGER set_commit_order
    AFTER INSERT ON event
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN ( new.id = -1 )
EXECUTE PROCEDURE proc_set_primary_key();

When doing (batch) INSERTs number all your id's with temporary negative values starting from -1. The stored procedure will renumber them just before commit. This creates id's in commit order and preserves the exact INSERT order, but is not gapless.

Note that each transaction always starts numbering id's from -1. so if you never insert more than one record per transaction, always insert it with -1.

Be careful if you need to know the value of the id's afterwards, as they're changed from their temporary values. A framework like Hibernate for example may need to have its cache cleared of these records.

Although it may seem that multiple concurrent transactions would have conflicting negative id's, as they're not visible to other transactions and as they're resolved to real id's before commit, this does not cause any problems.