Обсуждение: How serial primary key numbers are assigned

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.

Regards,

Rich

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

On Thu, 4 Apr 2019, Adrian Klaver wrote:

> See:
> https://www.postgresql.org/docs/11/sql-createsequence.html

Thanks, Adrian.

My web searches did not find this URL, only results on how to set up
automatic serial id generation.

Regards,

Rich

On 4/4/19 11:27 AM, Rich Shepard wrote:
> On Thu, 4 Apr 2019, Adrian Klaver wrote:
> 
>> See:
>> https://www.postgresql.org/docs/11/sql-createsequence.html
> 
> Thanks, Adrian.
> 
> My web searches did not find this URL, only results on how to set up
> automatic serial id generation.

Well serial is basically a macro for:

https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-SERIAL


"CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
"





> 
> Regards,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com