Re: [GENERAL] postgres catalog files problem
Josh Harrison [EMAIL PROTECTED] writes: if I give this query, which checks for the triggers that do not have corresponding tables in the pg_class select * from pg_trigger where tgrelid not in (select relfilenode from pg_class), This query is wrong --- relfilenode is not a join column for any other table. You should be using pg_class.oid there. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres catalog files problem
Thanks Tom. I tried it using pg_class.oid and I still have some records which r not in the pg_class files. What can I do about that? (BTW what does relfilenode in pg_class stands for?) Thanks again Josh On 9/17/07, Tom Lane [EMAIL PROTECTED] wrote: Josh Harrison [EMAIL PROTECTED] writes: if I give this query, which checks for the triggers that do not have corresponding tables in the pg_class select * from pg_trigger where tgrelid not in (select relfilenode from pg_class), This query is wrong --- relfilenode is not a join column for any other table. You should be using pg_class.oid there. regards, tom lane
Re: [GENERAL] postgres catalog files problem
On Sep 17, 2007, at 9:02 AM, Josh Harrison wrote: On 9/17/07, Tom Lane [EMAIL PROTECTED] wrote: Josh Harrison [EMAIL PROTECTED] writes: if I give this query, which checks for the triggers that do not have corresponding tables in the pg_class select * from pg_trigger where tgrelid not in (select relfilenode from pg_class), This query is wrong --- relfilenode is not a join column for any other table. You should be using pg_class.oid there. regards, tom lane Thanks Tom. I tried it using pg_class.oid and I still have some records which r not in the pg_class files. What can I do about that? (BTW what does relfilenode in pg_class stands for?) Thanks again Josh relfilenode is the name of the actual file that holds the given relation's data. Now that you've got that query right, can you give us some more details about what's happening? Just to be sure, when checking triggers, the query should be select * from pg_trigger where tgrelid not in (select oid from pg_class); What are the queries that are generating these errors? Why are you looking at pg_trigger? In order to help, we'll need to know more about the situation that just the error type as different situations can yield that. Do the same queries consistently yield the error? Do you see the same oids in the errors, or do they change? Do you use CLUSTER on any kind of regular basis? Have you had any other kind of abnormal performance issues (other errors, system crashes, etc...)? The more info you give, the better help you can receive. Erik Jones Software Developer | Emma® [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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgres catalog files problem
Thanks Eric, ll try to explain as much as possible 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 This error was consistent for this query and it yielded the same OID every time. When I checked the pg_class with oid=672178, there were no records found with that OID. So I checked the other catalog files to see if there are any reference to that OID in any of them. I found a couple of them in the pg_trigger files that references that OID 672178. That is why I tried the first query ie., select * from pg_trigger where tgrelid not in (select oid from pg_class) to see if there are any other rows in pg_trigger without corresponding OID in pg_class. Is it common to have records in pg_triggers and other catalog files iwthout corresponding OID in the pg_class file? Do you use CLUSTER on any kind of regular basis? Have you had any other kind of abnormal performance issues (other errors, system crashes, etc...)? I don't use cluster on any kind. Im not sure about the performance since Im working with a very small test dataset. Thanks Josh On 9/17/07, Erik Jones [EMAIL PROTECTED] wrote: On Sep 17, 2007, at 9:02 AM, Josh Harrison wrote: On 9/17/07, Tom Lane [EMAIL PROTECTED] wrote: Josh Harrison [EMAIL PROTECTED] writes: if I give this query, which checks for the triggers that do not have corresponding tables in the pg_class select * from pg_trigger where tgrelid not in (select relfilenode from pg_class), This query is wrong --- relfilenode is not a join column for any other table. You should be using pg_class.oid there. regards, tom lane Thanks Tom. I tried it using pg_class.oid and I still have some records which r not in the pg_class files. What can I do about that? (BTW what does relfilenode in pg_class stands for?) Thanks again Josh relfilenode is the name of the actual file that holds the given relation's data. Now that you've got that query right, can you give us some more details about what's happening? Just to be sure, when checking triggers, the query should be select * from pg_trigger where tgrelid not in (select oid from pg_class); What are the queries that are generating these errors? Why are you looking at pg_trigger? In order to help, we'll need to know more about the situation that just the error type as different situations can yield that. Do the same queries consistently yield the error? Do you see the same oids in the errors, or do they change? Do you use CLUSTER on any kind of regular basis? Have you had any other kind of abnormal performance issues (other errors, system crashes, etc...)? The more info you give, the better help you can receive. 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
Re: [GENERAL] postgres catalog files problem
Josh Harrison [EMAIL PROTECTED] writes: 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 ? Well, are there any other relations that the UPDATE might need to touch? (Think about foreign keys, or even just indexes.) 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? Egad. That is *definitely* in the category of don't do that if you're not damn sure of what you're doing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres catalog files problem
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® [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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgres catalog files problem
On Sep 17, 2007, at 12:31 PM, Josh Harrison wrote: 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 If you can successfully drop those tables, then yes. Given that this is just a test database, if you have any issues doing that, I'd scrap the whole database. Erik Jones Software Developer | Emma® [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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgres catalog files problem
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 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
Re: [GENERAL] postgres catalog files problem
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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 [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
