ALTER TABLESPACE ... MOVE ALL TO ...

Greetings,

It's a day late and I'm a dollar short, but attached is a (very) minor
patch to allow users to more easily move their various objects from
one tablespace to another. Included are docs and a regression test;
I'm happy to improve on both should folks send me suggestions.

As we use tablespaces quite a bit, this can be extremely handy for us
and I expect others will find it useful too.

Thoughts?

Thanks,

Stephen

On Thu, Jan 16, 2014 at 4:37 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Greetings,
>
> It's a day late and I'm a dollar short, but attached is a (very) minor
> patch to allow users to more easily move their various objects from
> one tablespace to another. Included are docs and a regression test;
> I'm happy to improve on both should folks send me suggestions.
>
> As we use tablespaces quite a bit, this can be extremely handy for us
> and I expect others will find it useful too.
>
> Thoughts?

Don't be late next time?

I did look this over and it seems fine.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 16 January 2014 22:37, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> allow users to more easily move their various objects from
> one tablespace to another. Included are docs and a regression test;
> I'm happy to improve on both should folks send me suggestions.

Sounds good.

The command uses the word ALL but then less than all objects, i.e.
only moves objects that are owned by the user.

I would like to see two variants of this...

ALL ... which attempts to move all objects and fails if it doesn't own
everything
ALL OWNED ... which moves only objects that it owns, and ignores others

i.e. ALL should mean all

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> The command uses the word ALL but then less than all objects, i.e.
> only moves objects that are owned by the user.

My thinking was that it was "all" from that user's perspective.

> I would like to see two variants of this...
>
> ALL ... which attempts to move all objects and fails if it doesn't own
> everything
> ALL OWNED ... which moves only objects that it owns, and ignores others

I could add that, though it feels like the next request would be to
allow a specific role to be passed in (ie: move all of *this* user's
objects) and I'm not sure we really need to go to that level. It
doesn't seem like there's really much point in having two options
either- "ALL OWNED" run by the superuser would be identical to "ALL" and
normal users would have zero use for just "ALL" because it would either
be identical to "ALL OWNED" or it would fail with a permission denied
error.

If an extra noise word to clarify what is happening would be useful,
then I could simply require "OWNED" as well, but I'm not particularly
thrilled with that option, also ...

> i.e. ALL should mean all

This is a bit of a non-starter when it comes to tablespaces anyway- we
can't move another database's objects and so even if it was "ALL", it
may only be moving a subset of the objects in the tablespace (namely
those which are in the current database). I don't see it being an
improvement to require "IN CURRENT DATABASE ALL OWNED" even though it
would be more accurate.

Thanks,

Stephen

On 20 January 2014 14:24, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
>> The command uses the word ALL but then less than all objects, i.e.
>> only moves objects that are owned by the user.
>
> My thinking was that it was "all" from that user's perspective.
>
>> I would like to see two variants of this...
>>
>> ALL ... which attempts to move all objects and fails if it doesn't own
>> everything
>> ALL OWNED ... which moves only objects that it owns, and ignores others
>
> I could add that, though it feels like the next request would be to
> allow a specific role to be passed in (ie: move all of *this* user's
> objects) and I'm not sure we really need to go to that level. It
> doesn't seem like there's really much point in having two options
> either- "ALL OWNED" run by the superuser would be identical to "ALL" and
> normal users would have zero use for just "ALL" because it would either
> be identical to "ALL OWNED" or it would fail with a permission denied
> error.
>
> If an extra noise word to clarify what is happening would be useful,
> then I could simply require "OWNED" as well, but I'm not particularly
> thrilled with that option, also ...
>
>> i.e. ALL should mean all
>
> This is a bit of a non-starter when it comes to tablespaces anyway- we
> can't move another database's objects and so even if it was "ALL", it
> may only be moving a subset of the objects in the tablespace (namely
> those which are in the current database). I don't see it being an
> improvement to require "IN CURRENT DATABASE ALL OWNED" even though it
> would be more accurate.

