when do pg_temp SCHEMAS get purged? - Mailing list pgsql-general

I create 2 temp tables on each connection to store session relevant variables.

It works beautifully with code I robbed accross postgresql mailinglists:

CREATE OR REPLACE FUNCTION set_quarant(mylvlquarant int4)
  RETURNS int4 AS
$BODY$
    BEGIN
    perform relname from pg_class
            where relname = 'quara_tmp'
              and case when has_schema_privilege(relnamespace, 'USAGE')
                    then pg_table_is_visible(oid) else false end;
        if not found then
            create temporary table quara_tmp (
                lvlquara integer
            );
    else
       delete from quara_tmp;
    end if;

    insert into quara_tmp values (mylvlquarant);
  return 0;
  END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION get_quarant()
  RETURNS int4 AS
$BODY$
declare
ergebnis int4;
    BEGIN
    perform relname from pg_class
            where relname = 'quara_tmp'
              and case when has_schema_privilege(relnamespace, 'USAGE')
                    then pg_table_is_visible(oid) else false end;
  if not found then
    return 0;
  else
    select lvlquara from quara_tmp into ergebnis;
  end if;
 
  if not found then
    ergebnis:=0;
  end if;
   
  RETURN ergebnis;
  END;
 $BODY$
  LANGUAGE 'plpgsql' STABLE;

Now I looked at system objects and detected, that schemas named PG_TEMPn, where n is a slowly growing integer,
are created.

Those schemas seem to get more and more and more. Is anyprocess taking care of purging the ones no longer needed?

   "PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)"

(yes, autovacuum is set up, yes, I have vacuumed the database manually in addition)


Harald

Post


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all.