Re: 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 <erik(at)myemma(dot)com> wrote:
>
>
> On Sep 17, 2007, at 9:02 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:
> > > 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)
> 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
>
>
>