Re: ERROR: type "temp_gc" already exists - Mailing list pgsql-general

I agree with you that it's probably not hardware problem.
I had the exactly the same issues some months ago with the 8.0 version when
using temp tables in functions.

I changed the temp tables to standard tables and used the PID
(pg_backend_pid) to manage concurrent access to this table... The code is
somehow more heavy to write but really the application is now robust where
it was not...

Regards,
Patrick

----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Janning Vygen
Sent: mercredi 28 septembre 2005 11:51
To: pgsql-general@postgresql.org
Cc: Tom Lane; Damon Hart
Subject: Re: [GENERAL] ERROR: type "temp_gc" already exists


Hi Tom, Hi Damon,

Am Dienstag, 27. September 2005 20:36 schrieb Tom Lane:
> Damon Hart <dhcom@sundial.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.8/113 - Release Date: 27/09/2005

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.8/113 - Release Date: 27/09/2005