Обсуждение: remove tablespace for primary key (*not* by drop/recreate constraint)

Hello list,

Due to there are lots of foreign key dependencies, would prefer not to 
drop/create for primary key. Is there other way(s) for psql8.3 to remove 
tablespace for primary key please?

For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"

May I know how to remove tablespace(set tablespace to empty for z1)?

Thanks a lot!

Hello, 

to remove tablespace for primary key please?

For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"

May I know how to remove tablespace(set tablespace to empty for z1)?

​ It doesn't make sense to "remove" a tablespace...the best you can do is change a table's (and its related indexes) tablespace

​

 

​ from one to another.

If "ALTER TABLE ... SET TABLESPACE ..." doesn't accomplish your goal you will need to explain yourself better.

Want to SET tablespace = '' for primary key but not table. Tried alter index ... set tablespace='', but empty does not work?

Thanks

On 06/04/2015 11:35 AM, Emi Lu wrote:
> Hello,
>>
>>     to remove tablespace for primary key please?
>>
>>     For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace
>>     "abc"
>>
>>     May I know how to remove tablespace(set tablespace to empty for z1)?
>>
>>
>> ​ It doesn't make sense to "remove" a tablespace...the best you can do
>> is change a table's (and its related indexes) tablespace
>> ​
>> ​ from one to another.
>>
>> If "ALTER TABLE ... SET TABLESPACE ..." doesn't accomplish your goal
>> you will need to explain yourself better.
>
> Want to SET tablespace = '' for primary key but not table. Tried alter
> index ... set tablespace='', but empty does not work?

set tablespace pg_default

>
> Thanks
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

 

to remove tablespace for primary key please?

For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"

May I know how to remove tablespace(set tablespace to empty for z1)?

​ It doesn't make sense to "remove" a tablespace...the best you can do is change a table's (and its related indexes) tablespace

​

 

​ from one to another.

If "ALTER TABLE ... SET TABLESPACE ..." doesn't accomplish your goal you will need to explain yourself better.

Want to SET tablespace = '' for primary key but not table. Tried alter index ... set tablespace='', but empty does not work?

​ So, what you want to do is place the primary key index back onto the default tablespace while the table resides on a different tablespace?

Does this work?

ALTER INDEX ... SET TABLESPACE pg_default;

I think this is what I prefer to run. But it seems that schema owner does not have permission to run it.

"permission denied for tablespace pg_default"

Probably only postmaster can run it?

Thanks a lot!

On Friday, June 5, 2015, Igor Neyman <ineyman@perceptron.com> wrote:

I think this is what I prefer to run. But it seems that schema owner does not have permission to run it. 

"permission denied for tablespace pg_default"

GRANT USAGE ON SCHEMA…

 z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"
how to remove tablespace(set tablespace to empty for z1)?

ALTER INDEX ... SET TABLESPACE pg_default;

This is what I prefer to run. But it seems that schema owner does not have permission to run it.

"permission denied for tablespace pg_default"

Probably only postmaster can run it?

 

GRANT USAGE ON SCHEMA…  GRANT CREATE ON SCHEMA…

schema owner already have full control for the whole schema, this username can create/drop tables/indexs, even drop schema. I think the permission is related to the pg_default - the tablespace. For example, there are 3 tablespaces: pg_default, abc, test (is the one used by table z1)

. alter index pk_z1 set tablespace abc; (success)
. alter index pk_z1 set tablespace test (permission denied)
. alter index pk_z1 set tablespace pg_default (permission denied)