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

I have scanned all pages for tran numbers 370881,370882,364507 .

It looks like 364507 is the number of restore transaction.

Is it possible that  370881,370882 are the numbers of failed maintenance
jobs like vacuum or rindex?

regards,

Sergey.

select xmin,count(1) from nb.nb_basedtl where xmin in
(370881,370882,364507) group by xmin
>>364507;3236715
>>370881;50
>>370882;50

select xmax,count(1) from nb.nb_basedtl where xmax in
(370881,370882,364507) group by xmax
>> -------------------

--analyze verbose nb.nb_basedtl

-->>49090
do $$ declare n integer = 49090 -1 ; total37088X int = 0; total364507
int = 0;
begin
loop
    if exists (SELECT 1 FROM
heap_page_items(get_raw_page('nb.nb_basedtl', n)) where t_xmin in
(370881,370882) or t_xmax in (370881,370882) ) then
        total37088X=total37088X+1;
    end if;
    if exists (SELECT 1 FROM
heap_page_items(get_raw_page('nb.nb_basedtl', n)) where t_xmin in
(364507) ) then
        total364507=total364507+1;
    end if;
    n=n-1;
    exit when n < 0;
end loop;
raise notice 'total37088X %  total364507 % ',total37088X,total364507;
end$$;

>>NOTICE:  total37088X 0  total364507 44223

04.12.2017 18:13, Tomas Vondra пишет:
>
> On 12/04/2017 04:01 PM, Сергей А. Фролов wrote:
>> page inspection is
>>
>> SELECT * FROM page_header(get_raw_page('nb.nb_basedtl', 0));
>> SELECT * FROM heap_page_items(get_raw_page('nb.nb_basedtl', 0));
>>
>>  lsn | checksum | flags | lower | upper | special | pagesize | version |
>> prune_xid
>> -----+----------+-------+-------+-------+---------+----------+---------+-----------
>>
>>  0/0 |        0 |     0 |   320 |   416 |    8192 |     8192 | 4
>> |         0
>> (1 строка)
>>
>>  lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid |
>> t_infomask2 | t_infomask | t_hoff | t_bits              | t_oid | t_data
>> ----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+----------------------------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>>   1 |   8080 |        1 |    108 | 364507 |      0 |        0 | (0,1)
>> |          27 |       2817 |     32 | 11111110011100111100000010000000
>> |       |
>>
> Thanks. Unfortunately that does not explain anything, and makes it ever
> stranger - none of the items on the page is HOT-updated (t_infomask2 is
> 27, so it only contains number of attributes) or even deleted.
>
> And the only xmin value on the page is 364507. So where did the other
> values (370881, 370882) come from?
>
> regards
>