Thread: Tablespace issues (comment on ,moving indexes)
Hi! I'm currently working on the psql tab-complete code, fixing quite a lot of bugs/annoyances in the process. One of the things I'm trying to do is syncing the available commands in psql with the docs - during this work I found two irritating things regarding tablespaces: 1. there is no COMMENT ON TABLESPACE support - it is neither documented nor does it seem to work using the obvious syntax (COMMENT ON TABLESPACE 'foo' IS 'bar'). 2. how is one supposed to move indexes(not tables) to another tablespace? The (devel)docs have this in the ALTER TABLE - section: "This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. " not sure how to interpret that - who would an example for moving an index look like given that (AFAIR there is nothing like ALTER INDEX 'foo' SET TABLESPACE 'bar') ? thanks Stefan
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> 1. there is no COMMENT ON TABLESPACE support
That's right.
> 2. how is one supposed to move indexes(not tables) to another tablespace?
Use ALTER TABLE on the index.
regards, tom lane
Tom Lane wrote: > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes: > > 1. there is no COMMENT ON TABLESPACE support > > That's right. > > > 2. how is one supposed to move indexes(not tables) to another tablespace? > > Use ALTER TABLE on the index. Hmm...not ALTER INDEX? Now that there's an operation that actually modifies an index instead of the table itself, should there be an ALTER INDEX? It would be cleaner and more consistent, IMO... -- Kevin Brown kevin@sysexperts.com
Tom Lane wrote: > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes: > > 1. there is no COMMENT ON TABLESPACE support > > That's right. Added to TODO: * Add COMMENT for tablespaces -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Mon, 9 Aug 2004, Bruce Momjian wrote: > Tom Lane wrote: > > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes: > > > 1. there is no COMMENT ON TABLESPACE support > > > > That's right. > > Added to TODO: > > * Add COMMENT for tablespaces Well, Chris did bring this up but it will have the same problem as other shared tables, from memory. That is, you can add the comment in one database, but wont see if from another. Did I misunderstand? Gavin
Gavin Sherry wrote: > On Mon, 9 Aug 2004, Bruce Momjian wrote: > > > Tom Lane wrote: > > > Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes: > > > > 1. there is no COMMENT ON TABLESPACE support > > > > > > That's right. > > > > Added to TODO: > > > > * Add COMMENT for tablespaces > > Well, Chris did bring this up but it will have the same problem as other > shared tables, from memory. That is, you can add the comment in one > database, but wont see if from another. > > Did I misunderstand? Oh, that shared thing! OK, removed. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Mon, Aug 09, 2004 at 06:47:45PM -0400, Bruce Momjian wrote: > Gavin Sherry wrote: > > Well, Chris did bring this up but it will have the same problem as other > > shared tables, from memory. That is, you can add the comment in one > > database, but wont see if from another. > > Oh, that shared thing! OK, removed. How about a TODO for allowing comments for global objects, if there isn't one already? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> Added to TODO:
>>>
>>> * Add COMMENT for tablespaces
> Oh, that shared thing! OK, removed.
Well, it's a legitimate thing to have in TODO, just as long as you don't
think it's trivial ;-). But don't we already have a TODO item about
properly supporting comments on shared objects? Databases, users,
groups, and now tablespaces all have the same issue.
regards, tom lane
Kevin Brown <kevin@sysexperts.com> writes:
> Tom Lane wrote:
>> Use ALTER TABLE on the index.
> Hmm...not ALTER INDEX? Now that there's an operation that actually
> modifies an index instead of the table itself, should there be an ALTER
> INDEX? It would be cleaner and more consistent, IMO...
[ shrug ] There have been some variants of ALTER TABLE that would work
on indexes since day one. Sequences too.
regards, tom lane
> Hmm...not ALTER INDEX? Now that there's an operation that actually > modifies an index instead of the table itself, should there be an ALTER > INDEX? It would be cleaner and more consistent, IMO... Errr, unlike all the other uses for alter table and friends? ie: OWNER TO RENAME TO SET TABLESPACE etc. Lots of things against tables work against indexes and views. Some stuff for commenting on columns say works on views, composite types and indexes! Chris
>>>1. there is no COMMENT ON TABLESPACE support >> >>That's right. That's deliberate. > Added to TODO: > > * Add COMMENT for tablespaces You may as well make that: * Add COMMENT ON for all cluster global objects (users, groups, databases and tablespaces) Chris
OK, added. --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > >>>1. there is no COMMENT ON TABLESPACE support > >> > >>That's right. > > That's deliberate. > > > Added to TODO: > > > > * Add COMMENT for tablespaces > > You may as well make that: > > * Add COMMENT ON for all cluster global objects (users, groups, > databases and tablespaces) > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Christopher Kings-Lynne wrote: > >Hmm...not ALTER INDEX? Now that there's an operation that actually > >modifies an index instead of the table itself, should there be an ALTER > >INDEX? It would be cleaner and more consistent, IMO... > > Errr, unlike all the other uses for alter table and friends? ie: > > OWNER TO Which changes the attributes of the table... > RENAME TO Same. > SET TABLESPACE Which again changes the attributes of the table.. But using ALTER TABLE to change the tablespace that an index belongs to doesn't change an attribute of a table, it changes the attribute of an index. > etc. > > Lots of things against tables work against indexes and views. Some > stuff for commenting on columns say works on views, composite types and > indexes! No doubt. Of course, that something's been done a certain way in the past doesn't imply that it's the right way to do something new, nor does it imply that the new thing must be done that way. I mean, it's not a terribly big deal or anything, but since we're talking about stuff that isn't in the SQL spec it seems reasonable to define the commands in such a way that they don't violate the principle of least surprise. Using ALTER TABLE to alter the characteristics of an index violates that principle, at least in my opinion. It's not the first command I would have thought of when asking myself "how do I change the tablespace of an index?" -- ALTER INDEX is. And the reason is simple: we use CREATE INDEX to create an index and DROP INDEX to drop one -- we don't use ALTER TABLE subcommands to create or drop indexes. Why, then, should modification of an index's properties be treated any differently than the rest of the index manipulation commands? I just happen to like consistency. :-) -- Kevin Brown kevin@sysexperts.com
>>Errr, unlike all the other uses for alter table and friends? ie: >> >>OWNER TO > > > Which changes the attributes of the table... And indexes. >>RENAME TO > > > Same. And indexes. >>SET TABLESPACE > > > Which again changes the attributes of the table.. And indexes. Chris
Christopher Kings-Lynne wrote: > >>Errr, unlike all the other uses for alter table and friends? ie: > >> > >>OWNER TO > > > >Which changes the attributes of the table... > > And indexes. Sure. But not *just* indexes. > >>RENAME TO > > > >Same. > > And indexes. It does? I thought the indexes pointed to relations directly, not to tables by name, and so changing the name of the table wouldn't have any effect on the indexes, right? > >>SET TABLESPACE > > > >Which again changes the attributes of the table.. > > And indexes. But it does change more than just the indexes. But the context here is changing the tablespace of indexes independently of the tablespace for the table. For that, how exactly does it affect the table metadata? Not at all, I'd wager. If you're going to go use ALTER TABLE to make changes to the attributes of indexes, might I suggest that you also use ALTER TABLE to create and destroy them as well? Otherwise you end up with an inconsistent language, which is fine if the spec calls for it or if you somehow are attempting to maintain compatibility with something. But what we're talking about here is brand new functionality for which the language hasn't been defined yet. It would be a bit unfortunate to introduce inconsistencies where they're not needed, wouldn't you say? -- Kevin Brown kevin@sysexperts.com
Kevin Brown <kevin@sysexperts.com> writes:
> ... But what we're talking about
> here is brand new functionality for which the language hasn't been
> defined yet.
You're missing the point, which is that there *is* a precedent of long
standing. ALTER TABLE has worked on indexes (and sequences, and views)
for those cases in which the operation sensibly applied for a long time.
In particular, the original 7.1 implementation of ALTER TABLE OWNER
would work on tables, indexes, sequences, and views. Should we really
have insisted on inventing four syntaxes for the identical operation?
Maybe, but we didn't, and now there is a precedent to follow.
regards, tom lane
Tom Lane wrote: > Kevin Brown <kevin@sysexperts.com> writes: > > ... But what we're talking about > > here is brand new functionality for which the language hasn't been > > defined yet. > > You're missing the point, which is that there *is* a precedent of long > standing. ALTER TABLE has worked on indexes (and sequences, and views) > for those cases in which the operation sensibly applied for a long time. > In particular, the original 7.1 implementation of ALTER TABLE OWNER > would work on tables, indexes, sequences, and views. Should we really > have insisted on inventing four syntaxes for the identical operation? > Maybe, but we didn't, and now there is a precedent to follow. And yet we have ALTER SEQUENCE. In 7.4, we seem to have: ALTER AGGREGATE ALTER CONVERSION ALTER DATABASE ALTER DOMAIN ALTER FUNCTION ALTER GROUP ALTER LANGUAGE ALTER OPERATOR CLASS ALTER SCHEMA ALTER SEQUENCE ALTER TABLE ALTER TRIGGER ALTER USER Within ALTER TABLE, you can change: 1. columns 2. the table name 3. constraints 4. table ownership 5. index clustering and within those, only (2) and (4) apply to sequences and views, and (5) is the only ALTER TABLE operation that applies to indexes (corrections to this welcome). Furthermore, the rename operation for triggers, languages, groups, functions, databases, conversions, and aggregates are all implemented in their own ALTER statement (indeed, the rename operation is the only ALTER operation for some of those). The decision to roll some of the functionality affecting sequences and views into ALTER TABLE is at least somewhat sensible: those things look like tables in at least one key way, namely that they can be SELECTed from. That's not true of indexes, and so that reasoning does not apply to using ALTER TABLE to change an index's tablespace. It appears to me that the precedent for creating a new ALTER statement is actually much bigger than the precedent for rolling functionality into ALTER TABLE, based on the above. But that's just my bird's eye view on things. I'm sure lots of people disagree with me on this. :-) I'm certainly not arguing for a wholesale rework of the syntax in order to achieve maximum consistency (nice as that might be), but it seems to me that it would be a mistake to introduce more inconsistency than is already there when it's not necessary to do so. -- Kevin Brown kevin@sysexperts.com
I wrote: > I'm certainly not arguing for a wholesale rework of the syntax in order > to achieve maximum consistency (nice as that might be), but it seems to > me that it would be a mistake to introduce more inconsistency than is > already there when it's not necessary to do so. What I mean here is that I think it would be in our best interests to define the syntax for any new operation to be as easily guessed as possible. I believe that ALTER INDEX would be more easily guessed by more people as the means by which one would alter an index's tablespace than ALTER TABLE, even if those people have a decent amount of PG experience. -- Kevin Brown kevin@sysexperts.com
> What I mean here is that I think it would be in our best interests to > define the syntax for any new operation to be as easily guessed as > possible. I believe that ALTER INDEX would be more easily guessed by > more people as the means by which one would alter an index's tablespace > than ALTER TABLE, even if those people have a decent amount of PG > experience. I agree. Whether or not it gets done though is the question... Chris
On Tuesday 10 August 2004 22:13, Christopher Kings-Lynne wrote:
> > What I mean here is that I think it would be in our best interests to
> > define the syntax for any new operation to be as easily guessed as
> > possible. I believe that ALTER INDEX would be more easily guessed by
> > more people as the means by which one would alter an index's tablespace
> > than ALTER TABLE, even if those people have a decent amount of PG
> > experience.
>
> I agree. Whether or not it gets done though is the question...
>
But it should be on the TODO imho.
--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL
Added to TODO:
o Add ALTER INDEX that works just like ALTER TABLE already does on an index
---------------------------------------------------------------------------
Robert Treat wrote:
> On Tuesday 10 August 2004 22:13, Christopher Kings-Lynne wrote:
> > > What I mean here is that I think it would be in our best interests to
> > > define the syntax for any new operation to be as easily guessed as
> > > possible. I believe that ALTER INDEX would be more easily guessed by
> > > more people as the means by which one would alter an index's tablespace
> > > than ALTER TABLE, even if those people have a decent amount of PG
> > > experience.
> >
> > I agree. Whether or not it gets done though is the question...
> >
>
> But it should be on the TODO imho.
>
> --
> Robert Treat
> Build A Better Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Added to TODO;
o Add ALTER INDEX syntax to work like ALTER TABLE indexname
---------------------------------------------------------------------------
Christopher Kings-Lynne wrote:
> > What I mean here is that I think it would be in our best interests to
> > define the syntax for any new operation to be as easily guessed as
> > possible. I believe that ALTER INDEX would be more easily guessed by
> > more people as the means by which one would alter an index's tablespace
> > than ALTER TABLE, even if those people have a decent amount of PG
> > experience.
>
> I agree. Whether or not it gets done though is the question...
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
I posted a patch for this last Friday 13th. Gavin On Mon, 16 Aug 2004, Bruce Momjian wrote: > > Added to TODO; > > o Add ALTER INDEX syntax to work like ALTER TABLE indexname > > --------------------------------------------------------------------------- > > Christopher Kings-Lynne wrote: > > > What I mean here is that I think it would be in our best interests to > > > define the syntax for any new operation to be as easily guessed as > > > possible. I believe that ALTER INDEX would be more easily guessed by > > > more people as the means by which one would alter an index's tablespace > > > than ALTER TABLE, even if those people have a decent amount of PG > > > experience. > > > > I agree. Whether or not it gets done though is the question... > > > > Chris > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > > > !DSPAM:4120de11152569085518527! > >