PostgreSQL insert not occurring serially

Question

I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key)

So if, for example I have ids like 1521, 1522, 1527, the id of the next insert will be 1523 and not 1528 as I expected and would like to occur.

The missing ids may be due to the fact that the database that was migrated from MySQL. The id has the following definition: Data type: bigint Default value: nextval('fleetmanager.workorderjob_id_seq'::regclass) Not null Storage: Plain

I use PostgreSQL version 10 on Windows.

How can I get it to always do insert after the last id?

Answer 1

There is absolutely nothing wrong with Postgres to act like this and it has a reason. According to Postgres's documentation (in Notes section):

sequence objects cannot be used if "gapless" assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.

And again in another page it says:

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Such cases will leave unused "holes" in the sequence of assigned values. Thus, PostgreSQL sequence objects cannot be used to obtain "gapless" sequences.

Answer 2

Looks like there is wrong current value of sequense. Alter your sequence to max value in table:

SELECT setval('fleetmanager.workorderjob_id_seq', max(id)) FROM 'fleetmanager.workorderjob;