Обсуждение: postgres catalog files problem

Hi,
I noticed that I have too many records in my pg_ catalog files with the same name. For example
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),

I get  set of records like this (omitted some fields)

tgrelid                 tgname                                   tgfoid     tgtype          tgconstrname
629324      RI_ConstraintTrigger_654082              1644       5          payment_fk_id      ...                                                                                                                                
 629324      RI_ConstraintTrigger_654083              1645       17        payment_fk_id    ...
........

That is there are records in my pg_trigger that has no corresponding tgrelid in pg_class. Why is that? Is it okay to manually delete these records from the catalog files?

So Im getting this errorthis error when I try to update some tables
' could not open relation with oid'

Thanks for your help
josh

"Josh Harrison" <joshques@gmail.com> 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

On Sep 17, 2007, at 9:02 AM, Josh Harrison wrote:
>
> On 9/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: "Josh Harrison"
> <joshques@gmail.com> 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®
erik@myemma.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

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

"Josh Harrison" <joshques@gmail.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

"Josh Harrison" <joshques@gmail.com> 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

On Sep 17, 2007, at 11:57 AM, Josh Harrison wrote:
>
> On 9/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: "Josh Harrison"
> <joshques@gmail.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@myemma.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

Hi
Sometimes when I create a table with the
CREATE TABLE sql command,
it creates a table but with some warnings

For example, I create this table in Aqua studio as
create table foo(col1 varchar2);

gives

Warnings: --->
   W (1):
          <---
 0 record(s) affected

The table is created. So what does this warnings mean? where and how do i check them?

Thanks in advance

---

Be a better Globetrotter. Get better travel answers from someone who knows.
Yahoo! Answers - Check it out.

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@myemma.com> wrote:

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®
erik@myemma.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

On 9/17/07, SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> wrote:
> Hi
> Sometimes when I create a table with the
> CREATE TABLE sql command,
> it creates a table but with some warnings
>
> For example, I create this table in Aqua studio as
> create table foo(col1 varchar2);
>
> gives
>
> Warnings: --->
>    W (1):
>           <---
>  0 record(s) affected
>
> The table is created. So what does this warnings mean? where and how do i
> check them?

Are you sure you're using PostgreSQL?  Cause in 8.2.4 I get:

create table foo(col1 varchar2);
ERROR:  type "varchar2" does not exist
LINE 1: create table foo(col1 varchar2);

which is what I expect.  Is this a cut and paste or did you copy this by hand.

Please cut and paste EXACTLY what you're typing and what postgresql is saying.

Also, is this in psql or some other client?

sorry about that. I copied it.This is proper one
create table foo(col1 varchar);

i use aqua studio
Thanks


Scott Marlowe <scott.marlowe@gmail.com> wrote:

On 9/17/07, SHARMILA JOTHIRAJAH wrote:
> Hi
> Sometimes when I create a table with the
> CREATE TABLE sql command,
> it creates a table but with some warnings
>
> For example, I create this table in Aqua studio as
> create table foo(col1 varchar2);
>
> gives
>
> Warnings: --->
> W (1):
> <---
> 0 record(s) affected
>
> The table is created. So what does this warnings mean? where and how do i
> check them?

Are you sure you're using PostgreSQL? Cause in 8.2.4 I get:

create table foo(col1 varchar2);
ERROR: type "varchar2" does not exist
LINE 1: create table foo(col1 varchar2);

which is what I expect. Is this a cut and paste or did you copy this by hand.

Please cut and paste EXACTLY what you're typing and what postgresql is saying.

Also, is this in psql or some other client?

---

Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.

On 9/17/07, SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> wrote:
> sorry about that. I copied it.This is proper one
> create table foo(col1 varchar);
>
> i use aqua studio

Can you copy the error too?  Or is it in some kind of popup that makes
it hard to do?  It just seems odd that you get a warning with nothing
in it.  You might wanna try issuing your create table statement from
psql and see what it says.  Some clients munge the output from
postgresql into something other than useful.  :)