CODEWITHSUNDEEP
sqlpostgresqlpostgresql-12
Lars
Lars

Reputation: 243

Postgres SQL query inconsistency

How is it possible to get a different result (0 rows vs 272 rows) when running SELECT * vs COUNT(*) with the same query in Postgres 12 on Linux? The database is not changing. I tried vacuuming and restarting the Postgres server. I am manually trying to clean up a corrupted database by removing some references to an unimportant table that didn't get properly deleted and keeps throwing errors. Yes, I backed up all the data first.

I am mostly wondering if this is a bug, or I am misunderstanding how to use SQL. Also note that adding an AND condition increases the number of results for COUNT, which does not make sense to me.

Psql console dump:

postgres=# SELECT COUNT(*) FROM pg_attribute WHERE pg_attribute.attrelid >= 2128137 AND pg_attribute.attrelid <= 2132360;
 count 
-------
     0
(1 row)

postgres=# SELECT COUNT(*) FROM pg_attribute WHERE pg_attribute.attrelid >= 2128137 AND pg_attribute.attrelid <= 2132360 AND pg_attribute.attname NOT ILIKE '%pg.dropped%';
 count 
-------
   272
(1 row)

postgres=# SELECT COUNT(*) FROM pg_attribute WHERE pg_attribute.attrelid >= 2128137 AND pg_attribute.attrelid <= 2132360 AND pg_attribute.attname NOT ILIKE '%pg.dropped%';
 count 
-------
   272
(1 row)

postgres=# SELECT * FROM pg_attribute WHERE pg_attribute.attrelid >= 2128137 AND pg_attribute.attrelid <= 2132360 AND pg_attribute.attname NOT ILIKE '%pg.dropped%';
(returns 0 rows in editor)

Upvotes: 0

Views: 421

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

It is “normal” for queries on a corrupted database to return wrong results.

Rather than trying to fix the corruption (where you can never be certain that there may not be more corruption be lurking somewhere), pg_dump the database and restore it to a newly created cluster on different hardware. If you are lucky, that will work, and you are fine.

Catalog corruption is the nastiest kind. If the above doesn't work, seek professional help. If you don't know what a query plan is, you won't be able to do that yourself (no insult intended).

Upvotes: 2

Related Questions