3

I have a query that I've been successfully running since Postgres added upsert support, I believe that was 9.6:

INSERT INTO gitrefresh(projecttag, state, analysis_started, counter_requested, customer_id) 
VALUES('npm@randombytes', 'Q', NOW(), 1, 0) 
ON CONFLICT (projecttag) DO UPDATE SET state='Q';

Out of nowhere, I started getting this error, I'm on 14.5 now:

duplicate key value violates unique constraint "idx_projecttag"
DETAIL:  Key (projecttag)=(npm@randombytes) already exists.

In the server log there's almost the same:

ERROR:  duplicate key value violates unique constraint "idx_projecttag"
DETAIL:  Key (projecttag)=(npm@randombytes) already exists.
STATEMENT:  INSERT INTO gitrefresh(projecttag, state, analysis_started, counter_requested, customer_id) VALUES                 ('npm@randombytes',         'Q',    NOW(),             1,                0) ON CONFLICT (projecttag) DO UPDATE SET state='Q'

I don't understand what's going on, it's acting as if it didn't use the ON CONFLICT part.

The table in question:

 \d+ gitrefresh
+-------------------+-----------------------------+----------------------------------------------------------+----------+--------------+-------------+
| Column            | Type                        | Modifiers                                                | Storage  | Stats target | Description |
|-------------------+-----------------------------+----------------------------------------------------------+----------+--------------+-------------|
| projecttag        | text                        |                                                          | extended | <null>       | <null>      |
| state             | character(1)                |                                                          | extended | <null>       | <null>      |
| analysis_started  | timestamp without time zone |                                                          | plain    | <null>       | <null>      |
| analysis_ended    | timestamp without time zone |                                                          | plain    | <null>       | <null>      |
| counter_requested | integer                     |                                                          | plain    | <null>       | <null>      |
| customer_id       | integer                     |                                                          | plain    | <null>       | <null>      |
| id                | integer                     |  not null default nextval('gitrefresh_id_seq'::regclass) | plain    | <null>       | <null>      |
+-------------------+-----------------------------+----------------------------------------------------------+----------+--------------+-------------+
Indexes:
    "pk_gitrefresh" PRIMARY KEY, btree (id)
    "idx_projecttag" UNIQUE CONSTRAINT, btree (projecttag)
Has OIDs: no
GDR
  • 141
  • 1
  • 6

1 Answers1

1

As found by user ypercubeᵀᴹ, the problem was with a corrupted index. This happens when upgrading your operating system to one with glibc 2.28 - such as Ubuntu 18.04, Debian 10 or CentOS/RHEL 8.

This query will find the indices possibly affected by the problem:

SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s
 JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');

For the affected indices, an index rebuild can be performed with:

REINDEX CONCURRENTLY idx_name;

For me that didn't work - because of the broken index, there were duplicate keys in the table. A SELECT did not find all the duplicate keys. First I had to do:

VACUUM FULL table_name

Then find duplicate rows, delete them, and rebuild the indices. It may be convenient to you to use the table name instead of index name:

REINDEX TABLE CONCURRENTLY table_name;
GDR
  • 141
  • 1
  • 6