Re: BUG #14940: Duplicated records inspite of primary key and unique constraint

Hi,

I have got a VirtualBox with Debian  8.3 and have installed PG 9.6 and
amcheck:

select version ()
"PostgreSQL 9.6.6 on i686-pc-linux-gnu, compiled by gcc (Debian
4.9.2-10) 4.9.2, 32-bit"

No errors were raised by amcheck:
SELECT bt_index_check(index => indexrelid, heapallindexed =>
true),bt_index_parent_check(index =>indexrelid), relname, indexrelname
    FROM pg_stat_all_indexes
    WHERE indexrelname in ('pk_nb_basedtl','unq_nb_basedtl_basenorm');

"";"";"nb_basedtl";"pk_nb_basedtl"
"";"";"nb_basedtl";"unq_nb_basedtl_basenorm"

BUT duplicate rows were restored via pg_dump/pg_restore.
select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id
=11658502;

(0,49);5513;0;11658502;269;46203
(0,49);5518;0;11658502;269;46203
(0,49);5519;0;11658502;269;46203

select ctid,xmin,xmax,id, base_id, norm_id from nb.nb_basedtl where id
in (select
id from nb.nb_basedtl  group by id having count(1) > 1 ) order by ctid,id
(0,1);5513;0;16101774;321;1239643
(0,1);5519;0;16101774;321;1239643
(0,1);5518;0;16101774;321;1239643
(0,2);5519;0;20365934;425;2
(0,2);5518;0;20365934;425;2
(0,2);5513;0;20365934;425;2
(0,3);5513;0;20365935;425;3
(0,3);5519;0;20365935;425;3
(0,3);5518;0;20365935;425;3
(0,4);5513;0;20365936;425;4
(0,4);5519;0;20365936;425;4
(0,4);5518;0;20365936;425;4
(0,5);5513;0;20365937;425;276807
(0,5);5519;0;20365937;425;276807
(0,5);5518;0;20365937;425;276807
(0,6);5519;0;20365938;425;276808
(0,6);5513;0;20365938;425;276808
(0,6);5518;0;20365938;425;276808
...
(0,49);5519;0;11658502;269;46203
(0,49);5513;0;11658502;269;46203
(0,49);5518;0;11658502;269;46203
(0,50);5513;0;11658508;269;46204
(0,50);5519;0;11658508;269;46204
(0,50);5518;0;11658508;269;46204

I have tried reindex - no effect, clustering nb_basedtl using
pk_nb_basedtl changed lowest ctid and moved records last

(0,1);5519;0;16101774;321;1239643
(0,1);5518;0;16101774;321;1239643
(0,2);5519;0;20365934;425;2
(0,2);5518;0;20365934;425;2
(0,3);5519;0;20365935;425;3
(0,3);5518;0;20365935;425;3
(0,4);5519;0;20365936;425;4
(0,4);5518;0;20365936;425;4
...
(0,49);5519;0;11658502;269;46203
(0,49);5518;0;11658502;269;46203
(0,50);5519;0;11658508;269;46204
(0,50);5518;0;11658508;269;46204
(19306,68);5513;0;11658485;269;46190
(19306,75);5513;0;11658492;269;46197
(19306,77);5513;0;11658494;269;46196
(19307,1);5513;0;11658496;269;46194
(19307,7);5513;0;11658502;269;46203
(19307,13);5513;0;11658508;269;46204
(21100,27);5513;0;12066836;280;95258
(24575,75);5513;0;16101774;321;1239643
(24575,77);5513;0;20365934;425;2
(24575,78);5513;0;20365935;425;3
(24575,79);5513;0;20365936;425;4
...
(24576,36);5513;0;20365974;425;42
(24576,37);5513;0;20365975;425;43

This stuff  have made me slightly mad:

alter table nb.nb_basedtl
  add constraint unq_nb_basedtl_basenorm2 unique(norm_id, base_id);

create  unique index pk_nb_basedtl2 on nb.nb_basedtl(id);

select bt_index_check(index => indexrelid, heapallindexed => true),
bt_index_parent_check(index =>indexrelid),
          relname,
          indexrelname
    from pg_stat_all_indexes
    where indexrelname in
('pk_nb_basedtl','pk_nb_basedtl2','unq_nb_basedtl_basenorm','unq_nb_basedtl_basenorm2')

"";"";"nb_basedtl";"pk_nb_basedtl"
"";"";"nb_basedtl";"unq_nb_basedtl_basenorm"
"";"";"nb_basedtl";"unq_nb_basedtl_basenorm2"
"";"";"nb_basedtl";"pk_nb_basedtl2"

But this stuff have returned my trust in PG:

create table nb.nb_basedtl_copy (like nb.nb_basedtl including all);

insert into nb.nb_basedtl_copy select * from nb.nb_basedtl where id
=11658502;

ERROR:  duplicate key value violates unique constraint
"nb_basedtl_copy_pkey"
DETAIL:  Key (id)=(11658502) already exists.
********** Error **********

wbr,
Sergey