The following bug has been logged online:

Bug reference: 1236
Logged by: Fabien

Email address: coelho (AT) cri (DOT) ensmp.fr

PostgreSQL version: 8.0 Beta

Operating system: Linux debian

Description: still in use tablespaces can be removed

Details:

Sorry if this bug was already reported.
I could not search the list as http://archives.postgresql.org/pgsql-bugs/
looks blank right now...

One can remove a tablespace although it is being
used, putting the database in a slightly incoherent
state. It was so in yesterday (27/08/2004) cvs head:

sh> mkdir /tmp/postgres
pg> CREATE TABLESPACE tsp LOCATION '/tmp/postgres';
-- ok
pg> CREATE SCHEMA s TABLESPACE tsp;
-- ok
pg> DROP TABLESPACE tsp;
-- ok...
pg> CREATE TABLE s.t(id SERIAL PRIMARY KEY);
-- ERROR... cannot create directory

The bug is simply that DROP TABLESPACE looks whether
the directory is empty, but it happens that the namespace's tablespace uses
do not create anything in the directory...

I now that I can alter the tablespace entry manually
in pg_namespace to correct this, but it looks like a bug to me anyway: the
database should not be so easy
to put in a in coherent state.

Suggested fix: create some empty file in the directory
if it is used by a namespace. don't forget to move
the file around when altering the namespace (well, once
it will be implemented).

It does not seems practical to check for namespace's uses of a tablespace as
one belong to a cluster and the other to the database.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
This was discussed and rejected before --- the problem is not worth the
amount of mechanism that would have to be added to fix it this way.
See the pghackers archives, but I believe the main problem is that a
namespace is not a table and so none of the mechanisms that support file
creation/deletion will work with it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dear Tom,

I just made a small suggestion on how to fix it, and I agree that it
may not be appropriate. I just seemed a simple solution wrt to how
pg decides how the tablespace may be removed.

As for reading the pghackers history on tablespace, I guess that beta is
for testing the software and reporting issues. So I spend some time
testing new features, and if there is a problem I report it. If you thing
the problem is not worth solving, well, I've made my part by testing and
reporting it!

From what I've seen aboutt tablespaces, it seems to me that the
implementation is just NOT finished, whether beta freeze or not. This
means that anyone that who will use this feature in 8.0 will run into
these issues.

Have a nice day,

--
Fabien Coelho - coelho (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Added to open items list:

* Fix error message when creating objects in schema that has a
dropped tablespace as its default

I can confirm the bug still exists in CVS.

---------------------------------------------------------------------------

Fabien COELHO wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

I can confirm this is fixed in current CVS:

test=> CREATE TABLESPACE tsp LOCATION '/bjm/tmp';
CREATE TABLESPACE
test=> CREATE SCHEMA s TABLESPACE tsp;
CREATE SCHEMA
test=> DROP TABLESPACE tsp;
DROP TABLESPACE
test=> CREATE TABLE s.t(id SERIAL PRIMARY KEY);
NOTICE: CREATE TABLE will create implicit sequence "t_id_seq" for
serial column "t.id"
ERROR: tablespace with OID 17231 does not exist
DETAIL: The default tablespace for schema "s" has been dropped.


---------------------------------------------------------------------------

PostgreSQL Bugs List wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings