Thread: Duplicate data even with primary keys enabled

Duplicate data even with primary keys enabled

From

Teja Jakkidi

Date:

28 October 2022, 20:52:24

Hello Admin team,

I have an issue where we are seeing duplicate data in one of the tables. The table has primary key constraint defined
on3columns and we are seeing duplicate data in these column data. 
Is there any way to figure out why this could happen? Also is there a way that the primary key constraint can be
disabledduring  inserts?? If so how can we find who disabled it? (Note that audit is not enabled for the table) 
Please suggest.

Thanks,
Teja.

Re: Duplicate data even with primary keys enabled

From

Samed YILDIRIM

Date:

28 October 2022, 20:59:25

Hello Teja,

Could you please add some example lines? I need to see some examples to say something more accurate.

First of all, null != null in PostgreSQL. So, if any of those three columns is null, it means it is unique and won't violate unique constraints even if there is another row with the same values.

Another thing, if you defined a composite primary key on those 3 columns, it means that it will prevent inserting another row that has exactly the same values on those columns. If you want to keep values unique on each individual columns, you need to create 3 separate unique indexes/constraints.

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 19:52 Teja Jakkidi, <teja.jakkidi05@gmail.com> wrote:

Hello Admin team,

I have an issue where we are seeing duplicate data in one of the tables. The table has primary key constraint defined on 3columns and we are seeing duplicate data in these column data.
Is there any way to figure out why this could happen? Also is there a way that the primary key constraint can be disabled during inserts?? If so how can we find who disabled it? (Note that audit is not enabled for the table)
Please suggest.

Thanks,
Teja.

Re: Duplicate data even with primary keys enabled

From

Teja Jakkidi

Date:

28 October 2022, 21:27:14

Hi YILDIRIM,

Thanks for your response.

We have a composite primary key on 3columns. We are noticing multiple rows with same values in the 3pk columns which should not happen as primary constraint is defined on them.

Also, none of the columns are null.

Regards,

Teja. J.

On Oct 28, 2022, at 10:59 AM, Samed YILDIRIM <samed@reddoc.net> wrote:

Hello Teja,

Could you please add some example lines? I need to see some examples to say something more accurate.

First of all, null != null in PostgreSQL. So, if any of those three columns is null, it means it is unique and won't violate unique constraints even if there is another row with the same values.

Another thing, if you defined a composite primary key on those 3 columns, it means that it will prevent inserting another row that has exactly the same values on those columns. If you want to keep values unique on each individual columns, you need to create 3 separate unique indexes/constraints.

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 19:52 Teja Jakkidi, <teja.jakkidi05@gmail.com> wrote:
Hello Admin team,

I have an issue where we are seeing duplicate data in one of the tables. The table has primary key constraint defined on 3columns and we are seeing duplicate data in these column data.
Is there any way to figure out why this could happen? Also is there a way that the primary key constraint can be disabled during inserts?? If so how can we find who disabled it? (Note that audit is not enabled for the table)
Please suggest.

Thanks,
Teja.

Re: Duplicate data even with primary keys enabled

From

Holger Jakobs

Date:

28 October 2022, 21:41:29

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple 
> rows with same values in the 3pk columns which should not happen as 
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command 
which lead to the described behavior, we won't be able to track the problem.


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Attachment

OpenPGP_signature

Re: Duplicate data even with primary keys enabled

From

Ron

Date:

28 October 2022, 22:31:57

You're confusing PRIMARY KEY with UNIQUE.

On 10/28/22 12:59, Samed YILDIRIM wrote:

Hello Teja,

Could you please add some example lines? I need to see some examples to say something more accurate.

First of all, null != null in PostgreSQL. So, if any of those three columns is null, it means it is unique and won't violate unique constraints even if there is another row with the same values.

