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

On Fri, 30 Jun 2023 at 00:13, Ajinkya Tankhiwale
<ajinkya(dot)tankhiwale(at)tcs(dot)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