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 ?

Could you run `EXPLAIN` for those queries? That might explain it (no pun intended). Probably using a different index or something. All indexes, data etc are in separate files so if one is corrupted the others may very well be ok — Sami Kuhmonen, Mar 06 '19 at 07:59
Postgres 8.2 is long dead and forgotten. You should really plan an upgrade to a [supported version](https://www.postgresql.org/support/versioning/) **now** — , Mar 06 '19 at 08:03
@SamiKuhmonen, this is the return while i run EXPLAIN "Nested Loop (cost=0.00..27811.22 rows=1 width=2029)" " -> Seq Scan on table2 (cost=0.00..24881.40 rows=307 width=8)" " Filter: (table2. date = '2019-03-06'::date)" " -> Index Scan using table1_pkey on table1 (cost=0.00..9.53 rows=1 width=2021)" " Index Cond: (table2.tb1_id = table1.id)" " Filter: (table1.date = '2019-03-06'::date)" — adhi, Mar 13 '19 at 08:08

score 0 · Answer 1 · edited Mar 24 '19 at 17:08

0

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.

edited Mar 24 '19 at 17:08

marc_s

732,580
175
1,330
1,459

answered Mar 06 '19 at 08:35

Vlad Călin Buzea

559
5
19

i've try it, but the result is still : ERROR: invalid page header in block 11729 of relation "table1_pkey" SQL state: XX001 – adhi Mar 13 '19 at 08:14

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

1 Answers1