Thread: CREATE DATABASE with tablespace fix
Attached is a patch resolving the issue raised here: http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4 When I was testing this, I noticed the following: template1=# create tablespace blah location '/home/gavins/pgsql/blah'; CREATE TABLESPACE template1=# create table foo(i int) tablespace blah; CREATE TABLE template1=# create database bar tablespace blah; ERROR: template database "template1" is already using tablespace "blah" DETAIL: The default tablespace for a database cannot be in use by the template database template1=# drop table foo; DROP TABLE template1=# create database bar tablespace blah; ERROR: template database "template1" is already using tablespace "blah" DETAIL: The default tablespace for a database cannot be in use by the template database This happens because even though we drop the only entry in the tablespace we keep the empty database directory around. Should be test if the directory is empty and if so, not copy it (perhaps only if it is in the tablespace which will be the default tablespace of the new database?) Gavin
Attachment
Gavin Sherry wrote: > Attached is a patch resolving the issue raised here: > > http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4 > > When I was testing this, I noticed the following: > > template1=# create tablespace blah location '/home/gavins/pgsql/blah'; > CREATE TABLESPACE > template1=# create table foo(i int) tablespace blah; > CREATE TABLE > template1=# create database bar tablespace blah; > ERROR: template database "template1" is already using tablespace "blah" > DETAIL: The default tablespace for a database cannot be in use by the > template > database > template1=# drop table foo; > DROP TABLE > template1=# create database bar tablespace blah; > ERROR: template database "template1" is already using tablespace "blah" > DETAIL: The default tablespace for a database cannot be in use by the > template database > > This happens because even though we drop the only entry in the tablespace > we keep the empty database directory around. > > Should be test if the directory is empty and if so, not copy it (perhaps > only if it is in the tablespace which will be the default tablespace of > the new database?) OK, I am finally following this issue. I couldn't figure out why we were throwing any error, let alone a strange one. Then I read the command in dbcommands.c: /* * Iterate through all tablespaces of the template database, and * copy each one to the new database. * * If we are trying to change the default tablespace of the template, * we require that the template not have any files in the new default * tablespace. This avoids the need to merge two subdirectories. * This could probably be improved later. */ I am a little confused why we can't just merge the directories? The relfilenodes would guarantee that the files are unique, right? Seems we should apply this patch with improved wording removing the "in use" mention because it isn't an "in use" issue as much as a references issue, and add a TODO to allow tablespaces to merge directories. -- 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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am a little confused why we can't just merge the directories? The
> relfilenodes would guarantee that the files are unique, right?
Actually not: there is no uniqueness check on relfilenode beyond the
indirect one of the files having to live in the same directory. In the
tablespace world it will be possible for tables in different tablespaces
to have conflicting relfilenodes. Admittedly this should be rare and
it's probably good enough to fail only if a collision occurs --- but
it's something that would have to be kept in mind while coding.
Another issue is that since we aren't changing the template's pg_class,
we would end up with a database in which some tables refer to the
database's default tablespace explicitly (reltablespace = its OID) and
some refer to it implicitly (reltablespace = 0). I'm not sure what the
consequences of that are, but it might not be good. It would definitely
be a state that you couldn't get into "normally". (One fairly likely
scenario for failure is if you then try to copy this database and change
the default TS again...)
> Seems we should apply this patch
I think it needs more thought. I've been busy with other issues and
haven't expended any cycles on the problem myself ...
regards, tom lane
Added to open items:
* fix case where template db already uses target tablespace
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I am a little confused why we can't just merge the directories? The
> > relfilenodes would guarantee that the files are unique, right?
>
> Actually not: there is no uniqueness check on relfilenode beyond the
> indirect one of the files having to live in the same directory. In the
> tablespace world it will be possible for tables in different tablespaces
> to have conflicting relfilenodes. Admittedly this should be rare and
> it's probably good enough to fail only if a collision occurs --- but
> it's something that would have to be kept in mind while coding.
>
> Another issue is that since we aren't changing the template's pg_class,
> we would end up with a database in which some tables refer to the
> database's default tablespace explicitly (reltablespace = its OID) and
> some refer to it implicitly (reltablespace = 0). I'm not sure what the
> consequences of that are, but it might not be good. It would definitely
> be a state that you couldn't get into "normally". (One fairly likely
> scenario for failure is if you then try to copy this database and change
> the default TS again...)
>
> > Seems we should apply this patch
>
> I think it needs more thought. I've been busy with other issues and
> haven't expended any cycles on the problem myself ...
>
> regards, tom lane
>
--
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 show this as fixed in CVS. --------------------------------------------------------------------------- Gavin Sherry wrote: > Attached is a patch resolving the issue raised here: > > http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4 > > When I was testing this, I noticed the following: > > template1=# create tablespace blah location '/home/gavins/pgsql/blah'; > CREATE TABLESPACE > template1=# create table foo(i int) tablespace blah; > CREATE TABLE > template1=# create database bar tablespace blah; > ERROR: template database "template1" is already using tablespace "blah" > DETAIL: The default tablespace for a database cannot be in use by the > template > database > template1=# drop table foo; > DROP TABLE > template1=# create database bar tablespace blah; > ERROR: template database "template1" is already using tablespace "blah" > DETAIL: The default tablespace for a database cannot be in use by the > template database > > This happens because even though we drop the only entry in the tablespace > we keep the empty database directory around. > > Should be test if the directory is empty and if so, not copy it (perhaps > only if it is in the tablespace which will be the default tablespace of > the new database?) > > Gavin Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- 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