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

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

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