Re: Help needed : temp tables.

"Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)br> writes:
> I'm having problems with temporary tables in postgre.
> I've been using Postgre 7.02 in a red hat 6.2 and I'm accessing using Delphi
> 5 + Winzeos Postgre Components. I've been using for some mounths and now I
> checked that there are lots of temp tables in my system. These tables where
> created by my application and I think everytime it crashed the temp tables
> where not removed.

Yeah, you're right :-(. 7.0 through 7.0.2 fail to do proper abort
processing if the client application disconnects partway through a
transaction. One of the effects is that temp tables aren't deleted.

> relatorio=# drop table pg_temp.10863.1;
> ERROR: parser: parse error at or near ".10863"

You'd need to quote the name, eg
drop table "pg_temp.10863.1";
since dot isn't considered a regular identifier character.

> And if I try vacuum verbose analyze, I get this message:

> NOTICE: --Relation pg_temp.10863.0--
> NOTICE: mdopen: couldn't open pg_temp.10863.0: No such file or directory
> ERROR: cannot open relation pg_temp.10863.0

Odd. Did you try to manually remove the pg_temp files at some point?

I think that 7.0.2 will let you do the drop anyway, but if not you
might have to create dummy files before dropping, eg
touch $PGDATA/base/relatorio/pg_temp.10863.1
and then drop the table.

7.0.3 will contain a fix for the failure-to-cleanup bug.

regards, tom lane