Re: postgres catalog files problem
| От | Josh Harrison |
|---|---|
| Тема | Re: postgres catalog files problem |
| Дата | |
| Msg-id | 8d89ea1d0709170850i40f4fe51j7df3b673c13ed2b2@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: postgres catalog files problem (Erik Jones <erik@myemma.com>) |
| Ответы |
Re: postgres catalog files problem
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-general |
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@myemma.com> wrote:
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
В списке pgsql-general по дате отправления: