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 <[EMAIL PROTECTED]> wrote: > > On Sep 17, 2007, at 11:57 AM, Josh Harrison wrote: > > > > On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Josh Harrison" > > <[EMAIL PROTECTED]> 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) > [EMAIL PROTECTED] > 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 > > >
