CODEWITHSUNDEEP
sqlpostgresql
Yegor Lukash
Yegor Lukash

Reputation: 510

Sequential Scan and Index Scan for primary key return different rows

I have problem with the data in my table users. This table has a primary key defined as:

"primary_c26a3d1a9d1c7aa4bb0a8d6b752c01a7" PRIMARY KEY, btree (id)

When I use a WITH clause to force a sequential scan on the table, I find duplicate IDs:

=> WITH temp_table AS (select * from "users") SELECT id from temp_table group by id having count(id) > 1;
-[ RECORD 1 ]
id | 8225700
-[ RECORD 2 ]
id | 8225682
...

How does this happen? If I search for these duplicates by index, I don't have the same problem:

=> select count(*) from users where id = 8225700;
-[ RECORD 1 ]
count | 1

I am using PostgreSQL 9.1.

VACUUM did not help me. I tried to delete duplicates by ctid:

// good and bad rows
> with usrs as (select ctid, * from users) select ctid, id from usrs where id = 8225700;
ctid     |   id    
-------------+---------
(195669,33) | 8225700
(195708,34) | 8225700

// good row
select id, ctid from users where id = 8225700;
-[ RECORD 1 ]-----
id   | 8225700
ctid | (195708,34)

// deleting bad row
DELETE FROM users WHERE ctid = '(195669,33)';
ERROR:  update or delete on table "users" violates foreign key constraint   "foreign_1589fcbc580d08caf03e0fbaaca7d6dd" on table "account"

In detail: Key (id)=(8225700) is still referenced from the account table.

But the real row has references and I can't delete it.

How can I delete these broken rows?

Upvotes: 17

Views: 868

Answers (2)

Joe Love
Joe Love

Reputation: 5962

Sounds like a bad index to me. Try rebuilding your indexes.

reindex table users

Upvotes: 0

filiprem
filiprem

Reputation: 7124

// deleting bad row DELETE FROM users WHERE ctid = '(195669,33)';
ERROR:  update or delete on table "users" violates foreign key
constraint   "foreign_1589fcbc580d08caf03e0fbaaca7d6dd" on table
"account"

In detail: Key (id)=(8225700) is still referenced from table
"account".

It says quite clearly: The row is referenced by account table.

You need to locate the reference and fix it. 

UPDATE account SET fkey_field = ??? WHERE ... ;

Details depend on structure and contents of account table.

If you want more help, please paste full output of \d account and \d users from psql.

Upvotes: 1

Related Questions