Alter column position/ja

From PostgreSQL wiki
Jump to navigationJump to search

postgresqlの初心者の多くからよく、テーブル内の列の位置を変更することはサポートされているかどうか聞かれます。 今のところサポートされていません。 列の位置を変更したいのであれば、テーブルを再作成するか、新しい列を追加してデータを移動するかのいずれかを行う必要があります。 列の位置の順序の変更を許すという考えをpostgresql開発者は対象としていません。 他にも作業に手を付けなければならないものが多くあります。 本書の残りの部分で回避方法を説明するつもりです。 またこの機能の実装を考えている方が扱わなければいけない問題についても触れています。

postgresで列位置の変更できたら便利になる理由が大きく2つあります。

  1. テーブルの先頭にサイズが固定の列を置くことで物理的なレイアウトが最適化できること
  2. 視覚効果が高い順序の結果セットを格納、または、テーブルに対する同様な関数に基づいて列をグループ化することにより、 列の順序付けがテーブルへの作業を簡単にすることができること。

現在postgresでは列の順序をpg_attributeテーブルのattnum列に基づいて定義しています。 列の順序を変更する方法は、テーブルを再作成する、または、列を追加しデータを移す作業を最終的なレイアウトになるまで繰り返すしか

ありません。

列変更の回避方法

テーブル再作成

最初の例ではfilm_actorというテーブルを想定します。 ここには、actor_id、film_id、last_updateという3つの列があります。 ここで多くの開発者がどれがフィルムのIDで、どれが俳優のIDかをよく困惑していることに気付きました。 これを解消するために列の順序を変更したいと考えました。 

pagila=# \d film_actor
                     Table "public.film_actor"
   Column    |            Type             |       Modifiers        
-------------+-----------------------------+------------------------
 actor_id    | smallint                    | not null
 film_id     | smallint                    | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)
    "idx_fk_film_id" btree (film_id)
Foreign-key constraints:
    "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON film_actor FOR EACH ROW EXECUTE PROCEDURE last_updated()


このためには、新しいテーブルを目的とする列の順序で作成し、そのデータを更新した後で、古いテーブルを削除して何らかの依存関係を

解決します。 

pagila=# begin; 
BEGIN
pagila=# create table film_actor_new ( 
pagila-# film_id smallint references film, 
pagila-# actor_id smallint references actor, 
pagila-# last_update timestamp not null default now(), 
pagila-# primary key (actor_id,film_id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit 
index "film_actor_new_pkey" for table "film_actor_new"
CREATE TABLE
pagila=# insert into film_actor_new select film_id, actor_id, last_update from film_actor; 
INSERT 0 5462
pagila=# drop table film_actor cascade; 
NOTICE:  drop cascades to rule _RETURN on view nicer_but_slower_film_list
NOTICE:  drop cascades to view nicer_but_slower_film_list
NOTICE:  drop cascades to rule _RETURN on view film_list
NOTICE:  drop cascades to view film_list
NOTICE:  drop cascades to rule _RETURN on view actor_info
NOTICE:  drop cascades to view actor_info
DROP TABLE
pagila=# alter table film_actor_new RENAME to film_actor;
ALTER TABLE
pagila=# \d film_actor
                     Table "public.film_actor"
   Column    |            Type             |       Modifiers        
-------------+-----------------------------+------------------------
 film_id     | smallint                    | not null
 actor_id    | smallint                    | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "film_actor_new_pkey" PRIMARY KEY, btree (actor_id, film_id)
Foreign-key constraints:
    "film_actor_new_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id)
    "film_actor_new_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id)


続けることがあるとすると、参照しているビューがあれば新しい版のテーブルを参照するように作成しなおす必要があります。 また、新しいテーブルでなくなってしまったインデックスがあれば作成しなおす必要もあることを忘れないようにしてください。 最終的なスキーマが以前のスキーマに一致することが確実になるように、すべてのテーブル、インデックスなどの名前を変更してください

。 この作業を1つのトランザクション内で行うことができますが、多くの依存関係の問題が発生する可能性がありますので、この作業を完全に

行うことが大切です。

列を追加しデータを移動

テーブルの再作成があまりに多くの問題を起こすことが時々ありますので、代わりに列を追加しデータを移動する方法を使用することがで