Not a good argument since IN CURRENT DATABASE applies to all SQL
commands, so would clearly be unnecessary.

At the moment, ALL does not include all objects. It's a POLA violation
to have a command affect just some objects and not others. That is
especially confusing when the command run as Superuser *will* move all
objects and a RC of zero has different meaning dependent upon who the
user is that executes the command.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> Not a good argument since IN CURRENT DATABASE applies to all SQL
> commands, so would clearly be unnecessary.

I suppose it depends on how you're looking at it.

ALTER TABLESPACE ... RENAME, for example, updates a shared catalog and
therefore the change is seen across all databases. That's not exactly
"IN CURRENT DATABASE".

> At the moment, ALL does not include all objects. It's a POLA violation
> to have a command affect just some objects and not others. That is
> especially confusing when the command run as Superuser *will* move all
> objects and a RC of zero has different meaning dependent upon who the
> user is that executes the command.

So you're still looking for an 'OWNED' noise word to be added? Also, I
did add the ability to specify types of objects (it's often that we'll
have a "INDEXES" tablespace, so this made sense), so how about:

ALTER TABLESPACE name MOVE OWNED TO name opt_nowait
ALTER TABLESPACE name MOVE TABLES OWNED TO name opt_nowait
ALTER TABLESPACE name MOVE INDEXES OWNED TO name opt_nowait
ALTER TABLESPACE name MOVE MATERIALIZED VIEWS OWNED TO name opt_nowait

Removing the 'ALL' entirely?

Should there be an "OWNED BY name_list" option also, since that's how we
use 'OWNED' elsewhere? Should the use of "OWNED" elsewhere (eg:
REASSIGN OWNED BY) also support just 'OWNED' to mean the current role
(I'm not entirely sure how much sense that makes, but figured I'd ask).

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> So you're still looking for an 'OWNED' noise word to be added? Also, I
> did add the ability to specify types of objects (it's often that we'll
> have a "INDEXES" tablespace, so this made sense), so how about:

> ALTER TABLESPACE name MOVE OWNED TO name opt_nowait
> ALTER TABLESPACE name MOVE TABLES OWNED TO name opt_nowait
> ALTER TABLESPACE name MOVE INDEXES OWNED TO name opt_nowait
> ALTER TABLESPACE name MOVE MATERIALIZED VIEWS OWNED TO name opt_nowait

> Removing the 'ALL' entirely?

What if you're a superuser and you want to move everybody's objects
(perhaps in preparation for dropping the tablespace)? I think there's
value in both the ALL and OWNED forms.

regards, tom lane

On 20 January 2014 15:46, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> So you're still looking for an 'OWNED' noise word to be added?

To clarify what the command is actually doing.

> Also, I
> did add the ability to specify types of objects (it's often that we'll
> have a "INDEXES" tablespace, so this made sense), so how about:
>
> ALTER TABLESPACE name MOVE OWNED TO name opt_nowait

The ALL seems to have value. "MOVE ALL OWNED TO" sounds better.

> ALTER TABLESPACE name MOVE TABLES OWNED TO name opt_nowait
> ALTER TABLESPACE name MOVE INDEXES OWNED TO name opt_nowait

On those two, I think the docs need to be clearer that we mean that
TABLES means tables, and yes we leave the indexes behind. Or that
INDEXES means "and we leave the tables behind. This is intended to
more easily separate tables and indexes into their own tablespaces."
or similar.

> ALTER TABLESPACE name MOVE MATERIALIZED VIEWS OWNED TO name opt_nowait
>
> Removing the 'ALL' entirely?
>
> Should there be an "OWNED BY name_list" option also, since that's how we
> use 'OWNED' elsewhere? Should the use of "OWNED" elsewhere (eg:
> REASSIGN OWNED BY) also support just 'OWNED' to mean the current role
> (I'm not entirely sure how much sense that makes, but figured I'd ask).

