PostgreSQL multicolumn unique not works

Question

I have a strange situation in PostgreSQL 9.4. I have a table:

id      integer                 NOT NULL DEFAULT nextval('users_userpropmeta_id_seq'::regclass)
name    character varying(255)  NOT NULL
cls     character varying(4)    NOT NULL
app_id  integer                 NOT NULL

And a unique constraint: UNIQUE (app_id, name)

Now I query table:

 SELECT COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

And get:

 count | app_id |          name
-------+--------+-------------------------
     2 |   6019 | Создание серии писем
     2 |   6019 | Увеличение объемов базы
(2 rows)

So unique doesn't work? I've gone futher:

SELECT * FROM users_userpropmeta WHERE app_id=6019 AND name in ('Создание серии писем', 'Увеличение объемов базы');
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 7308 | Создание серии писем    | str |   6019
 7309 | Увеличение объемов базы | str |   6019
(2 rows)

Only 2 rows. Some magic is going on here. Lets's find rows with a hack:

SELECT MAX(id), MIN(id), COUNT(*), app_id, name FROM users_userpropmeta GROUP BY app_id, name HAVING COUNT(*) > 1;

 max  | min  | count | app_id |          name
------+------+-------+--------+-------------------------
 7308 | 4633 |     2 |   6019 | Создание серии писем
 7309 | 4636 |     2 |   6019 | Увеличение объемов базы
(2 rows)

Here are found rows:

SELECT * FROM users_userpropmeta WHERE id IN (7308, 7309, 4633, 4636);
  id  |          name           | cls | app_id
------+-------------------------+-----+--------
 4633 | Создание серии писем    | str |   6019
 4636 | Увеличение объемов базы | str |   6019
 7308 | Создание серии писем    | str |   6019
 7309 | Увеличение объемов базы | str |   6019
(4 rows)

Comparing lines one by one is correct, they are equal:

SELECT a.id, b.id, a.name, b.name, a.name = b.name FROM users_userpropmeta AS a CROSS JOIN users_userpropmeta AS b WHERE a.id IN (7308, 7309, 4633, 4636) AND b.id IN (7308, 7309, 4633, 4636);
  id  |  id  |          name           |          name           | ?column?
------+------+-------------------------+-------------------------+----------
 4633 | 4633 | Создание серии писем    | Создание серии писем    | t
 4633 | 4636 | Создание серии писем    | Увеличение объемов базы | f
 4633 | 7308 | Создание серии писем    | Создание серии писем    | t
 4633 | 7309 | Создание серии писем    | Увеличение объемов базы | f
 4636 | 4633 | Увеличение объемов базы | Создание серии писем    | f
 4636 | 4636 | Увеличение объемов базы | Увеличение объемов базы | t
 4636 | 7308 | Увеличение объемов базы | Создание серии писем    | f
 4636 | 7309 | Увеличение объемов базы | Увеличение объемов базы | t
 7308 | 4633 | Создание серии писем    | Создание серии писем    | t
 7308 | 4636 | Создание серии писем    | Увеличение объемов базы | f
 7308 | 7308 | Создание серии писем    | Создание серии писем    | t
 7308 | 7309 | Создание серии писем    | Увеличение объемов базы | f
 7309 | 4633 | Увеличение объемов базы | Создание серии писем    | f
 7309 | 4636 | Увеличение объемов базы | Увеличение объемов базы | t
 7309 | 7308 | Увеличение объемов базы | Создание серии писем    | f
 7309 | 7309 | Увеличение объемов базы | Увеличение объемов базы | t
(16 rows)

Can anyone explain, why unique constraint inserted both lines without exception? Why IN operator can't find them (I've thought of some unprintable symbols?), but equality test shows, that they are equal?

P. s. Originally, I've created subscription to PostgreSQL 9.6 with pglogical to make a logical replica from postgres to another server. And it failed with error, that it can't insert duplicated rows =).

Answer 1

This has to be a corrupted index.

Experiment with enable_seqscan = off and see if you still find the duplicates.

Likely you will have to delete the duplicate entries by ctid and reindex the table.