0

I have a database with a unique constraint in a field that is apparently being ignored. That table also includes two rows with the same value for that field, but somehow querying by that value only returns one row.

koji=> select id, name, encode(name::bytea, 'hex') FROM package where id in (7694, 8429);
  id  |            name             |                         encode                         
------+-----------------------------+--------------------------------------------------------
 7694 | python-collectd_certificate | 707974686f6e2d636f6c6c656374645f6365727469666963617465
 8429 | python-collectd_certificate | 707974686f6e2d636f6c6c656374645f6365727469666963617465
(2 rows)

koji=> select * from package where name='python-collectd_certificate';
  id  |            name             
------+-----------------------------
 8429 | python-collectd_certificate
(1 row)

koji=> \d+ package
                                                Table "public.package"
 Column |  Type   | Collation | Nullable |               Default               | Storage  | Stats target | Description 
--------+---------+-----------+----------+-------------------------------------+----------+--------------+-------------
 id     | integer |           | not null | nextval('package_id_seq'::regclass) | plain    |              | 
 name   | text    |           | not null |                                     | extended |              | 
Indexes:
    "package_pkey" PRIMARY KEY, btree (id)
    "package_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
...

I'm totally confused, what on earth is going on here? How is this even possible? Is this DB totally corrupted now?

1
  • Are they the same though? What does select distinct name from package where name like 'python%certificate' ; tell you?
    – gsiems
    Commented Dec 9, 2022 at 14:46

1 Answer 1

0

That's a clear case of data corruption. Your index is broken, perhaps because you updated the C library. You will have to manually remove the duplicates, for example with

DELETE FROM package WHERE id = 7694;

Once all duplicates are removed,

REINDEX INDEX CONCURRENTLY package_name_key;
2
  • You're probably right, but this is just ASCII text, so it couldn't have been any sort of ordering change? Should I start suspecting cosmic rays or aliens here?
    – Alex
    Commented Dec 9, 2022 at 11:29
  • Cosmic rays are a possibility, aliens are unlikely. It might be connected to the - signs. Commented Dec 9, 2022 at 11:43

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.