On 4/4/19 11:04 AM, Rich Shepard wrote:
Just out of curiosity, how does postgres assign serial primary key
identifiers when rows are inserted in multiple, separate working sessions?
I'm populating tables using INSERT INTO statements in separate working
sessions and see that the assigned id numbers are sequential in each
session, but have large gaps from one session to the next. I don't care
what
id numbers are assigned yet would like to know how the starting number for
each session is determined, just because I expected the numbers to be
continuous.
See:
https://www.postgresql.org/docs/11/sql-createsequence.html
The relevant part:
"Unexpected results might be obtained if a cache setting greater than
one is used for a sequence object that will be used concurrently by
multiple sessions. Each session will allocate and cache successive
sequence values during one access to the sequence object and increase
the sequence object's last_value accordingly. Then, the next cache-1
uses of nextval within that session simply return the preallocated
values without touching the sequence object. So, any numbers allocated
but not used within a session will be lost when that session ends,
resulting in “holes” in the sequence."
Regards,
Rich
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
