Thread: cannot find attribute 1 of relation (second occurrence.)

cannot find attribute 1 of relation (second occurrence.)

From
"Nick Fankhauser"
Date:
Hi-

We've just seen the following error for a second time since upgrading to
7.2.1-2:

cannot find attribute 1 of relation <tablename>

The first time, Tom Lane helped us by suggesting a reindex of pg_attribute.
I tried this on the second case as well, and it solved the problem.

At this point, I'm concerned because I've never encountered this error
before in about 2 years of postgres use, but now I've seen it twice in the
last two weeks, so I need to figure out why it happened.

Some questions & conjecture:

Has anyone else encountered this problem on 7.2.1-2? The two major recent
changes that I can think of are our upgrade to 7.2 & new hardware. Hardware
seems the likely culprit, but if others have experience similar errors on
7.2, I'd squint more closely at the software.

The first time I encountered this error was on a massive dump file reload.
The second time, the only coincidence I can think of was updating
pg_attribute.atttypmod for several records last night. Could this have
caused my problem?

Any other ideas?

Thanks everyone!

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


Re: cannot find attribute 1 of relation (second occurrence.)

From
Tom Lane
Date:
"Nick Fankhauser" <nickf@ontko.com> writes:
> We've just seen the following error for a second time since upgrading to
> 7.2.1-2:
> cannot find attribute 1 of relation <tablename>
> The first time, Tom Lane helped us by suggesting a reindex of pg_attribute.
> I tried this on the second case as well, and it solved the problem.

> At this point, I'm concerned because I've never encountered this error
> before in about 2 years of postgres use, but now I've seen it twice in the
> last two weeks, so I need to figure out why it happened.

We haven't seen any other reports of this that I can recall, so I'm
leaning towards the bad-hardware theory, but I never like blaming
problems on hardware.  Next time it happens, would you shut down the
postmaster and make a copy of pg_attribute and its indexes (the physical
files) to send to me, before you reindex?  Eyeballing the remains might
give some evidence one way or the other.

            regards, tom lane

Re: cannot find attribute 1 of relation (second occurrence.)

From
"Nick Fankhauser"
Date:
Tom-

No Problem- we'll do that. Is there a table that contains the mapping from
the database object names to the actual filenames?

-Nick

> Next time it happens, would you shut down the
> postmaster and make a copy of pg_attribute and its indexes (the physical
> files) to send to me, before you reindex?  Eyeballing the remains might
> give some evidence one way or the other.


Re: cannot find attribute 1 of relation (second occurrence.)

From
Tom Lane
Date:
"Nick Fankhauser" <nickf@ontko.com> writes:
> No Problem- we'll do that. Is there a table that contains the mapping from
> the database object names to the actual filenames?

Look at the relfilenode column of pg_class for the individual file
names, and at the OID column of pg_database for the directory names.
Probably would be wise to have notes premade about the filenames of
pg_attribute and all its indexes ...

contrib/oid2name may be useful.

            regards, tom lane