From: | "Harald Armin Massa" <haraldarminmassa(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | when do pg_temp SCHEMAS get purged? |
Date: | 2006-08-02 11:59:50 |
Message-ID: | 7be3f35d0608020459j5fa03bdfk17f85f0a1f9035f6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Harald Fuchs | 2006-08-02 12:00:09 | Re: LISTEN considered dangerous |
Previous Message | Richard Huxton | 2006-08-02 11:44:11 | Re: Performance/Issues with CMP and JBoss |