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

On 12/04/2017 09:22 AM, Сергей А. Фролов wrote:
> 1.  I'll try amcheck after I get system on Debian and if the problem
> will remain after dump/restore.
>
> 2. Switthing off enable_indexscan enable_bitmapscan has no effect:
>
> set enable_indexscan = off;
> set enable_bitmapscan  = off;
>
> select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id =
> 11658502;
>
> (0,49);364507;0;11658502;269;46203
> (0,49);370881;0;11658502;269;46203
> (0,49);370882;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);364507;0;16101774;321;1239643
> (0,1);370881;0;16101774;321;1239643
> (0,1);370882;0;16101774;321;1239643
> (0,2);370882;0;20365934;425;2
> (0,2);370881;0;20365934;425;2
> (0,2);364507;0;20365934;425;2
> (0,3);370881;0;20365935;425;3
> (0,3);364507;0;20365935;425;3
> (0,3);370882;0;20365935;425;3
> (0,4);370881;0;20365936;425;4
> (0,4);364507;0;20365936;425;4
> ...
> (0,49);370882;0;11658502;269;46203
> (0,49);370881;0;11658502;269;46203
> (0,49);364507;0;11658502;269;46203
> (0,50);364507;0;11658508;269;46204
> (0,50);370882;0;11658508;269;46204
> (0,50);370881;0;11658508;269;46204
>

Interesting. All the duplicate records seem to be on the first page, and
there are always three of them ... I wonder if those records are part of
the same HOT chain, or something like that.

Can you look at the page using pageinspect? Something like

SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0));

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services