Thread: ERROR: Unable to locate type oid 718 in catalog
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
"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
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
"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
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
"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
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
> > 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
"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
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