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.