Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help needed : temp tables.

Help needed : temp tables.

From
"Vilson farias"
Date:
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.

How can I delete a 'zombie' temp table? I tried to drop these tables, but
the following message appeared, per example :


relatorio=# \dS
           List of relations
       Name        |  Type   |  Owner
-------------------+---------+----------
...
 pg_rules          | view    | postgres
 pg_shadow         | table   | postgres
 pg_statistic      | table   | postgres
 pg_tables         | view    | postgres
 pg_temp.10050.18  | table   | postgres
 pg_temp.10050.19  | table   | postgres
 pg_temp.10050.20  | table   | postgres
 pg_temp.10863.0   | table   | postgres
 pg_temp.10863.1   | table   | postgres
 pg_temp.10863.2   | table   | postgres
 pg_temp.10863.3   | table   | postgres
 pg_temp.10863.4   | table   | postgres
 pg_temp.10863.5   | table   | postgres
 pg_temp.10863.6   | table   | postgres
...lots and lots more

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


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


Does someone know how to solve this problem?

Thanks!

José Vilson de Mello de Farias
Dígitro Tecnologia ltda - Brazil

Re: Help needed : temp tables.

From
Tom Lane
Date:
"Vilson farias" <vilson.farias@digitro.com.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