Re: How serial primary key numbers are assigned

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@aklaver.com