Re: strange error with temp table: pg_type_typname_nsp_index

Am Mittwoch, 13. Juli 2005 16:04 schrieb Tom Lane:
> Janning Vygen <vygen(at)gmx(dot)de> writes:
> > I was just testing some configuration settings, especially increasing
> > shared_buffers and setting fsync to false. And suddenly it happens 3
> > times out of ten that i get this error.
>
> Could you put together a complete example --- that is a script someone
> else could run to see this error from a standing start?

i tried but the error mentioned above doesn't occur anymore. I dont know why.
but i get another error which looks similar to me because both errors deal
with temporary tables.

982 sfb69
ERROR: cache lookup failed for relation 14138243
CONTEXT: SQL statement "SELECT n.nspname ,c.relname FROM pg_catalog.pg_class
c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE
n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) AND
Upper(relname) = 'TEMP_GC'"
PL/pgSQL function "tsptcache_update" line 16 at perform
SQL statement "SELECT tsptcache_update( $1 , $2 , $3 )"
PL/pgSQL function "cache_update" line 15 at perform

i copied the query from a archive message but maybe it's not as robust as i
thought and all stuff relates to this query.

It should check if a given temp table is already created inside this session.
if not it should be recreated:

PERFORM n.nspname ,c.relname
FROM
pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname like 'pg_temp_%'
AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = 'TEMP_GC'
;

IF NOT FOUND THEN
CREATE TEMP TABLE temp_gc (
mg_name text NOT NULL,
gc_tsptpunkte int4 NOT NULL DEFAULT 0,
gc_tsptsieg int4 NOT NULL DEFAULT 0,
gc_gesamtsiege float NOT NULL DEFAULT 0.0,
gc_bonuspunkte int4 NOT NULL DEFAULT 0,
gc_gesamtpunkte int4 NOT NULL DEFAULT 0,
gc_prev_rank int4 NOT NULL DEFAULT 99999,
gc_rank int4 NOT NULL DEFAULT 99999
) WITHOUT OIDS;
ELSE
TRUNCATE TABLE temp_gc;
END IF;

but as i looked at the system catalogs pg_temp it is like that every session
can see the temporary tables of any other session. so the whole story about
the query above is wrong. It checks if ANY session has a temporrary table
gc_temp and not my own session.

The error occured when i cancelled a query (strg-c) and quickly rerun it. I
guess that the pg_catalog is not tidied up at that time, so the query results
to true because the temp table is still inside another session.

i guess my whole temporary table function ist buggy or i have to use EXECUTE
all the time.

hmm. i have to learn a lot more, i guess.

kind regards,
janning