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.