Thread: when do pg_temp SCHEMAS get purged?

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.

"Harald Armin Massa" <haraldarminmassa@gmail.com> writes:
> 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?

We don't bother.  One row in pg_namespace is not worth removing,
especially when it's likely to be needed again someday.

(Now, if any of the *contents* of the temp namespaces don't go away at
backend exit, that's another story...)

            regards, tom lane

Tom,

thanks for the information! Now I understand: every backend gets it's own pg_tempxx, that is why in production those number rose fairly quickly; and the pg_tempxx schemas gets recycled after the backend exits.

Now if only I would sell my software after maximum simultanuos concurrend users, I would have a very good, free measurement :)

Thank you very much,

Harald

--
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.