Обсуждение: CREATE DATABASE Š [ TEMPLATE [=] template ] [TABLESPACE [=] tablespace_name ]

Tim Kane wrote
> The documentation describes the tablespace parameter as:
> The name of the tablespace that will be associated with the new database,
> or
> DEFAULT to use the template database's tablespace. This tablespace will be
> the default tablespace used for objects created in this database. See
> CREATE
> TABLESPACE
> <http://www.postgresql.org/docs/9.3/static/sql-createtablespace.html>
> for
> more information.
>
> Is this a documentation issue, or a behavioural one?  It isn't clear to
> me.

The operative sentence is:

"This tablespace will be the default tablespace used for [new] objects
created in this database."

The implied "new" (inserted because of the word "default" preceding) means
that any existing objects in the template database will be assigned the same
tablespace onto which they are currently attached.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/CREATE-DATABASE-TEMPLATE-template-TABLESPACE-tablespace-name-tp5772451p5772464.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Tim Kane wrote
> However it seems this is not the case, and that the newly specified
> tablespace will only apply to objects that do not already have a
> tablespace
> defined in the templated database.

Note that all pre-existing objects have a tablespace by definition.  If one
is not explicitly provided during the object's creation then the database
default tablespace is assigned and permanently affixed.  Thus objects in the
tempate's default tablespace should not (in theory) be affected by changing
the default tablespace of the newly created database - those default
tablespace template objects will still be in the template's default
tablespace - or whichever tablespace was default when the object was created
(since the defaults can be changed).

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/CREATE-DATABASE-TEMPLATE-template-TABLESPACE-tablespace-name-tp5772451p5772466.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I have a question regarding the behaviour of CREATE DATABASE when used with TEMPLATE and TABLESPACE options together.

The documentation describes the tablespace parameter as:

The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database's tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information.

I would take that to mean that all objects from the template will be created in the new database within the specified tablespace.

This is possible setting the parameter 'default_tablespace':

SET default_tablespace = space1;

in this way, an implicit TABLESPACE clause is supplied in the objects creation, independently it's done from a template or not. 

Giuseppe.

-- 
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it