Postgres Error : Invalid Page header in block xxx, But Fine while run with 'Order By'

Question

I run this query on Postgres 8.2 Database (Windows) :

SELECT
    *
FROM
    (SELECT * FROM table1 tb1 WHERE date='2019-03-06' ) tb1
    JOIN table2 tb2 ON
        tb2.tb1_id = tb1.id
WHERE
    tb2.date ='2019-03-06'

then i got this error message :

ERROR: invalid page header in block 11729 of relation "table1_pkey" SQL state: XX001

But, when I add Order By clause, this query is running well.

SELECT
    *
FROM
    (SELECT * FROM table1 tb1 WHERE date='2019-03-06' ORDER BY id) tb1  
    JOIN table2 tb2 ON
        tb2.tb1_id = tb1.id
WHERE
    tb2.date = '2019-03-06'

AFAIK, error "invalid page header in block xxx" mostly because of bad memory or a bad drive (link : Postgres Database Error Invalid Page Header).

But, how could it run when I add Order Byclause ?

Answer 1

I think your query has trouble evaluating what columns are actually returned by SELECT * FROM table1, so it does not know if tb1.id exists and is a primary key.

As I see your query, you could do a simple join like this:

SELECT
    *
FROM
    table1 tb1  
JOIN 
    table2 tb2 ON tb2.tb1_id = tb1.id
WHERE
    tb2.date = '2019-03-06' AND tb1.date = '2019-03-06'

The results should be the same. Yes, this could bring a penalty in performance, but it should not be significant.