BUG #14389: Duplicate rows on tables with unique index constrain

The following bug has been logged on the website:

Bug reference: 14389
Logged by: Mahmoud Hakeem-Habeeb
Email address: mahmoudhakh(at)gmail(dot)com
PostgreSQL version: 9.4.4
Operating system: Centos 6.6
Description:

Hi,
I have noticed an issue with rows not honouring the unique index
constrain.
I first came across the issue when upgrading from 9.2.4 to 9.4.4, i got the
error below

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6666; 1259 29759702 INDEX
idxdomainid postgres
pg_restore: [archiver (db)] could not execute query: ERROR: could not
create unique index "idxdomainid"
DETAIL: Key (id, domain)=(3, people.net) is duplicated.
Command was: CREATE UNIQUE INDEX idxdomainid ON domains USING tree (id,
domain);

In the above case i rolled back and deleted the offending row, and then
upgraded successfully.

Then i started seeing more of the issue, during my upgrade so i stopped and
ran a check to find any duplicates using
BEGIN;
set enable_indexscan to false;
set enable_bitmapscan to false;
select count(*), id, domain from domains group by id, domain having
count(*) > 1;
ROLLBACK;

I found the same issue on servers running 9.2.4 and 9.4.4 and also found it
happening on a number of tables.

The below is one i have not fixed as i am using it report this bug or find
the cause.
I also have a few more servers with the same issue.

db=> select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)

db=> select sid,domain,id from domains where domain = 'mornet.net' and id
=3;
sid | domain | id
----------+------------+-----------
18662135 | mornet.net | 3

db=> set enable_indexscan to false;
SET
db=> set enable_bitmapscan to false;
SET
db=> select count(*), id, domain from domains group by id, domain having
count(*) > 1;
count | id | domain
-------+-----------+---------------------
2 | 3 | mornet.net

db=> select sid,domain,id from domains where domain = 'mornet.net' and id
=3;
sid | domain | id
----------+------------+-----------
266581 | mornet.net | 3
18662135 | mornet.net | 3

db=> set enable_indexscan to true;
SET
db=> set enable_bitmapscan to true;
SET

db=> select sid,domain,id from domains where domain = 'mornet.net' and id
=3;
sid | domain | id
----------+------------+-----------
18662135 | mornet.net | 3

db=> \d domains
Table "public.domains"
Column | Type | Modifiers
----------------+-------------------+---------------------------------------------------------
sid | bigint | not null default
nextval('domains_sid_seq'::regclass)
id | bigint | not null
domain | character varying | not null
sendonly | boolean | default false
spacedomain | boolean | default false
outboundchecks | smallint | default 0
normalised | character varying |
Indexes:
"domains_pkey" PRIMARY KEY, btree (sid)
"idxdomainid" UNIQUE, btree (id, domain)

If you require any more info please let me know and i will provide what i
can.

This is similar to the bug reported bug #13935

https://www.postgresql.org/message-id/flat/20160208163222(dot)2661(dot)51608%40wrigleys(dot)postgresql(dot)org#20160208163222(dot)2661(dot)51608(at)wrigleys(dot)postgresql(dot)org
Thanks,
Mahmoud