I'll first explain how it is now and then I'll explain how I think it
should behave :-)
I have Postgresql 9.1.4 64bit Linux
- The default tablespace at install is "pg_default" which is (here)
somewhere in /var/lib/...
- The parameter "default_tablespace" in *postgresql.conf* is commented out
(it's original state)
My Tablespace C1 definition:
CREATE TABLESPACE "C1"
My Database C1 definition:
CREATE DATABASE "C1"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = "C1"
LC_COLLATE = 'en_US.utf8'
LC_CTYPE = 'en_US.utf8'
CONNECTION LIMIT = -1;
After these two statements, tablespace C1 should be the default tablespace
for storing all objects in database C1, meaning that when I do not specify
a tablespace, objects are stored there. Right?
I now proceed to do:
CREATE TABLE "TEMP1"
"ID" bigint NOT NULL,
CONSTRAINT "PK_TEMP1" PRIMARY KEY ("ID" )
That makes a table "TEMP1" in tablespace C1. Ok so far.
*But it creates the index of the PK in tablespace "pg_default" !!!*
*If I want the index of that constraint in tablespace C1, I must
explicitely specify it.*
Same for any indexes I create: If I do not specify the tablespace when I
create an index, it goes into "pg_default" :-(
And now, this is how I would like Postgres to behave:
a) ALL objects in a given database, not only tables, should by default go
into the tablespace which is defined as default for the DB - in my case
b) All CREATE commands creating objects into a given database should assume
as the default tablespace the tablespace given as default for the DB, in my
case above "C1".
c) If would be nice if there was an additional (optional) parameter
"INDEX_TABLESPACE" for the CREATE DATABASE command, which would define a
default tablespace to contain indexes for the DB.
d) By extension, it would be nice if there were additional
"<OBJECTTYPE>_TABLESPACE" parameters for the CREATE DATABASE command for
all object types which have to be stored somewhere in the DB.
In any case, I think that how it behaves now is not 100% correct.
Thanks and best wishes,
pgsql-bugs by date
|Next:||From: Tom Lane||Date: 2012-06-29 23:10:08|
|Subject: Re: Feature Request (and/or possible bug) re Default Tablespaces|
|Previous:||From: Heikki Linnakangas||Date: 2012-06-29 16:34:48|
|Subject: Re: BUG #6710: txid_current() provides incorrect txid after