Обсуждение: BUG #18002: Duplicate entries of row possible even after having primary key

The following bug has been logged on the website:

Bug reference:      18002
Logged by:          Ajinkya Tankhiwale
Email address:      ajinkya.tankhiwale@tcs.com
PostgreSQL version: 14.7
Operating system:   RHEL 8.8
Description:

We migrated from 13.7 to 14.7 recently, in current data setup, we say that
duplicate records are present in table even after primary key. I see that in
the past, same issue was reported and possible solution was to run full
vacuum or re-index. We tried both, but still able to enter duplicate
records. 

This is blocker for us and would like to have solution for this as soon as
possible.

Hi,

On 2023-06-27 11:03:16 +0000, PG Bug reporting form wrote:
> Bug reference:      18002
> Logged by:          Ajinkya Tankhiwale
> Email address:      ajinkya.tankhiwale@tcs.com
> PostgreSQL version: 14.7
> Operating system:   RHEL 8.8
> Description:        
> 
> We migrated from 13.7 to 14.7 recently, in current data setup, we say that
> duplicate records are present in table even after primary key. I see that in
> the past, same issue was reported and possible solution was to run full
> vacuum or re-index. We tried both, but still able to enter duplicate
> records. 
> 
> This is blocker for us and would like to have solution for this as soon as
> possible.

You haven't provided information that'd allow us to debug the issue. At the
very least we'd need table + index definition and the inserts that are allowed
to happen despite being duplicates.  Even better would be a way to reproduce
the issue.

You said that you migrated from 13.7 to 14.7 recently - did you also change
your OS version?

Greetings,

Andres Freund

Hello,

Please find below DDL for one of the table

CREATE SEQUENCE IF NOT EXISTS seq_ao_id
    CYCLE
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 999999999999999999
    CACHE 1;

CREATE TABLE IF NOT EXISTS offer
(
    action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
    action_type character varying COLLATE pg_catalog."default" NOT NULL,
    buy_sell_indicator character varying COLLATE pg_catalog."default" NOT NULL,
    creation_date_time timestamp without time zone NOT NULL,
    modification_date_time timestamp without time zone NOT NULL,
    CONSTRAINT pk_ado PRIMARY KEY (action_id)
);

