DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=XAyehJlFZXU58XjC9LOS1/8n+drNXEqlFmTxgby+hCvXb34A6TJtfgOyoilXlMB8f6qL+zBwEfZSA3F2zCyr38VRmi5u8etngSB26n+If6AAvHX0aEMXFN4DqroutoAkEXN4aFedxGvPiny61N0+cY3KVjArjJaPR56oDHufSmA=
Message-ID: <2ca799770804142336q6422af38me80054c82699d1e2@mail.gmail.com>
Date: Tue, 15 Apr 2008 09:36:33 +0300
From: "Mikko Partio" <mpartio@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: FATAL: could not open relation xxx: No such file or directory
In-Reply-To: <2ca799770804090647m759b72f9gf2d49b4bf7cc8bdf@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_36542_26640177.1208241393854"
References: <2ca799770804090647m759b72f9gf2d49b4bf7cc8bdf@mail.gmail.com>

------=_Part_36542_26640177.1208241393854
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio <mpartio@gmail.com> wrote:

> Hello all
>
> my struggle with the database continues (see earlier thread titled "too
> many trigger records found for relation xyz").
>
> Today, I created yet another to table to the same database. Everything
> went ok, no errors or anything, but when I checked pg_tables -view I saw two
> tables with the same name. Instantly I queried pg_class and yes there was
> again two tables with same oid. I dropped the table before anything more
> serious could happen, but then postgres started to complain of "cache lookup
> failed for relation ...". I disconnected my psql session and tried to
> reconnect but failed to do so:
>
> 2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL:  could not open relation
> 1663/16386/544592: No such file or directory
>
> Indeed, there is no such file in that directory. I'm guessing that file is
> connected to the table I just dropped. Now, is there anything to do to get
> the database back online? I can still connect to other databases in the same
> instance


The cure was to create file 1663/16386/54459 8K in size with dd. The file in
question was in fact the oid index on pg_class -- I had issued a REINDEX on
pg_class just a moment before and apparantly something went wrong and the
system lost track of the index. There was also two entries in pg_index for
index pg_class_oid_index. After I removed the other entry and reindexed
pg_class and pg_index, everything seems to be working ok. All the symptoms
indicate that perhaps a xid wraparound had happened, but there is no such
warning in logs and age(datfrozenxid) went never higher than say
250,000,000. Does anybody have a clue what might have happened?

Regards

Mikko

------=_Part_36542_26640177.1208241393854
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><br><div class="gmail_quote">On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio &lt;<a href="mailto:mpartio@gmail.com">mpartio@gmail.com</a>&gt; wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hello all<br><br>my struggle with the database continues (see earlier thread titled &quot;too many trigger records found for relation xyz&quot;).<br><br>Today, I created yet another to table to the same database. Everything went ok, no errors or anything, but when I checked pg_tables -view I saw two tables with the same name. Instantly I queried pg_class and yes there was again two tables with same oid. I dropped the table before anything more serious could happen, but then postgres started to complain of &quot;cache lookup failed for relation ...&quot;. I disconnected my psql session and tried to reconnect but failed to do so:<br>

<br>2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL:&nbsp; could not open relation 1663/16386/544592: No such file or directory<br><br>Indeed, there is no such file in that directory. I&#39;m guessing that file is connected to the table I just dropped. Now, is there anything to do to get the database back online? I can still connect to other databases in the same instance</blockquote>
<div><br>The cure was to create file 1663/16386/54459 8K in size with dd. The file in question was in fact the oid index on pg_class -- I had issued a REINDEX on pg_class just a moment before and apparantly something went wrong and the system lost track of the index. There was also two entries in pg_index for index pg_class_oid_index. After I removed the other entry and reindexed pg_class and pg_index, everything seems to be working ok. All the symptoms indicate that perhaps a xid wraparound had happened, but there is no such warning in logs and age(datfrozenxid) went never higher than say 250,000,000. Does anybody have a clue what might have happened?<br>
<br>Regards<br><br>Mikko<br><br><br></div></div><br>

------=_Part_36542_26640177.1208241393854--