Maybe.

I'm not clamouring for squeezing additional goodies from you, just to
make a small change to avoid later confusion (for ALL users ;-) )

Good feature, thanks for working on it.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> What if you're a superuser and you want to move everybody's objects
> (perhaps in preparation for dropping the tablespace)? I think there's
> value in both the ALL and OWNED forms.

A superuser is considered to 'own' all objects and so 'ALL' and 'OWNED'
above would be the same when issued by a superuser, in the current
implementation.

Looking at DROP OWNED and REASSIGN OWNED, they operate at the more
specific level of "OWNED" == "relowner" rather than if the role is
considered an 'owner' of the object through role membership, as you are
implying above.

As such, I'll rework this to be more in-line with the existing OWNED BY
semantics of REASSIGN OWNED BY and DROP OWNED BY, which means we'd have:

ALTER TABLESPACE name MOVE [ ALL | OWNED [ BY reluser ] ]
[ TABLES | INDEXES | MATERIALIZED VIEWS ] TO name opt_nowait

eg:

ALTER TABLESPACE tblspc1 MOVE ALL TO tblspc2;
ALTER TABLESPACE tblspc1 MOVE OWNED TO tblspc2;
ALTER TABLESPACE tblspc1 MOVE OWNED BY myrole TO tblspc2;
ALTER TABLESPACE tblspc1 MOVE TABLES OWNED BY myrole TO tblspc2;
ALTER TABLESPACE tblspc1 MOVE ALL OWNED BY myrole TO tblspc2;

Thoughts?

Thanks,

Stephen

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> > ALTER TABLESPACE name MOVE OWNED TO name opt_nowait
>
> The ALL seems to have value. "MOVE ALL OWNED TO" sounds better.

I could go either way on this, really.

> > ALTER TABLESPACE name MOVE TABLES OWNED TO name opt_nowait
> > ALTER TABLESPACE name MOVE INDEXES OWNED TO name opt_nowait
>
> On those two, I think the docs need to be clearer that we mean that
> TABLES means tables, and yes we leave the indexes behind. Or that
> INDEXES means "and we leave the tables behind. This is intended to
> more easily separate tables and indexes into their own tablespaces."
> or similar.

Sure, I can certainly improve the documentation on that.

> I'm not clamouring for squeezing additional goodies from you, just to
> make a small change to avoid later confusion (for ALL users ;-) )

:) What are your thoughts on what I just proposed to Tom?

> Good feature, thanks for working on it.

Thanks!

Stephen

On 20 January 2014 17:00, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> What if you're a superuser and you want to move everybody's objects
>> (perhaps in preparation for dropping the tablespace)? I think there's
>> value in both the ALL and OWNED forms.
>
> A superuser is considered to 'own' all objects and so 'ALL' and 'OWNED'
> above would be the same when issued by a superuser, in the current
> implementation.
>
> Looking at DROP OWNED and REASSIGN OWNED, they operate at the more
> specific level of "OWNED" == "relowner" rather than if the role is
> considered an 'owner' of the object through role membership, as you are
> implying above.
>
> As such, I'll rework this to be more in-line with the existing OWNED BY
> semantics of REASSIGN OWNED BY and DROP OWNED BY, which means we'd have:
>
> ALTER TABLESPACE name MOVE [ ALL | OWNED [ BY reluser ] ]
> [ TABLES | INDEXES | MATERIALIZED VIEWS ] TO name opt_nowait
>
> eg:
>
> ALTER TABLESPACE tblspc1 MOVE ALL TO tblspc2;
> ALTER TABLESPACE tblspc1 MOVE OWNED TO tblspc2;
> ALTER TABLESPACE tblspc1 MOVE OWNED BY myrole TO tblspc2;
> ALTER TABLESPACE tblspc1 MOVE TABLES OWNED BY myrole TO tblspc2;
> ALTER TABLESPACE tblspc1 MOVE ALL OWNED BY myrole TO tblspc2;

