cannot open segment 1 of relation .... No such file or directory

[o***@yahoo.com]

Hello,

I'm using PG 7.3.4 under RedHat 9.0 and I've started noticing the
following type of errors:

cannot open segment 1 of relation url (target block 537329664): No
such file or directory

How can I determine what exactly is corrupt or missing and which row is
triggering this?

I have found pgfsck (http://svana.org/kleptog/pgsql/pgfsck.html), but
that gives me some funky, unrelated(?) output, like this:


-- Detected database format 7.3
-- Scanning table url
-- Indexing toast table
-- Index complete: 0 toasted tuples
-- Table url(17129): *** SERIOUS ***: No definition found for column 1
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 2
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 3
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 4
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 5
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 6
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 7
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 8
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 9
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 10
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 11
-- Table url(17129): Found old version, continuing
-- Table url(17129): *** SERIOUS ***: No definition found for column 12
-- Table url(17129): Found old version, continuing
-- Table url(17129):Page 0:Tuple 0: Unknown type timestamp (1114)
-- Table url(17129):Page 0:Tuple 0: Unknown type timestamp (1114)
-- Table url(17129):Page 0:Tuple 1: Unknown type timestamp (1114)
-- Table url(17129):Page 0:Tuple 1: Unknown type timestamp (1114)
...
... keeps going through Pages with the same timestamp error ...
...

As far as I can tell, the above errors/warnings are not related to the
error I am getting.


I do have a dump of this DB, in case that can be used to fix the
corrupt tuple.

I dump this DB every night with:

pg_dump -d xxx --clean --inserts --column-inserts --format=P -v


Any help would be much appreciated.

Thanks,
Otis


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by o***@yahoo.com
I'm using PG 7.3.4 under RedHat 9.0 and I've started noticing the
cannot open segment 1 of relation url (target block 537329664): No
such file or directory

If this is coming from an indexscan query, then I'd bet on the problem
being a corrupted index on the "url" table. REINDEX may get you out of
it. If not, try dumping and reloading that table (or the whole database
if need be).

7.3.4 is a bit old --- you should at least move up to 7.3.8, if not
7.4.something.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[o***@yahoo.com]

Hello,

Post by Tom Lane

Post by o***@yahoo.com
I'm using PG 7.3.4 under RedHat 9.0 and I've started noticing the

No

Post by o***@yahoo.com
such file or directory

If this is coming from an indexscan query, then I'd bet on the
problem
being a corrupted index on the "url" table. REINDEX may get you out
of it. If not, try dumping and reloading that table (or the whole
database if need be).

Thanks, I'll try REINDEX shortly. Before I do that, though, I'm
wondering if it's somehow possible to get the details of this problem -
I'd love to see which DB file PG is actually looking for.

Post by Tom Lane
7.3.4 is a bit old --- you should at least move up to 7.3.8, if not
7.4.something.

Thanks for the info - I'll upgrade to 7.3.8 then, too.

Otis


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend