Thread: ERROR: type "temp_gc" already exists
Hi, i run postgresql 8.0.3 and i have a script which calls a postgresql function to calculate a materialized View. this function creates a temp table. It does so with EXECUTE statments to avoid the caching of plans with temporary tables. It runs on three servers and evrything went fine for a couple of months. the temp table is dropped at the end of the function. everything runs inside a (serialized) transaction. suddenly the script fails on one server with ERROR: type "temp_gc" already exists if i connect and try to create a temp table it says create temp table temp_gc (id text); ERROR: type "temp_gc" already exists in my pg_type i have select * from pg_type where typname = 'temp_gc'; typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive |typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault ---------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+-----------+------------+-------------+-------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------ temp_gc | 16847 | 100 | -1 | f | c | t | , | 16562879 | 0 | record_in | record_out | record_recv | record_send | - | d | x | f | 0 | -1 | 0 | | (1 row) i guess the table was dropped but not the corresponding type. How can things like this happen? How can i fix it? Can i just drop the type from pg_type? kind regards, janning
Janning Vygen <vygen@gmx.de> writes:
> i guess the table was dropped but not the corresponding type.
> How can things like this happen?
Corrupted pg_depend table maybe? You might try REINDEXing pg_depend
to be on the safe side. Also please look to see if there are any
relevant entries in it (look for objid = the type's OID, or refobjid
= 16562879 which we can see was the table's OID).
> How can i fix it? Can i just drop the type from pg_type?
If there's no pg_depend entry then DROP TYPE should work. Otherwise
you might have to resort to manually DELETEing the pg_type row.
regards, tom lane
Am Samstag, 10. September 2005 18:05 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > i guess the table was dropped but not the corresponding type.
> > How can things like this happen?
>
> Corrupted pg_depend table maybe? You might try REINDEXing pg_depend
> to be on the safe side. Also please look to see if there are any
> relevant entries in it (look for objid = the type's OID, or refobjid
> = 16562879 which we can see was the table's OID).
>
> > How can i fix it? Can i just drop the type from pg_type?
>
> If there's no pg_depend entry then DROP TYPE should work. Otherwise
> you might have to resort to manually DELETEing the pg_type row.
Thanks for your detailed answer. I don't want to do anything wrong. To be
sure, i have some more questions:
- There is no entry in pg_depend. Should i just drop the entry from pg_type or
should i REINDEX anyway?
- Can i REINDEX pg_depend in normal operation mode or do i have to take
precautions mentioned in the docs? [1]
- How can things like this happen? Hardware failure? If yes, should i change
my harddisk?
kind regards,
Janning
[1] It's not clear to me if pg_depend is a "shared system catalog" because the
docs say
"any of the shared system catalogs (pg_database,
pg_group, pg_shadow, or pg_tablespace)"
Maybe the iteration is final, maybe it shows only examples)
Janning Vygen <vygen@gmx.de> writes:
> Am Samstag, 10. September 2005 18:05 schrieb Tom Lane:
>> If there's no pg_depend entry then DROP TYPE should work. Otherwise
>> you might have to resort to manually DELETEing the pg_type row.
> Thanks for your detailed answer. I don't want to do anything wrong. To be
> sure, i have some more questions:
> - There is no entry in pg_depend. Should i just drop the entry from pg_type or
> should i REINDEX anyway?
Well, what did you do to check that there was no entry? If the index is
corrupt and you issued a query that used the index, it might have failed
to find an entry that's actually there in the table (in fact, if we're
assuming the DROP TYPE didn't happen because the system didn't find the
dependency row while dropping the table, this is pretty much exactly
what you'd expect). I'd REINDEX and then check again.
> - How can things like this happen? Hardware failure? If yes, should i change
> my harddisk?
Insufficient information to say. It wouldn't be a bad idea to run some
disk tests though.
> [1] It's not clear to me if pg_depend is a "shared system catalog" because the
> docs say
> "any of the shared system catalogs (pg_database,
> pg_group, pg_shadow, or pg_tablespace)"
> Maybe the iteration is final, maybe it shows only examples)
That's meant to be a complete list --- I've updated the documentation to
make this clearer. But you could check for yourself:
select relname from pg_class where relisshared;
regards, tom lane
> Janning Vygen <vygen ( at ) gmx ( dot ) de> writes: > > Am Samstag, 10. September 2005 18:05 schrieb Tom Lane: > >> If there's no pg_depend entry then DROP TYPE should work. Otherwise > >> you might have to resort to manually DELETEing the pg_type row. > > > Thanks for your detailed answer. I don't want to do anything wrong. To be > > sure, i have some more questions: > > > - There is no entry in pg_depend. Should i just drop the entry from pg_type or > > should i REINDEX anyway? > > Well, what did you do to check that there was no entry? If the index is > corrupt and you issued a query that used the index, it might have failed > to find an entry that's actually there in the table (in fact, if we're > assuming the DROP TYPE didn't happen because the system didn't find the > dependency row while dropping the table, this is pretty much exactly > what you'd expect). I'd REINDEX and then check again. What i did so far: $ REINDEX TABLE pg_depend $ SELECT * from pg_depend where objid = 16562879; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype ---------+-------+----------+------------+----------+-------------+--------- (0 rows) $ SELECT * from pg_type where typname = 'temp_gc'; typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined | typdelim | typrelid | typelem | typinput | typoutput | typreceive | typsend | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault ---------+--------------+----------+--------+----------+---------+--------------+----------+----------+---------+-----------+------------+-------------+-------------+------------+----------+------------+------------+-------------+-----------+----------+---------------+------------ temp_gc | 16847 | 100 | -1 | f | c | t | , | 16562879 | 0 | record_in | record_out | record_recv | record_send | - | d | x | f | 0 | -1 | 0 | | (1 row) $ DROP TYPE temp_gc; ERROR: type "temp_gc" does not exist > If there's no pg_depend entry then DROP TYPE should work. Otherwise > you might have to resort to manually DELETEing the pg_type row. There is no pg_depend entry as far as i can tell, but DROP TYPE doesn't work. Can i just DELETE the pg_type row now?? Sorry for asking again and again, but manipulating system catalogs seems to me very dangerous. It's live database and i dont want to do anything wrong. kind regards Janning
Janning Vygen <vygen@gmx.de> writes:
> $ DROP TYPE temp_gc;
> ERROR: type "temp_gc" does not exist
The temp schema is evidently not in your search path. You need
something like
drop type pg_temp_NNN.temp_gc;
regards, tom lane
Am Donnerstag, 15. September 2005 15:31 schrieb Tom Lane: > Janning Vygen <vygen@gmx.de> writes: > > $ DROP TYPE temp_gc; > > ERROR: type "temp_gc" does not exist > > The temp schema is evidently not in your search path. You need > something like > drop type pg_temp_NNN.temp_gc; great support! great software! thanks a lot again! I managed it and now everything runs fine. kind regards janning