9

I have the below table in PostgreSQL 13:

table name: newtable

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

Why is Seq 4? Is there any way to increase value on successful inserts only?

0

2 Answers 2

4

With PostgreSQL 15 comes the MERGE command. Inserts made using this command don't burn sequence numbers.

CREATE TABLE newtable (
  seq INT GENERATED ALWAYS AS IDENTITY,
  code varchar UNIQUE
);

Run the following statement a couple of times, then change value 001 to 002 and repeat. Value 002 gets the next available seq number assigned.

MERGE INTO newtable n
USING (SELECT '001' AS code) AS u
ON (n.code = u.code)
WHEN MATCHED THEN
UPDATE SET
code = u.code
WHEN NOT MATCHED THEN
INSERT (code)
VALUES ('001');         

SELECT * FROM newtable ORDER BY 1;

seq code
1   001
2   002
1
  • @Don2: Note that MERGE is not an equivalent replacement. I added some explanation to my answer to. Commented Jan 11 at 1:59
9

Use MERGE instead?

MERGE (added with Postgres 15) looks similar on the surface, but is quite different under the hood. The manual:

When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

In particular, MERGE cannot reliably suppress unique violations under concurrent write load, like INSERT ... ON CONFLICT DO NOTHING can (the command in question). The manual:

If MERGE attempts an INSERT and a unique index is present and a duplicate row is concurrently inserted, then a uniqueness violation error is raised; MERGE does not attempt to avoid such errors by restarting evaluation of MATCHED conditions.

A minor (and typically unimportant) side effect of the different approach is that MERGE does not normally burn serial numbers. (It still can, like when an exception is raised ...)

If you don't have concurrent write load, or if the discussed issues don't matter to you, then MERGE can be an alternative. (Then you probably didn't need INSERT ... ON CONFLICT DO NOTHING in the first place.) Rudi added an answer with a code example.

Your questions about INSERT ... ON CONFLICT DO NOTHING

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 strategies. 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 and acknowledge you have read our privacy policy.

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