Thread: dropping a table, leaving actual file in base/db/...

we have a process which uses a transaction to a bunch of:

begin work;
select * into tmptable from main table;
commit work;
drop table tmptable;


sometimes (always?) tmptable gets dropped from the database, but for
some reason the file /usr/pgsql/data/base/dbname/tmptable does not get
deleted.

this means that the next select into tmptable and/or create table tmptable
fails because it can't create the table.

anyone know what's up, or a work around?

--
[ Jim Mercer    Reptilian Research      jim@reptiles.org   +1 416 410-5633 ]
[ The telephone, for those of you who  have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail.            ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]

jim@reptiles.org (Jim Mercer) wrote:

 > begin work;
 > select * into tmptable from main table;
 > commit work;
 > drop table tmptable;
 >
 > sometimes (always?) tmptable gets dropped from the database, but for
 > some reason the file /usr/pgsql/data/base/dbname/tmptable does not get
 > deleted.

I think I posted something similar some time ago, didn't get a reply:

Ulf Mehlig <umehlig@uni-bremen.de>:

 > Consider this (psql of 6.4, on i386-Linux 2.02b7):
 >
 >    db=> begin work;
 >    BEGIN
 >    db=> create table xxx (xx smallint);
 >    CREATE
 >    db=> insert into xxx values (1);
 >    INSERT 136937 1
 >    db=> rollback;
 >    ABORT
 >
 > The following is interesting:
 >
 >    db=> select * from xxx;
 >    ERROR:  xxx: Table does not exist.
 >    db=> create table xxx (xx smallint);
 >    ERROR:  xxx relation already exists
 >
 > Hmmm. And then:
 >
 >    db=> drop table xxx;
 >    ERROR:  Relation xxx Does Not Exist!
 >    db=> \dt
 >    NOTICE:  (transaction aborted): queries ignored until END
 >    Couldn't find any tables!
 >
 > After quitting and re-entering psql it is possible again to create a
 > table "xxx". Seems to be a little bug, or did I overlook something?

The very last line (giving \dt command) I can't reproduce at the
moment.

Ciao, Ulf

--
======================================================================
Ulf Mehlig    <umehlig@zmt.uni-bremen.de>
              Center for Tropical Marine Ecology/ZMT, Bremen, Germany
----------------------------------------------------------------------