CREATE INDEX IF NOT EXISTS idx_ado_active_action_id
    ON offer USING btree
    (action_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS idx_ao_modify_create_date_time
    ON offer USING btree
    (modification_date_time DESC NULLS FIRST, creation_date_time DESC NULLS FIRST)
    TABLESPACE pg_default;

Interesting point here is, if I create new table then constraint violation is thrown, on old table I could enter
duplicaterecords. 
Also, we migrated from el7 to el8.

Regards,
Ajinkya

-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Tuesday, June 27, 2023 10:37 PM
To: Ajinkya Tankhiwale <ajinkya.tankhiwale@tcs.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18002: Duplicate entries of row possible even after having primary key

"External email. Open with Caution"

Hi,

On 2023-06-27 11:03:16 +0000, PG Bug reporting form wrote:
> Bug reference:      18002
> Logged by:          Ajinkya Tankhiwale
> Email address:      ajinkya.tankhiwale@tcs.com
> PostgreSQL version: 14.7
> Operating system:   RHEL 8.8
> Description:
>
> We migrated from 13.7 to 14.7 recently, in current data setup, we say
> that duplicate records are present in table even after primary key. I
> see that in the past, same issue was reported and possible solution
> was to run full vacuum or re-index. We tried both, but still able to
> enter duplicate records.
>
> This is blocker for us and would like to have solution for this as
> soon as possible.

You haven't provided information that'd allow us to debug the issue. At the very least we'd need table + index
definitionand the inserts that are allowed to happen despite being duplicates.  Even better would be a way to reproduce
theissue. 

You said that you migrated from 13.7 to 14.7 recently - did you also change your OS version?

Greetings,

Andres Freund

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

On Tue, 27 Jun 2023 at 23:24, PG Bug reporting form
<noreply@postgresql.org> wrote:
> We migrated from 13.7 to 14.7 recently, in current data setup, we say that
> duplicate records are present in table even after primary key. I see that in
> the past, same issue was reported and possible solution was to run full
> vacuum or re-index. We tried both, but still able to enter duplicate
> records.

This is quite strange. A VACUUM FULL will create a new heap and
indexes and copy over all tuples from the old heap. If there was some
index corruption, then this really should fix it.

Did you remove the duplicates before performing the VACUUM FULL?

Does the table still have duplicates now?

If so, it would be interesting to see the result of:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET LOCAL enable_seqscan=0;
EXPLAIN SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING
count(*)>1;
SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING count(*)>1;
RESET enable_seqscan;
SET LOCAL enable_indexscan=0;
SET LOCAL enable_indexonlyscan=0;
SET LOCAL enable_bitmapscan=0;
EXPLAIN SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING
count(*)>1;
SELECT action_id,COUNT(*) from offer GROUP BY action_id HAVING count(*)>1;
COMMIT;

David

Hi,

On 2023-06-28 08:38:03 +0000, Ajinkya Tankhiwale wrote:
> CREATE TABLE IF NOT EXISTS offer
> (
>     action_id bigint NOT NULL DEFAULT nextval('trade.seq_ao_id'::regclass),
>     action_type character varying COLLATE pg_catalog."default" NOT NULL,
>     buy_sell_indicator character varying COLLATE pg_catalog."default" NOT NULL,
>     creation_date_time timestamp without time zone NOT NULL,
>     modification_date_time timestamp without time zone NOT NULL,
>     CONSTRAINT pk_ado PRIMARY KEY (action_id)
> );

Just to check, the non-detected uniqueness violations are in this table?

How exactly are you inserting?

As the primary key is using a sequence, you shouldn't even get to the
situation that there could be a uniqueness violation.


> Interesting point here is, if I create new table then constraint violation is thrown, on old table I could enter
duplicaterecords.
 
> Also, we migrated from el7 to el8.

If the relevant constraints were on some form of a string, I'd suspect that
you ran into a corrupted index due to collation changes between el7 and
el8. But the pkey of the table above is on a bigint...

Greetings,

Andres Freund

=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain
confidential or privileged information. If you are
not the intended recipient, any dissemination, use,
review, distribution, printing or copying of the
information contained in this e-mail message
and/or attachments to it are strictly prohibited. If
you have received this communication in error,
please notify us by reply e-mail or telephone and
immediately and permanently delete the message
and any attachments. Thank you

On Fri, 30 Jun 2023 at 00:13, Ajinkya Tankhiwale
<ajinkya.tankhiwale@tcs.com> wrote:
> Please find below snippet from pgAdmin
>
> I was able to enter duplicates from pgAdmin.

It's very unclear what's going on here. In the screenshots you
included the problem table seems to be named trade.addressed_offer and
the primary key column seems to be pre_trade_action_id, yet a few
emails ago the problem table was "offer" with that primary key on the
"action_id" column.

It might be best if you start again and explain the problem and
include the table name of the table that is actually causing the
issue. Use psql instead of pgAdmin and show us the output of:

\d name_of_the_problem_table

then try executing the commands that you expect to fail but are not.
Include the output here.  Include the same GROUP BY ... HAVING
COUNT(*) > 1 that shows the duplicates, then try performing a VACUUM
FULL. If you cannot afford the access exclusive lock, then at least a
CREATE UNIQUE INDEX CONCURRENTLY to show that it creates and includes
the duplicates.

As of now, this seems to be more likely due to operator error, so you
might need to start being a bit more concise to prove that's not the
case.

David

On Sun, Jul 2, 2023 at 2:53 PM David Rowley <dgrowleyml@gmail.com> wrote:
> It might be best if you start again and explain the problem and
> include the table name of the table that is actually causing the
> issue. Use psql instead of pgAdmin and show us the output of:
>
> \d name_of_the_problem_table

They're on 14, so I suggest that pg_amcheck be used for this.

Ajinkya: Something like this ought to do it:

pg_amcheck --install-missing --heapallindexed -d postgres

What does that command output? Can you let us know?

--
Peter Geoghegan