Re: ALTER TABLESPACE ... MOVE ALL TO ...

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