OS : SUSE Linux 15 sp3,
postgresql 13.2
When executing a postgresql SQL statement, first check if the primary key does not exist and then insert data, resulting in an error.
image_uuid is the primary key of the image table.
The table has more than 60 columns of data. Here, only the first two columns are written to simplify them. Apart from these, the above statement is a complete SQL statement. The simplified statement is as follows:

INSERT INTO image ( image_uuid, image_series_uuid )
SELECT 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f', '85c4a85a-10de-47d3-8af5-1c090e119a86' 
WHERE NOT EXISTS (
     SELECT 1 FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f'
);

The error message is as follows:

duplicate key value violates unique constraint "image_pkey"
--------
DETAIL:  Key (image_uuid)=(d9aaf41a-9c88-488b-b371-0c0bb1165a4f) already exists.
SCHEMA NAME:  public
TABLE NAME:  image
CONSTRAINT NAME:  image_pkey
_bt_check_unique, nbtinsert.c:656

The database service backend is executing checkpoint activities.
The above SQL statements are in a transaction. In one transaction, 200~300 similar records are inserted at the same time. This transaction usually executes tasks that 40~80 milliseconds, but it takes more than 4 seconds when the above error occurs.

OS : SUSE Linux 15 sp3,
postgresql 13.2
When executing a postgresql SQL statement, first check if the primary key does not exist and then insert data, resulting in an error.
image_uuid is the primary key of the image table.
The table has more than 60 columns of data. Here, only the first two columns are written to simplify them. Apart from these, the above statement is a complete SQL statement. The simplified statement is as follows:

INSERT INTO image ( image_uuid, image_series_uuid )
SELECT 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f', '85c4a85a-10de-47d3-8af5-1c090e119a86' 
WHERE NOT EXISTS (
     SELECT 1 FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f'
);

The error message is as follows:

duplicate key value violates unique constraint "image_pkey"
--------
DETAIL:  Key (image_uuid)=(d9aaf41a-9c88-488b-b371-0c0bb1165a4f) already exists.
SCHEMA NAME:  public
TABLE NAME:  image
CONSTRAINT NAME:  image_pkey
_bt_check_unique, nbtinsert.c:656

The database service backend is executing checkpoint activities.
The above SQL statements are in a transaction. In one transaction, 200~300 similar records are inserted at the same time. This transaction usually executes tasks that 40~80 milliseconds, but it takes more than 4 seconds when the above error occurs.

• sql
• postgresql

• 3 What does the transaction contain? May there be other, 4 seconds long transactions running on image while you run this one, inserting the same IDs (then you could try an ON CONFLICT DO NOTHING instead of WHERE NOT EXISTS (…))? Is that really your query (or is it a simplified form, but the real query gets its values from another table that could have duplicates)? – Guillaume Outters Commented 2 days ago
• 1 It seems impossible that query you are showing produces this error. Maybe you simplified too much. – Thorsten Kettner Commented 2 days ago
• @ThorstenKettner That can easily happen if the same statement runs in two concurrent database sessions at the same time. – Laurenz Albe Commented 2 days ago
• The table has more than 60 columns of data. Here, only the first two columns are written to simplify them. Apart from these, the above statement is a complete SQL statement. When inserting, each statement inserts one record. In one transaction, 200~300 similar records are inserted at the same time. – Chaoyang.Wang Commented 2 days ago
• @Chaoyang.Wang Why do you run 300 single-row inserts instead of a single query with all of them? – Zegarek Commented 2 days ago

1 Answer 1

1. Your inserted batch conflicts with itself (client attempts to insert more than 1 such image_uuid at a time so there's no existing conflict in the table before this insert). This won't work even on an empty table:

   INSERT INTO image ( image_uuid, image_series_uuid )
   SELECT*FROM(VALUES
     ('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'85c4a85a-10de-47d3-8af5-1c090e119a86'::uuid)
    ,('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'8a10fa91-faab-406c-bc79-4dcc03ad3d75'::uuid))_
   WHERE NOT EXISTS (
     SELECT FROM image WHERE image_uuid='d9aaf41a-9c88-488b-b371-0c0bb1165a4f');
   

   Example above only for illustration - it's intuitive to try and make that work with a CTE/source table and an anti-join against it. The construct will only work if it also specifies a condition that filters out incoming duplicates from the batch. For example, using distinct on:

   INSERT INTO image ( image_uuid, image_series_uuid )
   SELECT DISTINCT ON(1) * 
   FROM(VALUES
     ('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'85c4a85a-10de-47d3-8af5-1c090e119a86'::uuid)
    ,('d9aaf41a-9c88-488b-b371-0c0bb1165a4f'::uuid,'8a10fa91-faab-406c-bc79-4dcc03ad3d75'::uuid))_
   WHERE NOT EXISTS (
    SELECT FROM image WHERE image_uuid = 'd9aaf41a-9c88-488b-b371-0c0bb1165a4f')
   ORDER BY 1;
   

2. You have concurrent clients attempting to insert the same image_uuid. In PostgreSQL 15+ you can use insert..on conflict, called an upsert.

   INSERT INTO image ( image_uuid, image_series_uuid )
   VALUES('d9aaf41a-9c88-488b-b371-0c0bb1165a4f','85c4a85a-10de-47d3-8af5-1c090e119a86')
   ON CONFLICT DO NOTHING;
   

3. If the workers/agents/clients that process these image_uuids get them from this db and you didn't expect them to be able to grab the same one, use locking to make sure this doesn't happen.

   begin;
   select image_uuid
   from src_table
   where not is_processed
   limit 1
   for update skip locked;
   --...
   update src_table 
   set is_processed=true
   where image_uuid={v};
   commit;
   

_{demo at db<>fiddle}