Re: postgres catalog files problem

On Sep 17, 2007, at 11:57 AM, Josh Harrison wrote:
>
> On 9/17/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: "Josh Harrison"
> <joshques(at)gmail(dot)com> writes:
> > When i try to give this query
>
> > UPDATE payment
> > SET desc='New description'
> > WHERE payment_id='xyz'
>
> > I got the error
> > ERROR: could not open relation with OID 672178
>
> Hmm, there apparently *is* a pg_class row for relation 'payment', else
> you'd not get this far, and I'll bet it's got OID 672178 --- try
> "select
> oid from pg_class where relname = 'payment'" to see. If so, it seems
> likely that this is just an index corruption and you can get out of it
> by REINDEXing pg_class_oid_index. Depending on what PG version you
> are
> using, that may require special setup --- read the REINDEX reference
> page *for your version* before proceeding.
>
> What version is it, anyway, and what were you doing before you got
> this
> error the first time? This isn't exactly an everyday type of problem.
>
> regards, tom lane
> Hi,
> Yes...there is a relation in pg_class with the name 'payment' but
> its oid is not 672178. So why is it giving me "could not open
> relation with OID 672178" when i try an update statement ?
> I use version 8.2. I think the problem started when i manually
> deleted some rows from the pg_class catalog file instead of using
> 'drop table' sql command. Do you think this created the problem?
>
> Thanks
> josh
>

Yep, that would do it. Never manually edit catalog tables unless you
*really* know what you're doing and then think ten times about it
first. My guess is that you deleted an entry for a TOAST table or
index on that table and there are still entries in pg_depend (as well
as others) so that when you try to access that table it isn't finding
the related, dependant objects. Others may know more, but I don't
know enough to help you get your catalogs back in order past
restoring from a backup. Also, if I were you I'd see if you can get
a dump of the current database first thing. Do you know what you
deleted from pg_class?

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com