Geek Answers Handbook

Why do SQL identifier sequences go out of sync (in particular using Postgres)?

I have seen solutions to update the sequence when it is not synchronized with the primary key that it generates, but I do not understand how this problem occurs in the first place.

Does anyone know how a primary key field with its default value, following the nextval sequence, whose primary keys are not set explicitly anywhere, can go out of sync with the sequence? I use Postgres, and we see that this happens from time to time. As a result, this leads to duplication of the key constraint when the sequence creates an identifier for an existing row.

+8
sql postgresql sequence
source share
2 answers

Your application probably sometimes sets a primary key value for a new line. Then postgresql does not need to get a new sequence, and the sequence is not updated.

+8
source share

When the sequence number is highlighted, it remains highlighted, even if the TX that requested it is rolled back. In this way, a number that is not displayed in a stable database can be allocated. Of course, rows can also be deleted after they are created, so the maximum number found in the table does not have to be the maximum number ever allocated. This applies to any type of auto-increment.

In addition, depending on the technology used, individual sequences can be used with several tables, so the value may not be in table A, but present in TableB. This may be due to a mistake in using sequence names or it may be intentional.

0
source share

More articles:


All Articles