Thread: 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,  <br /><blockquote cite="mid:CAKFQuwaCfLjPv1UxfpkQUgN1Fh1AuZS=nHWXWKX5t-=U+9qfBg@mail.gmail.com"
type="cite"><divdir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote"
style="margin:0px0px 0px
 
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">to remove
tablespacefor primary key please?<br /><br /> For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace
"abc"<br/><br /> May I know how to remove tablespace(set tablespace to empty for z1)?<br /></blockquote><div><br
/></div><div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif;display:inline">​ It doesn't make
senseto "remove" a tablespace...the best you can do is change a table's (and its related indexes) tablespace</div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline">​</div>   <div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">​from one to another.</div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline"><br /></div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline">If "ALTER TABLE ... SET TABLESPACE
..."doesn't accomplish your goal you will need to explain yourself
better.</div></div></div></div></div></blockquote><br/> Want to SET tablespace = '' for primary key but not table.
Triedalter index ... set tablespace='', but empty does not work? <br /><br /> Thanks<br /><br /> 

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

  <br /><blockquote cite="mid:CAKFQuwb9MYyyQxOFp5QDc1wjQjsZOPJ964KujvRkTFB1VPgM=g@mail.gmail.com" type="cite"><div
dir="ltr"><divclass="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0
  .8ex;border-left:1px #ccc solid;padding-left:1ex"><div bgcolor="#FFFFFF" text="#000000"><span class=""><blockquote
type="cite"><divdir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote"
style="margin:0px0px 0px
 
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">to remove
tablespacefor primary key please?<br /><br /> For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace
"abc"<br/><br /> May I know how to remove tablespace(set tablespace to empty for z1)?<br /></blockquote><div><br
/></div><div><divstyle="font-family:arial,helvetica,sans-serif;display:inline">​ It doesn't make sense to "remove" a
tablespace...thebest you can do is change a table's (and its related indexes) tablespace</div><div
style="font-family:arial,helvetica,sans-serif;display:inline">​</div>  <div
style="font-family:arial,helvetica,sans-serif;display:inline">​from one to another.</div></div><div><div
style="font-family:arial,helvetica,sans-serif;display:inline"><br/></div></div><div><div
style="font-family:arial,helvetica,sans-serif;display:inline">If"ALTER TABLE ... SET TABLESPACE ..." doesn't accomplish
yourgoal you will need to explain yourself better.</div></div></div></div></div></blockquote><br /></span> Want to SET
tablespace= '' for primary key but not table. Tried alter index ... set tablespace='', but empty does not work? <br
/><br/></div></blockquote><div><br /></div><div><div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">​So, what you want to do is place the primary key index
backonto the default tablespace while the table resides on a different tablespace?</div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline"><br /></div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline">Does this work?</div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline"><br /></div></div><div><div
class="gmail_default"style="font-family:arial,helvetica,sans-serif;display:inline">ALTER INDEX ... SET TABLESPACE
pg_default;</div></div></div></div></div></blockquote>I think this is what I prefer to run. But it seems that schema
ownerdoes not have permission to run it. <br /><br /> "permission denied for tablespace pg_default"<br /><br />
Probablyonly postmaster can run it?<br /><br /> Thanks a lot!<br /> 

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…

<blockquote cite="mid:A76B25F2823E954C9E45E32FA49D70ECCD45F1F1@mail.corp.perceptron.com" type="cite"><div
class="WordSection1"><pclass="MsoNormal"><br /><blockquote
style="margin-top:5.0pt;margin-bottom:5.0pt"><div><div><div><blockquotestyle="border:none;border-left:solid #CCCCCC
           1.0pt;padding:0in 0in 0in
 
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"><div><blockquote
style="margin-top:5.0pt;margin-bottom:5.0pt"><div><div><div><blockquotestyle="border:none;border-left:solid
               #CCCCCC 1.0pt;padding:0in 0in 0in
 
6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"><p class="MsoNormal"> z1 (c1 text) with
pk_z1PRIMARY KEY (c1), tablespace "abc"<br /> how to remove tablespace(set tablespace to empty for
z1)?</blockquote></div></div></div></blockquote></div></blockquote><div><div><pclass="MsoNormal"><span
style="font-family:"Arial",sans-serif">ALTERINDEX ... SET TABLESPACE
pg_default;</span></div></div></div></div></div></blockquote><divstyle="border:none;border-bottom:solid windowtext
  1.0pt;padding:0in 0in 1.0pt 0in"><p class="MsoNormal">This is what I prefer to run. But it seems that schema owner
doesnot have permission to run it. <br /><br /> "permission denied for tablespace pg_default"<br /><br /> Probably only
postmastercan run it?<br /><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"></span></div></div></blockquote><blockquote
cite="mid:A76B25F2823E954C9E45E32FA49D70ECCD45F1F1@mail.corp.perceptron.com"type="cite"><div class="WordSection1"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"></span> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">GRANTUSAGE ON SCHEMA…</span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> </span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">GRANTCREATE ON
SCHEMA…</span></div></blockquote>schema owner already have full control for the whole schema, this username can
create/droptables/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) <br /><br /> . alter index pk_z1
settablespace abc; (success) <br /> . alter index pk_z1 set tablespace test (permission denied) <br /> . alter index
pk_z1set tablespace pg_default (permission denied) <br /><br />