Skip site navigation (1) Skip section navigation (2)

Feature Request (and/or possible bug) re Default Tablespaces

From: r d <rd0002(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Feature Request (and/or possible bug) re Default Tablespaces
Date: 2012-06-29 19:49:57
Message-ID: CALtFtE+SntZtMJUbX03RT8WgZgEUziAkQvX=SL=AupjT_v8hwA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-06-29 23:10:08
Subject: Re: Feature Request (and/or possible bug) re Default Tablespaces
Previous:From: Heikki LinnakangasDate: 2012-06-29 16:34:48
Subject: Re: BUG #6710: txid_current() provides incorrect txid after server startup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group