Thread: BUG #1236: still in use tablespaces can be removed
The following bug has been logged online: Bug reference: 1236 Logged by: Fabien Email address: coelho@cri.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.
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> Suggested fix: create some empty file in the directory
> if it is used by a namespace.
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
Dear Tom, > > Suggested fix: create some empty file in the directory > > if it is used by a namespace. > > 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. 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@cri.ensmp.fr
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:
>
> Dear Tom,
>
> > > Suggested fix: create some empty file in the directory
> > > if it is used by a namespace.
> >
> > 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.
>
> 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@cri.ensmp.fr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.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
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:
>
> The following bug has been logged online:
>
> Bug reference: 1236
> Logged by: Fabien
>
> Email address: coelho@cri.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@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.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