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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
