Re: Really odd corruption problem: cannot open pg_aggregate: No such file or directory

I too got this error. This happened with Postgres 7.2.3 and Linux 2.4.20 on via processor. This happened not on just one box but around dozen boxes. This may not be hardware problem.

In our case, we create the table and use it then delete it. This activity happens very often, once a day. we run vaccum also. The problem happened on one such table. The entry for the table exists in pg_class but the actual file is missing. Once it gets to this state, the table can not be dropped.

Were there any bug fixes related to this in the later versions of postgres. I searched in the google for this error and got some cases but not much information why.

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=%22RelationBuildDesc%3A+can%27t+open%22

Thanks,
balaji.

-----Original Message-----
From: Adam Haberlach [mailto:adam(at)newsnipple(dot)com]
Sent: Thu 7/24/2003 11:07 AM
To: pgsql-hackers(at)postgresql(dot)org
Cc:
Subject: Re: [HACKERS] Really odd corruption problem: cannot open pg_aggregate: No such file or directory
On Thu, Jul 24, 2003 at 10:17:06AM -0700, Adam Haberlach wrote:
> So, one of the many machines that I support seems to have developed
> an incredibly odd and specific corruption that I've never seen before.
>
> Whenever a query requiring an aggregate is attempted, it spits out:
> cannot open pg_aggregate: No such file or directory
> and fails.
>
> If I do:
> select * from pg_class where relname='pg_aggregate';
> I see that the relation exists.
>
> If I check the relfilenode in the data directory, that exists, and
> seems to be an object file containing what should be the basic
> aggregate functions.
>
> version: PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
>
>
> The system ran for a few weeks before anything odd happened, and
> then suddenly this. Does anyone have any ideas? Now that I look at
> the above string, I realize that the system /is/ an Athlon processor.
> Does anyone know if there could be an issue between the i686 and

I'd like to thank everyone for the quick responses and the suggestion
to strace the postmaster.

open("/var/lib/pgsql/data/base/16556/16406", O_RDWR) = -1 ENOENT (No such file or directory)

It looks like a file /was/ missing, and I had been looking in the
wrong place to verify that it was there (the template database). I'm
going to chalk this one up to bad hardware and hope it doesn't happen
again. Thanks again...

--
Adam Haberlach | "When your product is stolen by thieves, you
adam(at)mediariffic(dot)com | have a police problem. When it is stolen by
http://mediariffic.com | millions of honest customers, you have a
| marketing problem." - George Gilder

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)