We tried to run a full vacuum on our Postgres database the other day because of disk problems. A bunch of things went wrong along the way and eventually we stopped the vacuum.
Now we have some new issues. For example, this constraint on our transactions table:
CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
If I try to count the rows for transaction_id 12345 with seqscan=on:
set enable_seqscan=on; with cte as ( select count(*), transaction_id from transactions group by transaction_id ) select count from cte where transaction_id='12345';
The result is a count of 12.
And if I run the same count with secscan=off (to use transactions_pkey):
set enable_seqscan=off; with cte as ( select count(*), transaction_id from transactions group by transaction_id ) select count from cte where transaction_id='12345';
That returns a count of 8.
I only use an awkward count query so that the first example doesn't use the index.
The sequential scan shows 12 rows and the index scan shows 8 rows, but the pkey constraint shouldn't allow more than a single row.
Maybe there was a short period of time during the failed vacuum where the pkey was ignored and duplicate records were inserted? Then somehow the pkey was re-added?
We've tried to script our own cleanup, but deciding which records to keep/update/remove for hundreds of tables with unique constraints has been troublesome. We might need to try another full vacuum, but our database seems to be in an uncommon error state and we're not sure if a vacuum will fix it or make the problem worse.