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
