Thread: ERROR: Unable to locate type oid 718 in catalog

ERROR: Unable to locate type oid 718 in catalog

From
"G. Anthony Reina"
Date:
Anyone run into this error before:

ERROR:  Unable to locate type oid 718 in catalog

This occurred when I tried to 'vacuum verbose analyze' my database. The
last time it occurred, I had to re-build the database to get rid of the
error message. Perhaps the pg_catalog is getting corrupted somehow? I'm
not quite sure what it means. The vacuum doesn't finish but rather craps
out after the error. There is no core being generated.

I'm using Postgres 6.5.1 on RH Linux 6.0 (i686).

Thanks.
-Tony



Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
Tom Lane
Date:
"G. Anthony Reina" <reina@nsi.edu> writes:
> Anyone run into this error before:
> ERROR:  Unable to locate type oid 718 in catalog
> This occurred when I tried to 'vacuum verbose analyze' my database.

A quick glimpse says that the only occurrences of that error text are
in parse_type.c, which is not code that I'd think would get called from
vacuum.  Odd.

If you do "select * from pg_type where oid = 718;" you should get

typname|typowner|typlen|typprtlen|typbyval|typtype|typisdefined|typdelim|typrelid|typelem|typinput |typoutput
|typreceive|typsend  |typalign|typdefault
 

-------+--------+------+---------+--------+-------+------------+--------+--------+-------+---------+----------+----------+----------+--------+----------
circle |     256|    24|       47|f       |b      |t           |,       |       0|
0|circle_in|circle_out|circle_in|circle_out|d       |
 
(1 row)

If you don't then indeed pg_type is corrupted.
        regards, tom lane


Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
"G. Anthony Reina"
Date:
Tom Lane wrote:

> A quick glimpse says that the only occurrences of that error text are
> in parse_type.c, which is not code that I'd think would get called from
> vacuum.  Odd.
>
> If you do "select * from pg_type where oid = 718;" you should get
>
> typname|typowner|typlen|typprtlen|typbyval|typtype|typisdefined|typdelim|typrelid|typelem|typinput |typoutput
|typreceive|typsend  |typalign|typdefault
 
>
-------+--------+------+---------+--------+-------+------------+--------+--------+-------+---------+----------+----------+----------+--------+----------
> circle |     256|    24|       47|f       |b      |t           |,       |       0|
0|circle_in|circle_out|circle_in|circle_out|d       |
 
> (1 row)
>
> If you don't then indeed pg_type is corrupted.
>
>                         regards, tom lane

Okay, I found out why I am getting this error. My partner is building a table which he is calling 'circle'. Of course,
circleis a pg_type in the
 
PostgreSQL. So he DROP TYPE'd circle from the database (we don't need that type anyway). For some reason, the database
seemsto not mind this until I do
 
the vacuum analyze.

Any suggestions on a workaround? We'd really prefer to use 'circle' as a tablename and don't need it as a pg_type.
-Tony




Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
Tom Lane
Date:
"G. Anthony Reina" <reina@nsi.edu> writes:
> Okay, I found out why I am getting this error. My partner is building
> a table which he is calling 'circle'. Of course, circle is a pg_type
> in the PostgreSQL. So he DROP TYPE'd circle from the database (we
> don't need that type anyway). For some reason, the database seems to
> not mind this until I do the vacuum analyze.

Is it possible that you've got tables lying around that have ordinary-
circle-type fields in them?  Vacuum analyze would notice the lack of
type data, but I'm not sure a plain vacuum would.

In any case, it'd be wise to flush everything in pg_operator and pg_proc
that has circle as an argument or result type.  (Does DROP TYPE do that
for you?  I bet not...)  There might be other system tables that have
references to circle, too.
        regards, tom lane


Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
"G. Anthony Reina"
Date:
Tom Lane wrote:

> Is it possible that you've got tables lying around that have ordinary-
> circle-type fields in them?

No. No tables at all use the type circle.

> In any case, it'd be wise to flush everything in pg_operator and pg_proc
> that has circle as an argument or result type.  (Does DROP TYPE do that
> for you?  I bet not...)  There might be other system tables that have
> references to circle, too.

>
>

I'm not sure what you mean by flush pg_operator and pg_proc. What would the
command be?

Thanks.
-Tony




Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
Tom Lane
Date:
"G. Anthony Reina" <reina@nsi.edu> writes:
> Tom Lane wrote:
>> In any case, it'd be wise to flush everything in pg_operator and pg_proc
>> that has circle as an argument or result type.  (Does DROP TYPE do that
>> for you?  I bet not...)  There might be other system tables that have
>> references to circle, too.

> I'm not sure what you mean by flush pg_operator and pg_proc. What would the
> command be?

I meant drop all the operators and functions that use circle data.

You could run the oidjoins regression test script to find out which ones
those are ... it should complain about all system table entries that
refer to OID 718.  (If you are mistaken that you have no tables using
circles, you'd find that out, too.)
        regards, tom lane


Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
"G. Anthony Reina"
Date:
Tom Lane wrote:

> I meant drop all the operators and functions that use circle data.
>
> You could run the oidjoins regression test script to find out which ones
> those are ... it should complain about all system table entries that
> refer to OID 718.  (If you are mistaken that you have no tables using
> circles, you'd find that out, too.)
>
>                         regards, tom lane

So I think you are saying that although none of my tables have the circle type,
there are inherent Postgres functions and
operators which use circle. By running the regression test, I could find out
which functions and operators these are and just drop them. Is the vacuum
crapping out then because it is trying to vacuum one of these functions and
finding that OID 718 doesn't exist?

Thanks.
-Tony




Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
Thomas Lockhart
Date:
> > I meant drop all the operators and functions that use circle data.
> So I think you are saying that although none of my tables have the circle type,
> there are inherent Postgres functions and
> operators which use circle. By running the regression test, I could find out
> which functions and operators these are and just drop them. Is the vacuum
> crapping out then because it is trying to vacuum one of these functions and
> finding that OID 718 doesn't exist?

Aren't the built-in types cached at compile time? Even if not, I'd
*really* suggest using a different name for your table. Even "Circle"
(including the double-quotes and mixed case) would work, and would
keep you from having to drop built-in types.
                      - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California


Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
Tom Lane
Date:
"G. Anthony Reina" <reina@nsi.edu> writes:
> So I think you are saying that although none of my tables have the
> circle type, there are inherent Postgres functions and operators which
> use circle. By running the regression test, I could find out which
> functions and operators these are and just drop them.

Right.

> Is the vacuum
> crapping out then because it is trying to vacuum one of these
> functions and finding that OID 718 doesn't exist?

Vacuum doesn't vacuum functions (AFAIK).  It does, however, use the type
information about columns of tables that it's vacuuming --- at least it
does in vacuum analyze mode, not sure about plain vacuum.  That's why
I'm suspicious that you have somewhere a forgotten table that has a
column of circle type...
        regards, tom lane


Re: [HACKERS] ERROR: Unable to locate type oid 718 in catalog

From
"G. Anthony Reina"
Date:
Tom Lane wrote:

> Vacuum doesn't vacuum functions (AFAIK).  It does, however, use the type
> information about columns of tables that it's vacuuming --- at least it
> does in vacuum analyze mode, not sure about plain vacuum.  That's why
> I'm suspicious that you have somewhere a forgotten table that has a
> column of circle type...

Nope. I'm absolutely, positively, 100% sure that no table uses the type
'circle'. However, we're going to name the table 'circles' instead and
re-build the database by dumping, destroying, re-creating, and dumping back
in. IT just makes the most sense.

Thanks for the help.
-Tony