strange error with temp table: pg_type_typname_nsp_index

[Janning Vygen <vygen@xxxxxx>]

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