Understanding the First Numbering of PostgreSQL Sequence

Asked 1 years ago, Updated 1 years ago, 429 views

Question Summary

In the sequence of PostgreSQL, we believe it will be numbered with the current value of +1 (if INCREMENT is 1).
So the next time it's INSERTed, it's numbered 5+1 to 6.
However, in the first INSERT, it is strange that the INSERT is performed at 1 even though the current value is 1 and the INCREMENT is 1.Considering the regularity, I feel like I'm INSERTed at 2.

Reproduction Steps

Suppose you created a sequence in PostgreSQL with the following SQL:

CREATE SEQUENCE test_seq
    INCREMENT 1
    START1
    MINVALUE1;

If you look at the last_value in the following SQL, you will see 1.

SELECT* FROM test_seq; 

Then, when you INSERT a record, the id is numbered with 1 (as last_value+1=2, it is not regular).
Now, if you check the last_value again, it remains 1 (last_value has not been updated)

When you INSERT the second item, the id is numbered with 2 (because it is last_value+1, it is regular).
Then, if you check the last_value again, it will be 2 (last_value has been updated correctly).

This second and subsequent cases will continue regularly.In other words, only the first case is moving against regularity.

However, it is not mechanically INSERTed with last_value+1, and in the case of the first INSERT, is it defined as an exception such as putting the last_value as it is?
If so, how do you determine if the INSERT is the first one?

Could you please tell me the literature or the structure of the document that can confirm this?
Thank you for your cooperation.

PostgreSQL14

I'm sorry if there are any questions.In that case, I will correct it.

postgresql database

2022-12-13 23:31

2 Answers

The sequence has a cache value.Default 1 for PostgreSQL.
Create this as 10 and each of the two sessions

select nextval('test_seq3'); 

and

SELECT* FROM test_seq3;

Try running to see how it works.
I think I can understand.

CREATE SEQUENCE test_seq3
    INCREMENT 1
    START2
    MINVALUE1
    CACHE 10
;

The sequence cache values can also be found in SQL below.

select 
 c. relowner::register as owner
, c. relnamespace::regnamespace as schema
, c.relname
, s.seqtypid::regtype
, s.seqstart, s.seqinclement, s.seqmax, s.seqmin, s.seqcache, s.seqcycle
, c. reltype::regtype
,array_to_string(relacl,',')relacl
from pg_sequences 
inner join pg_class con s.seqrelid = c.oid
where c.relname = 'test_seq3'
;


2022-12-13 23:31

YumaInaura Yuma Inaura's comments helped solve the problem.

The sequence has the flag is_called and until the first call is_called=false, it is understood that 1 is returned exceptionally, and is_called=true is returned regularly from 2 after the second call.

Engineer Tips NotesPostgreSQL Sequence Operations

However, there is something to be careful about here.That is how to set the current value to 1.
For example, if the current value is set to 1, as shown above, the ID of the next allocated flower will be 2.
SELECT SETVAL('Flower_id_seq',1)
This is because one of the columns in the sequence, isis_called が, is ttrue. に
isis_called は is a flag that indicates whether llast_value が has already been called, which is true if called or false if not already called.
The sequence calls llast_value を at the time of creating the serial number because the current value must be known in order for the sequence to generate a serial numbers.In the early stages of , がis_called 」 is true.If isis_called が is false, it means that the sequence has not yet generated a serial number, that is, there is no data to be serialized yet.
In other words, even if the same current value ("last_value") is 1, it is divided into true and true of "is_called" and whether there is data with a value of 1 assigned.

Nextval Behavior of PostgreSQL Sequences

If you specify start with 1 in the create sequence, you will get 1 when you first do nextval().
So far, I wanted to reset the value, so I did setval('hoge',1) and then I did nextval('hoge') and got 2 back, and I was like, "Huh!?"
Looking at each condition, the value of is_called is false immediately after the create sequence, but true immediately after the set value.

SQL for Verification

 --Verifying
SELECT* FROM test_seq;

-- set
SELECT setval('test_seq',1,true);
-- numbering
select nextval('test_seq'); 

-- set
SELECT setval('test_seq',1,false);
-- numbering
select nextval('test_seq'); 


2022-12-14 07:02

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.