2

I discovered that my 3-column (integers, not null) primary key is allowing duplicates! Then I created new unique index and replace the current one, and the problem still exists, how is that even possible!? When select is using that index, it doesn't return replicated rows, I have to force seq scan then it's found.

@edit:

create table balance
(customer_id integer not null, 
period_id integer not null, 
promotion_id integer not null,
-- (and few other columns)
CONSTRAINT balance_pk PRIMARY KEY (customer_id, period_id, promotion_id)
 )

It was unique index promoted to PK using:

ALTER TABLE balance ADD CONSTRAINT balance_pk PRIMARY KEY USING INDEX balance_pk;
5
  • 2
    It's pretty hard to see your table and index definitions from here; do you mind adding them to your question? Steps to reproduce the problem would also be helpful.
    – mustaccio
    Apr 22, 2022 at 20:37
  • I added it, but I don't think that would be any closer to the answer now, because there's no room for something wrong with the DDL itself, that should be quite simple, PK = no duplicates
    – sh4rkyy
    Apr 22, 2022 at 21:32
  • 1
    Works just fine - apparently there is more to that problem than you are telling use. You need to provide a self-contained test case that shows this behaviour
    – user1822
    Apr 23, 2022 at 5:50
  • That is totally random, application is inserting few thousands of records per minute, and there are just few duplicates per hour. The query application is using includes ON CONFLICT (customer_id, period_id, promotion_id) DO UPDATE SET points = balance.points + excluded.points
    – sh4rkyy
    Apr 23, 2022 at 9:29
  • 1
    might be related to Prevent possible corruption of indexes created or rebuilt with the CONCURRENTLY option (Álvaro Herrera) postgresql.org/docs/release/14.4
    – brauliobo
    Aug 29, 2022 at 15:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.