Thread: DB corrupted ?

Hi all,

I use Postgres 7.0 on a mandrake 8.0 and I am really novice
with Postgres and SQL.

If I try :
select * from cpjo order by joMA, joJNL, joFOL ;
I obtain :

ERROR:  cannot open segment 1 of relation cpjo
 (target block 1346502656): No such file or directory

but the same request on an other table works well and if if try :
select * from cpjo where joma='200201' order by joMA, joJNL, joFOL ;
                             ================
this works fine also !
joma, jojnl and jofol are the primary key of cpjo and there is nothing
before '200201' in it.

It seem to me that the DB is corrupted
but what I must do for repairs it ?
VACUUM, REINDEX ?

Thanks,
--
Alain Lucari    Eurlix
1, rue Reine Elisabeth von Witellsbach
       (Reine des Belges)
13001 Marseille
FRANCE

eurlix <eurlix@libertysurf.fr> writes:
> I use Postgres 7.0 on a mandrake 8.0 and I am really novice
> with Postgres and SQL.

You really, really should update to something newer than 7.0.
The list of bugs we've fixed since then would make your blood run cold.

> If I try :
> select * from cpjo order by joMA, joJNL, joFOL ;
> I obtain :
> ERROR:  cannot open segment 1 of relation cpjo
>  (target block 1346502656): No such file or directory

Is that query using an index?  (Try EXPLAINing the query to find out.)
If so, I'd suggest dropping and recreating that index.  7.0 is far
enough back that I'm not sure REINDEX works real well in it, if it
has it at all.

            regards, tom lane

Hi Tom,

Le Wed, 29 Jan 2003 11:09:37 -0500
Tom Lane <tgl@sss.pgh.pa.us> a écrit:

> eurlix <eurlix@libertysurf.fr> writes:
> > I use Postgres 7.0 on a mandrake 8.0 and I am really novice
> > with Postgres and SQL.
>
> You really, really should update to something newer than 7.0.
> The list of bugs we've fixed since then would make your blood run
> cold.

Ok, if you say that, I shall to go to update (but it seem to be a big work).
Otherwise, I have fastly read again the ~940 lines of HISTORY between 7.0
and 7.2 and, unless because the season, my blood isn't too cold ^-).
In fact, I just try to use pg in replacement of ISAM files (in a first
time) for COBOL progams.

>
> > If I try :
> > select * from cpjo order by joMA, joJNL, joFOL ;
> > I obtain :
> > ERROR:  cannot open segment 1 of relation cpjo
> >  (target block 1346502656): No such file or directory
>
> Is that query using an index?  (Try EXPLAINing the query to find
> out.) If so, I'd suggest dropping and recreating that index.  7.0 is
> far enough back that I'm not sure REINDEX works real well in it, if
> it has it at all.

I have tried "reindex table cpjo" and obtained
NOTICE: table 'cpjo' wasn't reindexed
but "reindex table cpjo force;" seem to works
and now the previous request works.
Explain give :
eurlix=> explain select * from cpjo order by joma, jojnl,jofol;
NOTICE:  QUERY PLAN:

Sort  (cost=174.93..174.93 rows=394 width=216)
  ->  Seq Scan on cpjo  (cost=0.00..157.94 rows=394 width=216)

EXPLAIN

If I well understand (my english is not very good), pg make a sequential
research on the table : curious because joma+jojnl+jofol is the primary
an unique index. Perhaps because I ask all the table, but I hope it don't
make a sort after !
In all cases, it is very fast, and if I type :
eurlix=> explain select * from cpjo where joma='200206' order by joma, jojnl,jofol;
NOTICE:  QUERY PLAN:

Index Scan using cpjo_pkey on cpjo  (cost=0.00..5.15 rows=4 width=216)

EXPLAIN

seem this works not too bad.

Thanks and Regards,
--
Alain Lucari    Eurlix
1, rue Reine Elisabeth von Witellsbach
       (Reine des Belges)
13001 Marseille
FRANCE