On Sat, Apr 15, 2006 at 06:39:22PM -0400, Dave Siktberg
wrote:
> I use temporary tables in Postgres 7.1 (via
"select foo into temporary table
> bar"). These are needed for a short time only.
I expected they would
> periodically be deleted. In looking at my vacuum
results, it appears they
> are persisting forever. I can't locate any
instructions on how to remove
> them. Is there anything short of individual drop table
commands or a dump /
> reload that will remove these from the database?
The following quotes are partly from postgres 8.x doc, but
it works also
with 7.4.5 (don't know about 7.1):
Temp Tables are deleted at the end of the database session
automatically.
Maybe you have never closed these sessions.
You also can control, what happens to temp tables at the end
of a transaction
(see http://www.postgresql.org/docs/8.1/interactiv
e/sql-createtable.html).
temp tables live in a separate namespace (schema):
From bsn.com
Basis Systeme netzwerk (BSn) phone :
+49 89 692 8120
Brecherspitzstr. 8 Rgb. fax :
+49 89 692 8150
81541 Muenchen, Germany mobile:
0179 2133436
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to
<listserv listserv.aol.com> with the
body of "SIGNOFF AOLSERVER" in the email
message. You can leave the Subject: field of your email
blank.
|
![bsn.com Basis Systeme netzwerk (BSn) phone : +49 89 692 8120 Brecherspitzstr. 8 Rgb. fax : +49 89 692 8150 81541 Muenchen, Germany mobile: 0179 2133436 -- AOLserver -](http://developer.postgresql.org/~momjian/upgrade_tips_
7.3<img
src='http://www.archivesat.com/images/blushing.gif'>http://d
eveloper.postgresql.org/~momjian/upgrade_tips_7.3:</a>
"
A related point is that temporary tables no longer have
names (in the
catalogs) of the form "pg_temp_NNN"; rather they
have exactly the name
that the creating user gave them. They are kept separate
from other
tables by placing them in schemas named like
"pg_temp_NNN" (where now
NNN identifies an active backend, not a single table). So
if you wanted
your scan to exclude temp tables then you'd definitely
better change to
excluding on the basis of schema name not table name. On
the upside,
if you do want your scan to show temp tables then it's much
easier than
before. (BTW, the pg_table_is_visible function is the best
way of
distinguishing your own session's temp tables from other
people's. Yours
will be visible, other people's won't.)
".
While naming your tables as <schema>.<tablename>
you can treat them
like normal tables (in a psql Shell):
rails_test=# create temp table a (i integer);
rails_test=# \dt
List of relations
Schema | Name | Type | Owner
-----------+------+-------+-------
pg_temp_1 | a | table | np
rails_test=# \d pg_temp_1.*
Table "pg_temp_1.a"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
insert into pg_temp_1.a values ( 12345 );
INSERT ....
drop table a;
# or ...
drop table pg_temp_1.a;
# System Table pg_namespace will print existing namespaces:
rails_test=# select * from pg_namespace;
nspname | nspowner | nspacl
--------------------+----------+----------------------------
pg_temp_2 | 1 |
pg_toast | 1 |
pg_temp_1 | 1 |
pg_catalog | 1 | {pgsql=UC/pgsql,=U/pgsql}
public | 1 | {pgsql=UC/pgsql,=UC/pgsql}
information_schema | 1 | {pgsql=UC/pgsql,=U/pgsql}
# Something like this will list all temp tables in a
database:
# even if they are not related to the current session:
select c.relname, c.relnamespace, n.nspname from pg_class c
join pg_namespace n on c.relnamespace=n.oid
where n.nspname ~'pg_temp' order by n.nspname;
relname | relnamespace | nspname
---------+--------------+-----------
a | 17261 | pg_temp_2
# Now, while you now the name, you can drop it:
drop table pg_temp_2.a;
check \? in psql shell , try the \d... commands
and watch the postgres server log file while entering these
commands.
You see, how psql transform the \d information commands
into
queries over the system tables.
So you can learn easyly something about the postgres system
catalog.
--
Norbert Poellmann email :
np<img src=){kind=link}