Re: Delete temp tables

"Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)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