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

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?

> 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

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

Janning Vygen <vygen(at)planwerk6(dot)de> 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