From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Teju Jakkidi vlogs <teja.jakkidi05@gmail.com>
Cc: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Duplicate data even with primary keys enabled
Date: Fri, 28 Oct 2022 14:50:22 -0600
Message-ID: <CECDAF83-106B-4425-865C-176B170CA56D@elevated-dev.com> (raw)
In-Reply-To: <CAKA2XvYoEw0qbMKmnk686gOKe+FrR22R14S6Ts1aY96+ExHtgA@mail.gmail.com>
References: <CAAo1mbnEV6QxHwnV6r2EUMXjb_Fz98+8eO6ZRU7eux4FTuF0YA@mail.gmail.com>
<59B1180A-4A4B-4A1A-B50A-C9D9B41AA67D@gmail.com>
<74e777cb-c332-3db7-7c4a-784d408227d6@jakobs.com>
<CAKA2XvaFawqTiRtnYKEz84x4A9rDcY2vDCY2SbkS3e2cNKxLng@mail.gmail.com>
<032E52B8-00C2-431D-BBAF-DCFCCEA817D2@elevated-dev.com>
<CAKA2XvYoEw0qbMKmnk686gOKe+FrR22R14S6Ts1aY96+ExHtgA@mail.gmail.com>
> 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 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.
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'd suggest posting the result of \d "TEST", to make sure that the index hasn't been altered (or dropped), and trying REINDEX on the table as well.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, teja.jakkidi05@gmail.com, holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Duplicate data even with primary keys enabled
In-Reply-To: <CECDAF83-106B-4425-865C-176B170CA56D@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox