Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 9D6592E0044 for ; Thu, 17 Apr 2008 07:09:15 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15660-09 for ; Thu, 17 Apr 2008 07:09:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.180]) by developer.postgresql.org (Postfix) with ESMTP id 327972E0068 for ; Thu, 17 Apr 2008 07:09:02 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id n7so5968wag.5 for ; Thu, 17 Apr 2008 03:08:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=VHSV/vv2de3V/VzahotaCQTGNzGRZq1XXcTgYJ44HdM=; b=BiErojKJOKrF6DQSF9XCOZ+bjah5hKm0ogB3TRc11WcRNYKKQqDhkdZMk5/L/DotDqGtRpbCaI7tTNoQKApxb1PpNSCKjullDxiHo93YykT/4I3lzIidU9ph2xH0lD8i0E0+zUsbovOOxjQcjMcXIXudyEL5e3qAGtBXOPRPuBY= 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=cPIXHbF5w04Ih9OGKwBGZ91PiZN8wn3ig4yKadY4S3EQKWyP/USZeKZ9B6eP/V8HY6bK6YaGLsVluGEzIIxHxuX1ICA2WE1aZJesm63j7LfuvSBO6j955du92EAYYk3B8DJ/zBFOC2/+locjG3/LHuZx4lx0i1TlQs/6GHtGE8E= Received: by 10.115.76.5 with SMTP id d5mr1153889wal.191.1208426938740; Thu, 17 Apr 2008 03:08:58 -0700 (PDT) Received: by 10.114.102.14 with HTTP; Thu, 17 Apr 2008 03:08:58 -0700 (PDT) Message-ID: <2ca799770804170308m367eaea2nc7899198c0b260e0@mail.gmail.com> Date: Thu, 17 Apr 2008 13:08:58 +0300 From: "Mikko Partio" To: pgsql-admin@postgresql.org Subject: Re: FATAL: could not open relation xxx: No such file or directory In-Reply-To: <2ca799770804142336q6422af38me80054c82699d1e2@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3475_9275177.1208426938734" References: <2ca799770804090647m759b72f9gf2d49b4bf7cc8bdf@mail.gmail.com> <2ca799770804142336q6422af38me80054c82699d1e2@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/168 X-Sequence-Number: 28932 ------=_Part_3475_9275177.1208426938734 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Tue, Apr 15, 2008 at 9:36 AM, Mikko Partio wrote: > > > On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio 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 happene > And now it has happened again. A CLUSTER operation was done on a table succesfully, afterwards when trying to access the table I get the error 2008-04-17 13:05:30 EEST [8435]: [32-1] ERROR: could not open relation 1663/16386/359232: No such file or directory Seems to me like VACUUM FULL, REINDEX and CLUSTER change the filename of a table and/or index and then fail to record the new name to system catalogues. Is this a known deficiency what can I do to stop this behaviour? Regards Mikko ------=_Part_3475_9275177.1208426938734 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On Tue, Apr 15, 2008 at 9:36 AM, Mikko Partio <mpartio@gmail.com> wrote:


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 happene

And now it has happened again. A CLUSTER operation was done on a table succesfully, afterwards when trying to access the table I get the error

2008-04-17 13:05:30 EEST [8435]: [32-1] ERROR:  could not open relation 1663/16386/359232: No such file or directory

Seems to me like VACUUM FULL, REINDEX and CLUSTER change the filename of a table and/or index and then fail to record the new name to system catalogues. Is this a known deficiency what can I do to stop this behaviour?

Regards

Mikko
------=_Part_3475_9275177.1208426938734--