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/interactive/sql-createtable.html). temp tables live in a separate namespace (schema): >From http://developer.postgresql.org/~momjian/upgrade_tips_7.3: " 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 : [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> with the body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject: field of your email blank.
