Обсуждение: Feature Request (and/or possible bug) re Default Tablespaces

Hi,

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"
  OWNER postgres
  LOCATION '/some/path';

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
above, "C1".
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,

RD

r d <rd0002@gmail.com> writes:
> My Database C1 definition:

> CREATE DATABASE "C1" ...
>        TABLESPACE = "C1"

> 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?

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" !!!*

Um ... not for me.  AFAICS, nothing is created under $PGDATA/base
when I do this.  And both the table and the index show up in pg_class
with reltablespace = 0:

d1=# select relname,reltablespace from pg_class where relname like '%TEMP%';
 relname  | reltablespace
----------+---------------
 TEMP1    |             0
 PK_TEMP1 |             0
(2 rows)

which is the correct way of indicating they belong to the database's
default tablespace.  Are you perhaps misinterpreting the zero as meaning
they'll be in pg_default?

            regards, tom lane