きます。 以下の例では、ID、名(first_name)、姓(last_name)、最終更新日(last_update)という列を持つactorテーブルを想定します。 このテーブルにmiddle_name列を追加しようとしています。 またこの列がfirst_nameとlast_nameの間に追加したいとも考えています。 


pagila=# \d actor
                                         Table "public.actor"
   Column    |            Type             |                        Modifiers                         
-------------+-----------------------------+----------------------------------------------------------
 actor_id    | integer                     | not null default nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45)       | not null
 last_name   | character varying(45)       | not null
 last_update | timestamp without time zone | not null default now()
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
    "idx_actor_last_name" btree (last_name)
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()

これを行うには、まずmiddle_name列を追加します（これは最後に追加されます）。 そして新しいlast_nameとなるべき列とlast_updateとなるべき列を追加し、それぞれ対応した列で更新します。 その後古い列を削除します。 

pagila=# begin;
BEGIN
pagila=# alter table actor add column middle_name text, add column lname text, add column lupdate text;
ALTER TABLE
pagila=# update actor set lname=last_name, lupdate=last_update;
UPDATE 200
pagila=# alter table actor drop column last_name cascade, drop column last_update cascade; 
NOTICE:  drop cascades to rule _RETURN on view nicer_but_slower_film_list
NOTICE:  drop cascades to view nicer_but_slower_film_list
NOTICE:  drop cascades to rule _RETURN on view film_list
NOTICE:  drop cascades to view film_list
NOTICE:  drop cascades to rule _RETURN on view actor_info
NOTICE:  drop cascades to view actor_info
ALTER TABLE
pagila=# alter table actor rename column lname to last_name;
ALTER TABLE
pagila=# alter table actor  rename column lupdate to last_update;
ALTER TABLE
pagila=# \d actor
                                      Table "public.actor"
   Column    |         Type          |                        Modifiers                         
-------------+-----------------------+----------------------------------------------------------
 actor_id    | integer               | not null default nextval('actor_actor_id_seq'::regclass)
 first_name  | character varying(45) | not null
 middle_name | text                  | 
 last_name   | text                  | 
 last_update | text                  | 
Indexes:
    "actor_pkey" PRIMARY KEY, btree (actor_id)
Triggers:
    last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()


繰り返しになりますが、この変更を完了する前に、ビューの再作成やインデックス、トリガ、その他依存関係があるものを処理する必要が

あります。 また、この更新はテーブル内のすべての行を書き換えることになることは重要です。 これは後でメンテナンス作業（多くはテーブルに対するcluster/reindexなど）が必要になります。

ビューを使用した違いの隠ぺい

完全を期すために、他にもテーブルの順序を変更する方法を紹介します。 ビューを使用して変更を隠すという方法です。 基本的な考えは、新しい列を追加しテーブルの名前を変更します。 そして目的の列の順序を持つ、古いテーブルと同じ名前のビューを作成し、その後、列の更新・挿入・削除を扱うルールを追加します。 これは論理的な列の順序変更を行いますが、もし物理的なレイアウトの最適化のために順序変更を行いたいのであれば上記の方法のいずれ

かを行わなければなりません。 このためここでは例を示していませんが、wikiですのでやる気があれば自由に追加してください。

postgresにalter column構文を追加するには

上の方法は多くの問題がありますので、列の順序変更を許すことができるようpostgresに機能を追加したいと言われることがよくありました。 この裏面として、postgresが物理的に最適なレイアウトになるように、指定された論理的な順序を無視して、列の順序を自動的に決定するようにしたいということもあります。 これを実装する際の現在の問題は、現在のpostgresがテーブルの論理的な位置と物理的な位置を識別するために同一の識別子を使用していることに起因します。 これを解決するための現在の熱い計画は、システムを3つの識別子、つまり列の永続識別子と論理的な識別子、物理的な識別子、を参照するように変更しようというものです。 これにより論理的なレベルの列順序（例えばselect *）では特別に論理的な番号で参照するように扱う必要がある場所、ディスクシステムと相互作業するところでは物理的な番号でアクセスできるようになる場所、列の永続IDを使用するだけで済む場所ができるようになります。

他にもどのようにユーザがこの新しい機能を扱うかについて構文を決定しなければなりません。 よくある実装ではよくBEFORE/AFTERキーワードを既存の列名の前に付ける方法を使用します。

Retrieved from "https://wiki.postgresql.org/index.php?title=Alter_column_position/ja&oldid=9324"