Thread: Delete temp tables

Hi people!

I'm writing a script to erase non-droped temp tables. When I tried to delete
from pg_tables, the following problem raises:


**check out, there are data:
relatorio=# SELECT COUNT(*) FROM pg_tables WHERE tablename like 'pg_temp.%';
 count
-------
   101
(1 row)


**There are data, really:
relatorio=# SELECT * FROM pg_tables WHERE tablename like 'pg_temp.%' LIMIT
5;
    tablename    | tableowner | hasindexes | hasrules | hastriggers
-----------------+------------+------------+----------+-------------
 pg_temp.10752.1 | postgres   | f          | f        | f
 pg_temp.1085.10 | postgres   | f          | f        | f
 pg_temp.1085.11 | postgres   | f          | f        | f
 pg_temp.1085.12 | postgres   | f          | f        | f
 pg_temp.1085.13 | postgres   | f          | f        | f
(5 rows)

**But when I execute a delete, nothing happens:
relatorio=# DELETE FROM pg_tables WHERE tablename like 'pg_temp.%';
DELETE 0
relatorio=#

**or
relatorio=# delete from pg_tables where tablename ='pg_temp.1823.12';
DELETE 0

** I tried everything, even with double quotes (of course doesn't work).
relatorio=# delete from pg_tables where tablename ="pg_temp.1823.12";
ERROR:  Attribute 'pg_temp.1823.12' not found


Why DELETE 0, if the select, with same structure, shows data?

I remember that table associated files where auto-removed. I went to
$PGDATA/base/relatorio and there were no temp_table files. I created them by
my self.

Now, how can I remove these temp tables?

Regards from Brazil,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda.

"Vilson farias" <vilson.farias@digitro.com.br> writes:
> I'm writing a script to erase non-droped temp tables.

Er, why don't you just disconnect?

> **But when I execute a delete, nothing happens:
> relatorio=# DELETE FROM pg_tables WHERE tablename like 'pg_temp.%';
> DELETE 0

Fortunately for you, pg_tables is only a view, and it has no ON DELETE
rule, so nothing happens.  (In 7.1 you'll get an error along the lines
of "Cannot delete from a view without an appropriate rule", but in
current releases nothing happens.)

Had you tried this against the real relation, pg_class, you would've
managed to delete the pg_class tuples for the temp relations --- but
the physical file storage would still have been there, not to mention
all the auxiliary tuples in tables like pg_attribute, pg_index, etc.
End result: when you did log out, all hell would break loose, and you'd
probably end up with a broken database.  (And yet the physical Unix
files occupied by the temp tables would still be there...)

PostgreSQL does not attempt to defend itself against depredations
committed by the superuser on the system catalogs.  If you have the
rights to modify those tables, you're expected to know what you're
doing, or else exercise restraint.

In short: if you want to delete a table there is one and only one
safe method to do it: DROP TABLE.  The key difference between a temp
table and a regular table is that the DROP will be done for you
automatically when you disconnect.

            regards, tom lane

: In short: if you want to delete a table there is one and only one
: safe method to do it: DROP TABLE.  The key difference between a temp
: table and a regular table is that the DROP will be done for you
: automatically when you disconnect.

Now why?
relatorio=#  DROP TABLE "pg_temp.1823.17";
ERROR:  class "pg_temp.1823.17" is a system catalog

I really need to erase these tables, because they are not been auto-removed
after my application crashes. I have more than 100 zombie temp tables in my
system. What are the system tables that I need to remove temp tables
information?

Regards,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda.

:
: regards, tom lane
:

"Vilson farias" <vilson.farias@digitro.com.br> writes:
> Now why?
> relatorio=#  DROP TABLE "pg_temp.1823.17";
> ERROR:  class "pg_temp.1823.17" is a system catalog

Tables named pg_something are normally system tables, and so there's a
check to prevent you from deleting them.  Perhaps it's being overly
enthusiastic by preventing you from deleting temp tables (comments
anyone?), but in the meantime, you can override the check by starting
the postmaster with -o "-O".

> I really need to erase these tables, because they are not been auto-removed
> after my application crashes. I have more than 100 zombie temp tables in my
> system.

That implies that you're suffering backend crashes, not just application
failures, and should be looked into.

            regards, tom lane

[ Charset ISO-8859-1 unsupported, converting... ]
> : In short: if you want to delete a table there is one and only one
> : safe method to do it: DROP TABLE.  The key difference between a temp
> : table and a regular table is that the DROP will be done for you
> : automatically when you disconnect.
>
> Now why?
> relatorio=#  DROP TABLE "pg_temp.1823.17";
> ERROR:  class "pg_temp.1823.17" is a system catalog
>
> I really need to erase these tables, because they are not been auto-removed
> after my application crashes. I have more than 100 zombie temp tables in my
> system. What are the system tables that I need to remove temp tables
> information?

That is strange that they were not auto-deleted.  If they don't exist in
pg_class anymore, just 'rm' the physical files.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026