agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedremove tablespace for primary key (*not* by drop/recreate constraint)
11+ messages / 4 participants
[nested] [flat]
* remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-04 16:41 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Emi Lu @ 2015-06-04 16:41 UTC (permalink / raw)
To: pgsql-sql
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!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-04 17:44 David G. Johnston <david.g.johnston@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2015-06-04 17:44 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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.
http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html
Reading about tablespaces may help you as well.
http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html
http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html
David J.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-04 18:35 Emi Lu <emilu@encs.concordia.ca>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Emi Lu @ 2015-06-04 18:35 UTC (permalink / raw)
To: pgsql-sql
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Hello, <br>
<blockquote
cite="mid:CAKFQuwaCfLjPv1UxfpkQUgN1Fh1AuZS=nHWXWKX5t-=U+9qfBg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">to
remove tablespace for 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>
<div class="gmail_default"
style="font-family:arial,helvetica,sans-serif;display:inline">
It doesn't make sense to "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. Tried
alter index ... set tablespace='', but empty does not work? <br>
<br>
Thanks<br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-04 18:50 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 0 replies; 11+ messages in thread
From: Adrian Klaver @ 2015-06-04 18:50 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-04 18:51 David G. Johnston <david.g.johnston@gmail.com>
parent: Emi Lu <emilu@encs.concordia.ca>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2015-06-04 18:51 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 13:33 Emi Lu <emilu@encs.concordia.ca>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Emi Lu @ 2015-06-05 13:33 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<br>
<blockquote
cite="mid:CAKFQuwb9MYyyQxOFp5QDc1wjQjsZOPJ964KujvRkTFB1VPgM=g@mail.gmail.com"
type="cite">
<div dir="ltr">
<div class="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 text="#000000" bgcolor="#FFFFFF"><span class="">
<blockquote type="cite">
<div dir="ltr">
<div class="gmail_extra">
<div class="gmail_quote">
<blockquote class="gmail_quote"
style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">to
remove tablespace for 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>
<div
style="font-family:arial,helvetica,sans-serif;display:inline">
It doesn't make sense to "remove" a
tablespace...the best 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 your goal 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
back onto 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 owner
does not have permission to run it. <br>
<br>
"permission denied for tablespace pg_default"<br>
<br>
Probably only postmaster can run it?<br>
<br>
Thanks a lot!<br>
</body>
</html>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 13:47 Igor Neyman <ineyman@perceptron.com>
parent: Emi Lu <emilu@encs.concordia.ca>
0 siblings, 1 reply; 11+ messages in thread
From: Igor Neyman @ 2015-06-05 13:47 UTC (permalink / raw)
To: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 13:52 Igor Neyman <ineyman@perceptron.com>
parent: Igor Neyman <ineyman@perceptron.com>
0 siblings, 2 replies; 11+ messages in thread
From: Igor Neyman @ 2015-06-05 13:52 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; emilu@encs.concordia.ca <emilu@encs.concordia.ca>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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> [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<mailto: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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 13:59 David G. Johnston <david.g.johnston@gmail.com>
parent: Igor Neyman <ineyman@perceptron.com>
1 sibling, 1 reply; 11+ messages in thread
From: David G. Johnston @ 2015-06-05 13:59 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; +Cc: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 14:00 Emi Lu <emilu@encs.concordia.ca>
parent: Igor Neyman <ineyman@perceptron.com>
1 sibling, 0 replies; 11+ messages in thread
From: Emi Lu @ 2015-06-05 14:00 UTC (permalink / raw)
To: Igor Neyman <ineyman@perceptron.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<blockquote
cite="mid:A76B25F2823E954C9E45E32FA49D70ECCD45F1F1@mail.corp.perceptron.com"
type="cite">
<div class="WordSection1">
<p class="MsoNormal"><br>
<o:p></o:p>
</p>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<div>
<blockquote style="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>
<blockquote
style="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_z1 PRIMARY KEY (c1), tablespace "abc"<br>
how to remove tablespace(set tablespace
to empty for z1)?<o:p></o:p></p>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</blockquote>
<div>
<div>
<p class="MsoNormal"><span
style="font-family:"Arial",sans-serif">ALTER
INDEX ... SET TABLESPACE pg_default;<o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</blockquote>
<div style="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 does not have permission to run it.
<br>
<br>
"permission denied for tablespace pg_default"<br>
<br>
Probably only postmaster can run it?<br>
<span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p></o:p></span></p>
</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"><o:p></o:p></span>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">GRANT
USAGE ON SCHEMA…<o:p></o:p></span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p>
</o:p></span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">GRANT
CREATE ON SCHEMA…<o:p></o:p></span></p>
</div>
</blockquote>
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) <br>
<br>
. alter index pk_z1 set tablespace abc; (success) <br>
. alter index pk_z1 set tablespace test (permission denied) <br>
. alter index pk_z1 set tablespace pg_default (permission denied) <br>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: remove tablespace for primary key (*not* by drop/recreate constraint)
@ 2015-06-05 14:25 Igor Neyman <ineyman@perceptron.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Igor Neyman @ 2015-06-05 14:25 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: emilu@encs.concordia.ca <emilu@encs.concordia.ca>; pgsql-sql
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<mailto: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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2015-06-05 14:25 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-04 16:41 remove tablespace for primary key (*not* by drop/recreate constraint) Emi Lu <emilu@encs.concordia.ca>
2015-06-04 17:44 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-04 18:35 ` Emi Lu <emilu@encs.concordia.ca>
2015-06-04 18:50 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-06-04 18:51 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-05 13:33 ` Emi Lu <emilu@encs.concordia.ca>
2015-06-05 13:47 ` Igor Neyman <ineyman@perceptron.com>
2015-06-05 13:52 ` Igor Neyman <ineyman@perceptron.com>
2015-06-05 13:59 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-05 14:25 ` Igor Neyman <ineyman@perceptron.com>
2015-06-05 14:00 ` Emi Lu <emilu@encs.concordia.ca>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox