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

The database was created at october 2016 on PG 9.5.3 then 
backuped/restored into PG 9.6.5 and then backuped/restored into PG 9.6.6.

I am sure that the ~10 problematic records were added on PG 9.6.5. and 
~40  were added on  PG 9.6.6.

The file systems is NTFS.

Windows 10 runs as virtual machine under Hyper-V. Windows logs contains 
nothing suspicious on both.

I have wrote the script to generate  select to check for duplicates all 
tables in all schemas - all other tables are OK.

The only problem I observed - the PG dbugger hanged once and we had to 
kill related postgres process via taskmanager (killing session had no 
effect) , but I am shure that the killed session did not touch the 
problem table at all.

wbr,
Sergey.


01.12.2017 17:20, Tomas Vondra пишет:
> On 12/01/2017 01:45 PM, sergey.frolov@smetarik.ru wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      14940
>> Logged by:          sergey frolov
>> Email address:      sergey.frolov@smetarik.ru
>> PostgreSQL version: 9.6.6
>> Operating system:   Windows 10, 64
>> Description:
>>
>> Hi, I have noticed duplicated records inspite of primary key and unique
>> constraint.
>>
>> select version ()
>> PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit
>>
>> The DDL is
>> CREATE TABLE nb.nb_basedtl
>> (
>>    id integer NOT NULL,
>>    base_id integer NOT NULL,
>>    norm_id integer NOT NULL,
>>    ...
>>    CONSTRAINT pk_nb_basedtl PRIMARY KEY (id),
>>    CONSTRAINT unq_nb_basedtl UNIQUE (norm_id, base_id),
>>    ....
>>
>> The problem is
>>
>> select ctid,xmin,xmax, id, base_id, norm_id from nb.nb_basedtl where id =
>> 11658502 ;-- expected ONE row
>>
>> (0,49);364507;0;11658502;269;46203
>> (0,49);370881;0;11658502;269;46203
>> (0,49);370882;0;11658502;269;46203
>>
>>
>> select (select count(1) from nb.nb_basedtl), (select count(1) from (select
>> id, count(1) from nb.nb_basedtl  group by id having count(1) > 1 ) t )
>> 3586895;50
>>
> Seems like some sort of data corruption, but it's impossible to say how
> the database got into this state. You'll have to tell us more about the
> system.
>
> Did it crash in the past?
>
> What sort of filesystem/storage does it use?
>
> How old is the database/which PostgreSQL versions was it running (e.g.
> it may be a new system loaded last week, or it may be an old system
> started on 9.0 and upgraded using pg_upgrade).
>
> regards
>