Re: [GENERAL] strange error with temp table: pg_type_typname_nsp_index
Janning Vygen [EMAIL PROTECTED] writes: 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' ; 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. No, not at all: the pg_table_is_visible check will fail on temp tables of other sessions. I think the real problem here is a race condition: pg_table_is_visible will give the cache lookup failed error if the OID is for a table that no longer exists, which means you could have a problem when the select picks up a pg_class row for another session's temp table just before the other session drops the temp table. (The window for this is wider than it might seem, because pg_table_is_visible operates under SnapshotNow rules instead of MVCC.) We've gone back and forth about whether it'd be better for pg_table_is_visible to silently return FALSE if the OID is not a valid table OID, but that doesn't seem real attractive from an error-detection perspective. In any case I don't think this has anything to do with your original report about a duplicate key error. If you can reproduce that one again, let us know. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange error with temp table: pg_type_typname_nsp_index
Am Mittwoch, 13. Juli 2005 16:04 schrieb Tom Lane: Janning Vygen [EMAIL PROTECTED] 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_rankint4 NOT NULL DEFAULT 9, gc_rank int4 NOT NULL DEFAULT 9 ) 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] strange error with temp table: pg_type_typname_nsp_index
Hi,
[i am using Postgresql version 8.0.3]
yesterday i posted a mail regarding a function which calculates a ranking with
a plperl SHARED variable.
Today i ve got some problems with it:
FEHLER: duplizierter Schlüssel verletzt Unique-Constraint
»pg_type_typname_nsp_index«
CONTEXT: SQL-Anweisung »CREATE TEMP TABLE ranking AS SELECT *,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank FROM ( SELECT mg_name,
gc_gesamtpunkte, gc_gesamtsiege FROM temp_gc ORDER BY gc_gesamtpunkte DESC,
gc_gesamtsiege DESC, mg_name ASC ) AS r1«
[it meens: ERROR: duplicate key violates UNIQUE-Constraint]
I am running a stats collector function inside a transaction with isolation
level serializable.
the code which throws an error is the following:
snip
CREATE OR REPLACE function cacheresult(text) RETURNS boolean LANGUAGE 'plperl'
AS $$
[...]
PERFORM reset_ranking();
CREATE TEMP TABLE ranking AS
SELECT
*,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte,
gc_gesamtsiege
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC
) AS r1
;
EXECUTE '
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM ranking
WHERE temp_gc.mg_name = ranking.mg_name;
';
DROP TABLE ranking;
[...]
snip
and the ranking function is as follows:
CREATE OR REPLACE function ranking(int4, float) RETURNS int4 LANGUAGE 'plperl'
AS $$
my %this;
$this{'gesamtpunkte'} = shift;
$this{'sptsiege'} = shift;
$this{'ranking'} = $_SHARED{'prev'}{'ranking'};
$this{'count'}= $_SHARED{'prev'}{'count'} + 1;
$_SHARED{'prev'}{'gesamtpunkte'} = -1 if !defined $_SHARED{'prev'}
{'gesamtpunkte'};
$this{'ranking'} = $this{'count'} unless
$this{'gesamtpunkte'} == $_SHARED{'prev'}{'gesamtpunkte'}
and $this{'sptsiege'} == $_SHARED{'prev'}{'sptsiege'}
;
$_SHARED{'prev'} = \%this;
return $this{'ranking'};
$$;
snip
the function is called many times inside the same transaction.
Tom Lane wrote in a another thread regarding 7.4
[ http://archives.postgresql.org/pgsql-novice/2004-11/msg00246.php ]
It looks like the source of the problem is an
only-partially-deleted temp table left behind by some prior failure.
Specifically, the rowtype entry for the table is still there in
pg_type, though its pg_class entry must be gone or you'd have gotten
a different error message. This seems pretty odd, since the catalog
entries should have been deleted in a single transaction.
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.
It seems to me that setting fsync to false was not a good idea...
Is it a bug? I dont know. What can i do to prevent it? What might be the
reason for this error?
kind regards,
janning
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] strange error with temp table: pg_type_typname_nsp_index
Janning Vygen [EMAIL PROTECTED] 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? It seems to me that setting fsync to false was not a good idea... fsync per se is not relevant, unless maybe you were power-cycling the machine. Still it might be interesting to ask how you were stopping and restarting the postmaster... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
