strange error with temp table: pg_type_typname_nsp_index - Mailing list pgsql-general

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