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;
ABORTThe following is interesting:
db=> select * from xxx;
ERROR: xxx: Table does not exist.
db=> create table xxx (xx smallint);
ERROR: xxx relation already existsHmmm. 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
----------------------------------------------------------------------