0

I have a simple table as below in PostgreSQL 13:

table name: newtable1 

field       type 
-----       ----    
Seq         bigserial   
code        varchar     

Seq is the primary key (auto-increment)
Code is a unique key index

Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing   --> Seq value is 1
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing

Insert Into newtable (Code) Values ('002') On Conflict(Code) Do Nothing   --> Seq value is 4

Question

Why does the Seq value keep increasing?
Is there any way to only increase the Seq value if it is successfully inserted?

1
0

Why does the Seq value keep increasing?

The reason is that DEFAULT values (and triggers and anything else that might change row values) are applied before checking for duplicates (trying to enter index tuples). And serial numbers are designed to defend against race conditions under concurrent load. The underlying SEQUENCE does not "take back" numbers once it has been incremented. There are other scenarios that would burn serial numbers. So gaps in serial numbers are to be expected. As long as you don't burn numbers at a gigantic rate, this should not be a problem.

Is there any way to only increase the Seq value if it is successfully inserted?

Not without (more or less) seriously compromising performance, like by using SERIALIZABLE transaction isolation or manual locking stategies. That's the reason why the ON CONFLICT clause ("UPSERT") exists in the first place.

If you don't actually have concurrent writes to the same table (are you sure?), this alternative query would avoid burning serial numbers:

INSERT INTO newtable (code)
SELECT '001'
WHERE  NOT EXISTS (SELECT FROM newtable WHERE code = '001';

It's slightly more expensive in the non-conflicting case as it first checks for existence in the index and then actually enters the new row in table and index(es). But slightly faster for conflicting cases. There is a tiny window between checking and writing where race conditions can cause problems under concurrent write load. That's when we use an UPSERT.

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.