PostgreSQL: duplicates despite having a unique constraint

Question

We have a table in the production DB that looks like this:

create table domains
(
 domain_id serial not null
  constraint domains_pkey
   primary key,
 domain_name text not null
  constraint domains_domain_name_key
   unique
)

For some reason, we've got duplicates in that table, even though, in theory, this is impossible.

Here's a query that I use to find these duplicates:

SELECT md5(t1.domain_name), md5(t2.domain_name)
  FROM domains t1
  JOIN domains t2
    ON t1.domain_name = t2.domain_name
 WHERE t1.domain_id != t2.domain_id;

It returns 50 records, and there are over 7M total records in the table.

I understand how to get rid of them. However, I would really like to know how to prevent new duplicates and why this happened?

PostgreSQL version: PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Answer 1

It seems that the problem is caused by the glibc update issue that @a_horse_with_no_name highlighted in the comments: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html

I suppose it would be enough to get rid of the duplicates and reindex that table, there will be no new duplicates after that.