Sounds great, thanks.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Jan 21, 2014 at 1:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 20 January 2014 17:00, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> What if you're a superuser and you want to move everybody's objects
>>> (perhaps in preparation for dropping the tablespace)? I think there's
>>> value in both the ALL and OWNED forms.
>>
>> A superuser is considered to 'own' all objects and so 'ALL' and 'OWNED'
>> above would be the same when issued by a superuser, in the current
>> implementation.
>>
>> Looking at DROP OWNED and REASSIGN OWNED, they operate at the more
>> specific level of "OWNED" == "relowner" rather than if the role is
>> considered an 'owner' of the object through role membership, as you are
>> implying above.
>>
>> As such, I'll rework this to be more in-line with the existing OWNED BY
>> semantics of REASSIGN OWNED BY and DROP OWNED BY, which means we'd have:
>>
>> ALTER TABLESPACE name MOVE [ ALL | OWNED [ BY reluser ] ]
>> [ TABLES | INDEXES | MATERIALIZED VIEWS ] TO name opt_nowait
>>
>> eg:
>>
>> ALTER TABLESPACE tblspc1 MOVE ALL TO tblspc2;
>> ALTER TABLESPACE tblspc1 MOVE OWNED TO tblspc2;
>> ALTER TABLESPACE tblspc1 MOVE OWNED BY myrole TO tblspc2;
>> ALTER TABLESPACE tblspc1 MOVE TABLES OWNED BY myrole TO tblspc2;
>> ALTER TABLESPACE tblspc1 MOVE ALL OWNED BY myrole TO tblspc2;
>
> Sounds great, thanks.

We should add the tab-completion for ALTER TABLESPACE MOVE?
Attached does that.

Regards,

--
Fujii Masao

On Thu, Jan 30, 2014 at 8:47 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Tue, Jan 21, 2014 at 1:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On 20 January 2014 17:00, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>>> What if you're a superuser and you want to move everybody's objects
>>>> (perhaps in preparation for dropping the tablespace)? I think there's
>>>> value in both the ALL and OWNED forms.
>>>
>>> A superuser is considered to 'own' all objects and so 'ALL' and 'OWNED'
>>> above would be the same when issued by a superuser, in the current
>>> implementation.
>>>
>>> Looking at DROP OWNED and REASSIGN OWNED, they operate at the more
>>> specific level of "OWNED" == "relowner" rather than if the role is
>>> considered an 'owner' of the object through role membership, as you are
>>> implying above.
>>>
>>> As such, I'll rework this to be more in-line with the existing OWNED BY
>>> semantics of REASSIGN OWNED BY and DROP OWNED BY, which means we'd have:
>>>
>>> ALTER TABLESPACE name MOVE [ ALL | OWNED [ BY reluser ] ]
>>> [ TABLES | INDEXES | MATERIALIZED VIEWS ] TO name opt_nowait
>>>
>>> eg:
>>>
>>> ALTER TABLESPACE tblspc1 MOVE ALL TO tblspc2;
>>> ALTER TABLESPACE tblspc1 MOVE OWNED TO tblspc2;
>>> ALTER TABLESPACE tblspc1 MOVE OWNED BY myrole TO tblspc2;
>>> ALTER TABLESPACE tblspc1 MOVE TABLES OWNED BY myrole TO tblspc2;
>>> ALTER TABLESPACE tblspc1 MOVE ALL OWNED BY myrole TO tblspc2;
>>
>> Sounds great, thanks.
>
> We should add the tab-completion for ALTER TABLESPACE MOVE?
> Attached does that.

Committed.

Regards,

--
Fujii Masao

* Fujii Masao (masao(dot)fujii(at)gmail(dot)com) wrote:
> > We should add the tab-completion for ALTER TABLESPACE MOVE?
> > Attached does that.
>
> Committed.

Thanks! I had planned to get to it, but appreciate your handling of it.

Stephen