Re: ERROR: type "temp_gc" already exists

Hi Tom, Hi Damon,

Am Dienstag, 27. September 2005 20:36 schrieb Tom Lane:
> Damon Hart <dhcom(at)sundial(dot)com> writes:
> > I came across this thread after intermittently observing very similar
> > error messages last week from a PostgreSQL (8.0.2) server, e.g.:
> > SQL error: = 'type "local_roll" already exists' in line 1984
> > ...
> > Sorry I haven't been able to diagnose this any more precisely, but I
> > think a hardware explanation is very doubtful.
>
> I agree, it smells more like a software problem. Can you generate a
> test case, ie, a self-contained program someone could run that'd
> reproduce the error (possibly after running a long time)?

I recently reported this problem and i would like to help solving it. But how
can i build a self-contained test-case? It just happens sometimes under load.
do you just need DB Schema and my frontend script which causes the error?

Or do you need a complete dump of the database? Does it make sense to generate
a dump of the database when the error occured?

Hardware can't be the reason in my opionion because it happens on two servers
at the same time under the same load. The servers are not related to each
other in anyway but both run exactly the same application. They run the same
hardware but it woul dsurprise me if two hardware failures happen at the same
time and produce the same software failure.

I got the error in two cases:

case 1:
a daemon calls a plpgsql function in Transaction mode "SERIALIZATION". This
function generates a TEMP TABLE for calculating some stuff like this:

EXECUTE $$
CREATE TEMP TABLE temp_gc AS
SELECT
[...]

And at the end of the function the temp table is dropped with
EXECUTE $$DROP TABLE temp_gc;$$;

This function is only called by a daemon which calculates some materialized
view. This function is never called by more than one session at the same
time.

It fails under load (load comes from test case 2) with
"TYPE temp_gc already exists"

case 2:
Many parallel sessions do the following:

BEGIN;
CREATE TEMP TABLE spiele (
sp_id int4,
sp_heimtore int4,
sp_gasttore int4,
sp_abpfiff boolean,
wb_name text,
sn_name text,
sp_termin timestamp
) ON COMMIT DROP;
INSERT INTO spiele ...
SELECT ...
COMMIT;

there is also a table "Spiele" in schema public. The session selects a result
which depends on the user given game results (table "spiele" saves game
results).

Under load it happens that the transaction fails with
TYPE spiele already exists.

In both cases i got 10-15 of type temp_gc and spiele in pg_type.

Then i run something like
for I in 1..20 do
DROP TYPE pg_temp_$I.spiele;
DROP TYPE pg_temp_$I.temp_gc;
done;

After this everything works fine again.

kind regards,
Janning Vygen