Another thing, if you defined a composite primary key on those 3 columns, it means that it will prevent inserting another row that has exactly the same values on those columns. If you want to keep values unique on each individual columns, you need to create 3 separate unique indexes/constraints.

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 19:52 Teja Jakkidi, <teja.jakkidi05@gmail.com> wrote:
Hello Admin team,

I have an issue where we are seeing duplicate data in one of the tables. The table has primary key constraint defined on 3columns and we are seeing duplicate data in these column data.
Is there any way to figure out why this could happen? Also is there a way that the primary key constraint can be disabled during inserts?? If so how can we find who disabled it? (Note that audit is not enabled for the table)
Please suggest.

Thanks,
Teja.

--
Angular momentum makes the world go 'round.

Re: Duplicate data even with primary keys enabled

From

Teju Jakkidi vlogs

Date:

28 October 2022, 23:15:46

Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"

(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1	COL2	COL3	COL4	COL5	COL6
1	3456	76542	5	ABC	1234
1	3456	76542	5	ABC	1234
2	9872	89765	0	FGT	1234
3	6547	78659	7	JHL	8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,

Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

28 October 2022, 23:27:29

> On Oct 28, 2022, at 2:15 PM, Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:
>
> Also, the values that we are seeing is as below:
>
> COL1    COL2    COL3    COL4    COL5    COL6
> 1    3456    76542    5    ABC    1234
> 1    3456    76542    5    ABC    1234
> 2    9872    89765    0    FGT    1234
> 3    6547    78659    7    JHL    8790
>
> We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows
hasexactly same combination for those 3 rows. 

What query are you running?

Re: Duplicate data even with primary keys enabled

From

Teju Jakkidi vlogs

Date:

28 October 2022, 23:42:06

Hello Scott,

I am wondering how the query matters here?

Also, I am just running a simple select from "TEST" with COL1, COL2, COL3 in the WHERE condition.

select * from 'TEST" WHERE COL1='3456' AND COL2='76542' AND COL3='5';

The actual Issue is I am unable to figure out why the table is having duplicate data in the primary key columns. Or how postgres accepted 2 rows with the same combination of the columns in the primary key constraint. I would expect it to throw primary key constraint violation error.

Regards,

Teja. J.

On Fri, Oct 28, 2022 at 1:27 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:

> On Oct 28, 2022, at 2:15 PM, Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:
>
> Also, the values that we are seeing is as below:
>
> COL1 COL2 COL3 COL4 COL5 COL6
> 1 3456 76542 5 ABC 1234
> 1 3456 76542 5 ABC 1234
> 2 9872 89765 0 FGT 1234
> 3 6547 78659 7 JHL 8790
>
> We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

What query are you running?

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

28 October 2022, 23:50:22

> On Oct 28, 2022, at 2:42 PM, Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:
>
> I am wondering how the query matters here?

Because it's possible to write a query that returns the same row multiple times.

> Also, I am just running a simple select from "TEST" with COL1, COL2, COL3 in the WHERE condition.
> select * from 'TEST" WHERE COL1='3456' AND  COL2='76542' AND COL3='5';
>
> The actual Issue is I am unable to figure out why the table is having duplicate data in the primary key columns. Or
howpostgres accepted 2 rows with the same combination of the columns in the primary key constraint. I would expect it
tothrow primary key constraint violation error. 

A  corrupted index could allow that to happen by failing to catch the duplicate insert. I suppose it might also be able
tocause the same row to be returned multiple times--although I have personally only ever seen corrupted indexes cause
rowsto not be found. 

I'd suggest posting the result of \d "TEST", to make sure that the index hasn't been altered (or dropped), and trying
REINDEXon the table as well.

Re: Duplicate data even with primary keys enabled

From

Marco DE BOOIJ

Date:

28 October 2022, 23:51:09

Your output looks strange. The character colums are right aligned. This is for numeric colums. Saying this, can you run the following select to see if there are no trailing or prepending spaces. This can be the reason for not finding the duplicates.

select concat('|', "COL1", '|'), concat('|', "COL2", '|'), concat('|', "COL3", '|') from "TEST";

Regards,

Marco

Op 28/10/2022 om 22:15 schreef Teju Jakkidi vlogs:

Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Holger Jakobs

Date:

28 October 2022, 23:54:24

Am 28.10.22 um 22:15 schrieb Teju Jakkidi vlogs:

Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

I just copied your CREATE TABLE statement (with all peculiarities like quoted identifiers):
hj=# CREATE TABLE IF NOT EXISTS "TEST"
hj-# (
hj(#     "COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL4" numeric(10,0) NOT NULL,
hj(#     "COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(#     "COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
hj(#     CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
hj(# )
hj-# ;

CREATE TABLE

Trying to insert the same row twice results in an error:
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
INSERT 0 1
hj=# insert into "TEST" values (1,3456, 76542, 5, 'ABC', '1234');
FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint »test_pk«
DETAIL: Schlüssel »("COL1", "COL2", "COL3")=(1, 3456, 76542)« existiert bereits.

So you'll have to check what you are doing differently.

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Attachment

OpenPGP_signature

Re: Duplicate data even with primary keys enabled

From

Marco DE BOOIJ

Date:

29 October 2022, 00:06:10

select concat('|', "COL1", '|'), concat('|', "COL2", '|'), concat('|', "COL3", '|') from "TEST";

Regards, Marco

Op 28/10/2022 om 22:15 schreef Teju Jakkidi vlogs:

Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Erik Wienhold

Date:

29 October 2022, 03:18:38

> On 28/10/2022 22:50 CEST Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> A corrupted index could allow that to happen by failing to catch the duplicate
> insert. I suppose it might also be able to cause the same row to be returned
> multiple times--although I have personally only ever seen corrupted indexes
> cause rows to not be found.

I think it should be possible to tell those two cases apart by looking at system
column ctid.  The ctid should be the same for duplicate rows if the same row is
found multiple times in the index.  Different ctid if the corrupted index
allowed INSERT with duplicates.

--
Erik

Re: Duplicate data even with primary keys enabled

From

Jwiencek3

Date:

29 October 2022, 05:53:30

Is it possible that a column is padded with spaces? I’d check the length of each column. Extra spaces to the right would make the columns unique. You can’t tell if the column is padded with blanks from your query. Checking the length would confirm each column is exactly the same.

Sent from my iPad

On Oct 28, 2022, at 3:42 PM, Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:

Hello Scott,

I am wondering how the query matters here?

Also, I am just running a simple select from "TEST" with COL1, COL2, COL3 in the WHERE condition.
select * from 'TEST" WHERE COL1='3456' AND COL2='76542' AND COL3='5';

The actual Issue is I am unable to figure out why the table is having duplicate data in the primary key columns. Or how postgres accepted 2 rows with the same combination of the columns in the primary key constraint. I would expect it to throw primary key constraint violation error.

Regards,
Teja. J.

On Fri, Oct 28, 2022 at 1:27 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Oct 28, 2022, at 2:15 PM, Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:
>
> Also, the values that we are seeing is as below:
>
> COL1 COL2 COL3 COL4 COL5 COL6
> 1 3456 76542 5 ABC 1234
> 1 3456 76542 5 ABC 1234
> 2 9872 89765 0 FGT 1234
> 3 6547 78659 7 JHL 8790
>
> We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

What query are you running?

Re: Duplicate data even with primary keys enabled

From

abbas alizadeh

Date:

29 October 2022, 14:20:58

Hi all.
I faced the same issue last week.
We had duplicated records on primary key.
Postgresql version is 14.4


Sent from my iPhone

> On 29 Oct 2022, at 4:18 AM, Erik Wienhold <ewie@ewie.name> wrote:
>
> 
>>
>> On 28/10/2022 22:50 CEST Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>>
>> A corrupted index could allow that to happen by failing to catch the duplicate
>> insert. I suppose it might also be able to cause the same row to be returned
>> multiple times--although I have personally only ever seen corrupted indexes
>> cause rows to not be found.
>
> I think it should be possible to tell those two cases apart by looking at system
> column ctid.  The ctid should be the same for duplicate rows if the same row is
> found multiple times in the index.  Different ctid if the corrupted index
> allowed INSERT with duplicates.
>
> --
> Erik
>
>

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

29 October 2022, 15:16:01

Actual table definition, sample query, and output, please.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Oct 29, 2022, at 5:20 AM, abbas alizadeh <ramkly@yahoo.com> wrote:
> 
> I faced the same issue last week. 
> We had duplicated records on primary key.

Re: Duplicate data even with primary keys enabled

From

Jayson Hreczuck

Date:

29 October 2022, 17:40:54

Very curious about this too. Corruption of indexes does occur but less than a handful of times in my 30 years of DBA experience, and is usually due to hardware failure or some immediate cause.

Script the table and provide the query.

On Sat, Oct 29, 2022, 8:16 AM Scott Ribe <scott_ribe@elevated-dev.com> wrote:

Actual table definition, sample query, and output, please.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/

> On Oct 29, 2022, at 5:20 AM, abbas alizadeh <ramkly@yahoo.com> wrote:
>
> I faced the same issue last week.
> We had duplicated records on primary key.

Re: Duplicate data even with primary keys enabled

From

Samed YILDIRIM

Date:

29 October 2022, 17:47:20

Hi,

If you used CONCURRENTLY while creating your index, there is a possibility that you hit a bug, which was fixed with 14.4.

However, if you have any indexes that were created using the CONCURRENTLY option under 14.X, you should re-index them after updating. See the first changelog entry below.

https://www.postgresql.org/docs/14/release-14-4.html

Best regards.
Samed YILDIRIM

On Sat, 29 Oct 2022, 14:21 abbas alizadeh, <ramkly@yahoo.com> wrote:

Hi all.
I faced the same issue last week.
We had duplicated records on primary key.
Postgresql version is 14.4

Sent from my iPhone

> On 29 Oct 2022, at 4:18 AM, Erik Wienhold <ewie@ewie.name> wrote:
>
>
>>
>> On 28/10/2022 22:50 CEST Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>>
>> A corrupted index could allow that to happen by failing to catch the duplicate
>> insert. I suppose it might also be able to cause the same row to be returned
>> multiple times--although I have personally only ever seen corrupted indexes
>> cause rows to not be found.
>
> I think it should be possible to tell those two cases apart by looking at system
> column ctid. The ctid should be the same for duplicate rows if the same row is
> found multiple times in the index. Different ctid if the corrupted index
> allowed INSERT with duplicates.
>
> --
> Erik
>
>

Re: Duplicate data even with primary keys enabled

From

Samed YILDIRIM

Date:

29 October 2022, 17:53:04

Hi Ron,

No, I'm not :) I gave general information about unique indexes that would help Teja with his issue. Primary keys and unique constraints are similar to each other. :)

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 22:32 Ron, <ronljohnsonjr@gmail.com> wrote:

You're confusing PRIMARY KEY with UNIQUE.

On 10/28/22 12:59, Samed YILDIRIM wrote:
Hello Teja,

Could you please add some example lines? I need to see some examples to say something more accurate.

First of all, null != null in PostgreSQL. So, if any of those three columns is null, it means it is unique and won't violate unique constraints even if there is another row with the same values.

Another thing, if you defined a composite primary key on those 3 columns, it means that it will prevent inserting another row that has exactly the same values on those columns. If you want to keep values unique on each individual columns, you need to create 3 separate unique indexes/constraints.

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 19:52 Teja Jakkidi, <teja.jakkidi05@gmail.com> wrote:
Hello Admin team,

I have an issue where we are seeing duplicate data in one of the tables. The table has primary key constraint defined on 3columns and we are seeing duplicate data in these column data.
Is there any way to figure out why this could happen? Also is there a way that the primary key constraint can be disabled during inserts?? If so how can we find who disabled it? (Note that audit is not enabled for the table)
Please suggest.

Thanks,
Teja.

--
Angular momentum makes the world go 'round.

Re: Duplicate data even with primary keys enabled

From

Samed YILDIRIM

Date:

29 October 2022, 18:06:14

Hi Teja,

Your columns are varchar. They may seem the same to you due to the tool you use. But, it doesn't mean they are identical. There can be leading or trailing spaces.

You can test the data on columns by md5 hashing function, example below. If their hashes are the same, it means there is a problem on the database side. Otherwise, the problem comes from the data itself or from the application writing the data into the table.

select md5(col1), md5(col2), md5(col3) from table.

Another possibility is that you hit a bug I mentioned earlier from 14.0 to 14.3.

- What version of PpstgreSQL do you use?

- Did you use CONCURRENTLY keyword while creating your index?

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 23:16 Teju Jakkidi vlogs, <teja.jakkidi05@gmail.com> wrote:

Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

29 October 2022, 18:20:22

> On Oct 29, 2022, at 8:40 AM, Jayson Hreczuck <jayson.hreczuck@studentuniverse.com> wrote:
>
> Very curious about this too.  Corruption of indexes does occur but less than a handful of times in my 30 years of DBA
experience,and is usually due to hardware failure or some immediate cause. 

I've seen it once because of bad hardware, and once because of collation changes with a Linux upgrade. That latter one
wastricky, because it wasn't limited to just non-7-bit characters, but also how hyphens (or maybe it was underscores)
werehandled.

Re: Duplicate data even with primary keys enabled

From

Tom Lane

Date:

29 October 2022, 18:26:24

Samed YILDIRIM <samed@reddoc.net> writes:
> Hi Teja,
> Your columns are varchar. They may seem the same to you due to the tool you
> use. But, it doesn't mean they are identical. There can be leading or
> trailing spaces.

Another thing I'm wondering about is index corruption due to an
operating-system upgrade that changed string sort ordering.
Nothing might be obviously wrong right after the upgrade, but
index searches would probably fail to find existing entries,
which could lead to not rejecting duplicate entries.

This is, sadly, not at all hypothetical or uncommon.
There's some info about this hazard at:

https://wiki.postgresql.org/wiki/Locale_data_changes

            regards, tom lane

Re: Duplicate data even with primary keys enabled

From

Teju Jakkidi vlogs

Date:

31 October 2022, 19:06:59

Hello YILDIRIM,

Thanks for the reply.

The md5() value returns out to be the same for the duplicate values.

Also, postgres version is 14.4 and we did not use CONCURRENTLY keyword while creating indexes.

Regards,

Teja.

On Sat, Oct 29, 2022 at 8:06 AM Samed YILDIRIM <samed@reddoc.net> wrote:

Hi Teja,

Your columns are varchar. They may seem the same to you due to the tool you use. But, it doesn't mean they are identical. There can be leading or trailing spaces.

You can test the data on columns by md5 hashing function, example below. If their hashes are the same, it means there is a problem on the database side. Otherwise, the problem comes from the data itself or from the application writing the data into the table.

select md5(col1), md5(col2), md5(col3) from table.

Another possibility is that you hit a bug I mentioned earlier from 14.0 to 14.3.
- What version of PpstgreSQL do you use?
- Did you use CONCURRENTLY keyword while creating your index?

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 23:16 Teju Jakkidi vlogs, <teja.jakkidi05@gmail.com> wrote:
Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

31 October 2022, 19:16:00

You still have not provided the actual current table definition in use.

Output of \d on the table, please.

Re: Duplicate data even with primary keys enabled

From

jwiencek3

Date:

31 October 2022, 20:08:58

Have you tried dropping the index and then recreating it ?

Sent via the Samsung Galaxy S22+ 5G, an AT&T 5G smartphone

-------- Original message --------

From: Teju Jakkidi vlogs <teja.jakkidi05@gmail.com>

Date: 10/31/22 11:07 AM (GMT-06:00)

To: Samed YILDIRIM <samed@reddoc.net>

Cc: Holger Jakobs <holger@jakobs.com>, pgsql-admin <pgsql-admin@lists.postgresql.org>

Subject: Re: Duplicate data even with primary keys enabled

Hello YILDIRIM,

Thanks for the reply.

The md5() value returns out to be the same for the duplicate values.

Also, postgres version is 14.4 and we did not use CONCURRENTLY keyword while creating indexes.

Regards,

Teja.

On Sat, Oct 29, 2022 at 8:06 AM Samed YILDIRIM <samed@reddoc.net> wrote:

Hi Teja,

Your columns are varchar. They may seem the same to you due to the tool you use. But, it doesn't mean they are identical. There can be leading or trailing spaces.

You can test the data on columns by md5 hashing function, example below. If their hashes are the same, it means there is a problem on the database side. Otherwise, the problem comes from the data itself or from the application writing the data into the table.

select md5(col1), md5(col2), md5(col3) from table.

Another possibility is that you hit a bug I mentioned earlier from 14.0 to 14.3.
- What version of PpstgreSQL do you use?
- Did you use CONCURRENTLY keyword while creating your index?

Best regards.
Samed YILDIRIM

On Fri, 28 Oct 2022, 23:16 Teju Jakkidi vlogs, <teja.jakkidi05@gmail.com> wrote:
Hello Jakobs,

I have provided the sample create table and inserts that we are observing.

CREATE TABLE IF NOT EXISTS "TEST"
(
"COL1" character varying(9) COLLATE pg_catalog."default" NOT NULL,
"COL2" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL3" character varying(30) COLLATE pg_catalog."default" NOT NULL,
"COL4" numeric(10,0) NOT NULL,
"COL5" character varying(12) COLLATE pg_catalog."default" NOT NULL,
"COL6" character varying(12) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("COL1", "COL2", "COL3")
)

CREATE UNIQUE INDEX IF NOT EXISTS "TEST_UNIQUE"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL2" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS "TEST_INDEX"
ON "TEST" USING btree
("COL1" COLLATE pg_catalog."default" ASC NULLS LAST, "COL3" COLLATE pg_catalog."default" ASC NULLS LAST);

Also, the values that we are seeing is as below:

COL1 COL2 COL3 COL4 COL5 COL6
1 3456 76542 5 ABC 1234
1 3456 76542 5 ABC 1234
2 9872 89765 0 FGT 1234
3 6547 78659 7 JHL 8790

We already defined COL1, COL2, COL3 as primary keys, but still as you see above in the table output, the first 2 rows has exactly same combination for those 3 rows.

Thanks,
Teja.

On Fri, Oct 28, 2022 at 11:41 AM Holger Jakobs <holger@jakobs.com> wrote:

Am 28.10.22 um 20:27 schrieb Teja Jakkidi:
> Hi YILDIRIM,
>
> Thanks for your response.
>
> We have a composite primary key on 3columns. We are noticing multiple
> rows with same values in the 3pk columns which should not happen as
> primary constraint is defined on them.
> Also, none of the columns are null.
>
> Regards,
> Teja. J.
>
Unless you don't post your CREATE TABLE command plus some INSERT command
which lead to the described behavior, we won't be able to track the problem.

--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Re: Duplicate data even with primary keys enabled

From

Scott Ribe

Date:

31 October 2022, 20:19:14

> On Oct 31, 2022, at 11:08 AM, jwiencek3 <jwiencek3@comcast.net> wrote:
>
> Have you tried dropping the index and then recreating it ?

Recreate or reindex are good things to try. BUT DEFINITELY CHECK INDEX DEFINITION BEFORE DROPPING