ERROR: type "temp_gc" already exists

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]It's not clear to me if pg_depend is a "shared system catalog" because the docs say

- 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
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