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

From: sergey(dot)frolov(at)smetarik(dot)ru
To: pgsql-bugs(at)postgresql(dot)org
Cc: sergey(dot)frolov(at)smetarik(dot)ru
Subject: BUG #14940: Duplicated records inspite of primary key and unique constraint
Date: 2017-12-01 12:45:33
Message-ID: 20171201124533.27099.26427@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mirav T. Mehta 2017-12-01 13:22:35 Postgres installation issue
Previous Message Bogdan Stepanenko 2017-12-01 10:59:21 default value CURRENT_TIMESTAMP in TIMESTAMP is a string value 'CURRENT_TIMESTAMP' in pgsql >= 10.0