Re: 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(at)kaltenbrunner(dot)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(at)kaltenbrunner(dot)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(at)sysexperts(dot)com

Tom Lane wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, 9 Aug 2004, Bruce Momjian wrote:

> Tom Lane wrote:
> > Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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(at)kaltenbrunner(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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[(at)]dcc(dot)uchile(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)sysexperts(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)sysexperts(dot)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(at)sysexperts(dot)com

Kevin Brown <kevin(at)sysexperts(dot)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(at)sysexperts(dot)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(at)sysexperts(dot)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(at)sysexperts(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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(at)candle(dot)pha(dot)pa(dot)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!
>
>