Postgres SQL query inconsistency

Question

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)

What are the plans for the queries? "I am manually trying to clean up a corrupted database" well, if your database is known to be corrupted, that seems like a sufficient explanation for pretty much any observation. — jjanes
– jjanes, Commented Oct 27, 2021 at 18:50
The plan is to replace the SELECT * with a DELETE, to remove references to attributes that should have been deleted (which also deletes 0 results right now). All of the other tables in the database seems to be working fine, so I've held out hope it's a recoverable bug. The original Postgres bug/error message which prevents creating a new table of the same name is this: "ERROR: catalog is missing 16 attribute(s) for relid 2128167" — Lars
– Lars, Commented Oct 27, 2021 at 19:01

Laurenz Albe · Accepted Answer · 2021-10-28 03:19:35Z

2

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).

answered Oct 28, 2021 at 3:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres SQL query inconsistency

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related