max function to increment a key field

Question

Is it safe to use the max function in order to get the next value of a field like it is being done here?

Custom auto-increment field in postgresql (Invoice/Order No.)

I ask this because I need to generate something similar but will the max function returns always a different value for concurrent users? I undertand that a trigger is being used, but again, I dont understand how a max function may be safe here. If not, what should be used?

thank you!

This seems dangerous, because between one session's read of the MAX and its eventual COMMIT, any other number of sessions could have read the same MAX. Can you use a sequence instead? https://www.postgresql.org/docs/current/static/sql-createsequence.html — 404, Oct 06 '17 at 15:44
so, my point seems correct. I would like to have a counter that starts from 1 for each year. I would like to have this logic on the database. But I dont know how to achieve that with a sequence. If I need something like "year+month+sequence" and if I dont have my key like this, then how could I easily find records if from my application I will have the whole value and not only the sequence number? — user3643038, Oct 06 '17 at 16:37

score 0 · Accepted Answer · answered Oct 06 '17 at 17:19

The method is not safe. There is no guarantee that two concurrent sessions do not get the same max value and will try to insert a row with the same number.

Using a serial column (or a sequence explicitly) is a better option. It ensures that you receive unique values, but does not guarantee that they will be successive, see PostgreSQL - next serial value in a table.

If this last condition must be retained you can use advisory locks to use the max() function for this purpose, e.g.:

begin;
select pg_advisory_xact_lock('my_table'::regclass::bigint);
-- a concurrent session waits here until the transaction completes
insert into my_table (id)
select max(id)+ 1 from my_table;
commit;

You can also use explicit lock in access exclusive mode for the table, however the negative impact of advisory locks on server performance is lower.

max function to increment a key field

1 Answers1