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

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(dot)frolov(at)smetarik(dot)ru wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 14940
>> Logged by: sergey frolov
>> Email address: sergey(dot)frolov(at)smetarik(dot)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
>