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

Поиск
Список
Период
Сортировка

remove tablespace for primary key (*not* by drop/recreate constraint)

От
Emi Lu
Дата:
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!

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
"David G. Johnston"
Дата:
On Thu, Jun 4, 2015 at 12:41 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
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

Version 8.3 is no longer supported.​
 
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.


Reading about tablespaces may help you as well.


David J.

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Emi Lu
Дата:
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 />

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Adrian Klaver
Дата:
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

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
"David G. Johnston"
Дата:
On Thu, Jun 4, 2015 at 2:35 PM, Emi Lu <emilu@encs.concordia.ca> 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?


​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;

David J.

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Emi Lu
Дата:
  <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 />

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Igor Neyman
Дата:

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu
Sent: Friday, June 05, 2015 9:33 AM
To: David G. Johnston
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 

 

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!

 

Use:

 

GRANT USAGE ON SCHEMA…

 

 

Regards,

Igor Neyman

 

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Igor Neyman
Дата:

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Igor Neyman
Sent: Friday, June 05, 2015 9:48 AM
To: emilu@encs.concordia.ca; David G. Johnston
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu
Sent: Friday, June 05, 2015 9:33 AM
To: David G. Johnston
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 

 

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!

 

Use:

 

GRANT USAGE ON SCHEMA…

 

 

Regards,

Igor Neyman

 

Actually, you probably need:

 

GRANT CREATE ON SCHEMA…

 

 

Regards,

Igor Neyman

 

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
"David G. Johnston"
Дата:


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…


Tablespace != schema ...

David J. 

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Emi Lu
Дата:
<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 />

Re: remove tablespace for primary key (*not* by drop/recreate constraint)

От
Igor Neyman
Дата:

 

 

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Friday, June 05, 2015 9:59 AM
To: Igor Neyman
Cc: emilu@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 



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…

 

Tablespace != schema ...

 

David J. 

 

--

You are right, of course:

 

GRANT CREATE ON TABLESPACE…

 

Igor Neyman