Re: BUG #18002: Duplicate entries of row possible even after having primary key

On Tue, 27 Jun 2023 at 23:24, PG Bug reporting form
<noreply(at)postgresql(dot)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