Re: postgres catalog files problem

Thanks.
Actually Im currently testing "oracle to postgres migration" with a small
dataset. I haven't started working with the real dataset. I wanted to check
with a small dataset before I start with the big one.
I know the records that I deleted from the pg_class file. so what do i do
next? Will it help if I drop the problematic tables and recreate them? (like
i said im currently testing with a small datset only and my real data are in
oracle and they are not messed in nay way). What will happen to my catalog
files if i do that?
Josh

n 9/17/07, Erik Jones <erik(at)myemma(dot)com> wrote:
>
> 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(r)
> 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
>
>
>