Where do negative PostgreSQL primary key index values ​​come from?

Question

PostgreSQL

The following question appeared in terms of working with PostgreSQL through the Entity Framework.

There is the following table:

CREATE TABLE "TankContentStateHistory" (
"TankContentStateHistoryId" integer NOT NULL SET DEFAULT nextval('"TankContentStateHistory_TankContentStateHistoryId_seq"'::regclass),
"AverageTemperature" double precision NOT NULL,
...
"GTVolume" double precision DEFAULT 0.0 NOT NULL);

TankContentStateHistoryId takes its values ​​from the TankContentStateHistory_TankContentStateHistoryId_seq enum:

CREATE SEQUENCE "TankContentStateHistory_TankContentStateHistoryId_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

Values ​​should be positive all the time, but recently there have been moments when the primary key becomes negative and where it comes from is not clear. The PostgreSQL logs are not clear:

2021-09-19 00:45:58 MSK ОШИБКА:  повторяющееся значение ключа нарушает ограничение уникальности "PK_TankContentStateHistory"
2021-09-19 00:45:58 MSK ПОДРОБНОСТИ:  Ключ "("TankContentStateHistoryId")=(-2147482602)" уже существует.
2021-09-19 00:45:58 MSK ОПЕРАТОР:  INSERT INTO "TankContentStateHistory" ("TankContentStateHistoryId", ... "GTVolume")
VALUES ($1, ..)

EntityFrameworkCore essentially duplicates the same thing:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23505: повторяющееся значение ключа нарушает ограничение уникальности "PK_TankContentStateHistory"
в Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()

The question is, where is the problem? Because after vain attempts to set the value, all the next key values ​​go further in the sequence (for example, id = 2044, then a negative value id=-454635, after id=2045).

Answer 1

https://stackoverflow.com/questions/41732585/ef-co...

Answer 2

Why not use the built-in type serial instead of TankContentStateHistory_TankContentStateHistoryId_seq

CREATE TABLE "TankContentStateHistory" (
  "TankContentStateHistoryId" serial NOT NULL,
  "AverageTemperature" double precision NOT NULL,
  "GTVolume" double precision DEFAULT 0.0 NOT NULL
);

INSERT INTO "TankContentStateHistory" ("AverageTemperature", "GTVolume") VALUES (10, 20);

SELECT * FROM "TankContentStateHistory";

PostgreSQL fiddle

Answer 3

Negative values ​​are a sign that your counter "went to the second round".
Change to BigInt.
In general, this fact and gaps in the continuous numbering of the index indicate, perhaps, a problematic algorithm that intensively uses this table in "temporary" calculations.