How to Fix a Sequence When It Goes Out of Sync in Postgres | by Arctype | Feb, 2022

Postgres is among the most superior and broadly used open-source RDBMS (Relational Database Administration Methods) on the earth. It’s significantly appreciated by the developer group as a result of it helps each SQL and JSON querying, making it’s each relational and non-relational compliant.

But, Postgres has some well-known points, and probably the most annoying ones entails sequences. Particularly, Postgres sequences are susceptible to exit of sync, stopping you from inserting new rows. Postgres even returns a complicated error message when this occurs.

On this article, we’ll train you concerning the out-of-sync challenge and present you methods to resolve it. We are going to find out about sequences, see what circumstances result in the out-of-sync challenge, discover ways to diagnose the issue, and at last resolve it. Let’s start.

What’s a Postgres sequence?

The official documentation explains {that a} sequence is nothing greater than a quantity generator. Specifically, Postgres makes use of a sequence to generate a progressive quantity, which normally represents an robotically incremented numeric main key.

In case you are conversant in MySQL, the results of having a sequence in Postgres is corresponding to the AUTO_INCREMENT habits. The principle distinction is that the Postgres sequences may also begin from an outlined worth after which decrement it at every INSERT.

You possibly can outline a sequence in Postgres with the CREATE SEQUENCE assertion. Equally, the particular sort SERIAL initializes an auto-incremental numeric main key utilizing a sequence behind the scene. Typically, you need to use SERIAL when creating a brand new desk with CREATE TABLE.

When do sequences exit of sync?

Primarily based on my expertise as a senior software program developer, there are three the reason why a Postgres sequence can exit of sync. Particularly, this occurs when:

• importing many rows with an INSERT script or restoring an intensive database;
• manually setting the improper worth of a sequence with the setval() perform;
• inserting a brand new file right into a desk by manually specifying the id subject within the INSERT question.

The final one is the commonest trigger and normally happens as a result of Postgres makes use of a sequence and robotically updates its worth solely once you omit the id subject or use the DEFAULT key phrase within the INSERT question.

How do I determine this challenge?

Recognizing this challenge is easy. While you specify no worth for the id column and manually launch an INSERT question or let your ORM (Object-Relational Mapping) do it for you, Postgres will at all times return a “duplicate key worth violates distinctive constraint” error. Additionally, it will by no means occur when performing UPDATE queries.

As you possibly can think about, the issue lies within the sequence associated to the id column that went out of sync. Particularly, the error occurs when the sequence returns a worth for the id column that’s already in use. And this results in the aforementioned error as a result of an ID have to be distinctive by definition.

It’s now time to see methods to handle the out-of-sync drawback and resolve it as soon as and for all. This drawback might be addressed in a couple of methods. Listed here are two approaches.

Technique 1: Single desk resolution

Suppose you need to repair the sequence related to the id column of your Customers desk. You possibly can obtain this by operating the next question:

SELECT SETVAL('public."Users_id_seq"', COALESCE(MAX(id), 1)) FROM public."Customers";

Such question will replace the Users_id_seq sequence by setting its present worth to the results of COALESCE(MAX(id), 1). Discover how the identify of the sequences in Postgres follows this notation:

"<table-name>_<column-name>_seq"

The COALESCE perform returns the primary non-null worth, and it’s required as a result of if Customers have been empty, MAX(id) would return NULL. So, through the use of COALESCE, you’re positive that the worth assigned to Users_id_seq will likely be MAX(id) when Customers isn’t null, and 1 when Customers is null. In each instances, that question units the specified worth.

Technique 2: Fixing all of your sequences with one script

In the event you wished to repair all of your sequences with one question, you could possibly use the next script coming from the official Postgres Wiki:

SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;

This question returns the set of queries required to repair every of your sequences when executed. As said within the Wiki, you need to use this question as follows:

1. Save the question in a fix_sequences.sql file.
2. Run the question contained within the fix_sequences.sql file and retailer the end in a temp file. Then, run the queries contained within the temp file. Lastly, delete the temp file. You possibly can obtain this with the next three instructions:

bash
psql -Atq -f fix_sequences.sql -o temp
psql -f temp
rm temp

Now, all it’s a must to do to confirm that your sequence is now not out-of-sync is to insert a brand new file in the identical desk the place you initially skilled the difficulty.

Keep in mind to present the id column the DEFAULT worth or omit it completely within the INSERT question. This manner, Postgres will use the Users_id_seq sequence behind the scene to retrieve the right worth to present to id.

For instance, run the next question, and you need to now not obtain the “duplicate key worth violates distinctive constraint” error message:

INSERT INTO "Customers"("id", "identify", "surname", "e mail", "password")
VALUES (DEFAULT, 'Jennifer', 'Jones', 'jennifer.jones@email-domain.com', 'pBHxe*cWnC2ZJKHw');

As an alternative, this question will insert a brand new file within the Customers desk as anticipated.

Postgres is undoubtedly an excellent RDBMS. On the similar time, it has a couple of points that may waste your time.

That is very true should you aren’t conscious of them, don’t know methods to determine them, and don’t handle them accordingly. On this article, we appeared on the tough out-of-sync challenge.

Out-of-sync sequence errors are tough as a result of it results in a “duplicate key worth violates distinctive constraint” message error, which can be related to different issues. However that gained’t idiot you anymore, as a result of now you understand why it happens, methods to detect it, and methods to repair it!