Postgresql テーブル定義変更

## はじめに
PostgreSQLでは基本的にDDL実行時にTable Lockがかかり、一時的に実行対象のテーブルに対するDML実行が停止します。
特にIndex追加、Unique Constraint追加、NOT NULL条件追加作業などの作業は、処理に時間がかかりDML実行停止が長くなります。
その場合、DBが使えなくなりますが、WorkaroundでDML処理は可能にな方法を紹介します。


## DDL作業Workaround
今回紹介するWorkaroundによって、DML実行が出来なくなるロックを回避する事が出来ます。
説明の中で登場するロックの種類は下記になります。
SHARE~SHARE ROW EXCLUSIVE
更新系のDMLが実行できません。SELECT は実行可能です。
・INSERT
・UPDATE
・DELETE

ACCESS EXCLUSIVE
すべてのDMLが実行できません。
・SELECT
・INSERT
・UPDATE
・DELETE

ShareUpdateExclusiveLock
Select及びDML処理が可能、DDLが実行できません。

## 1.Index作成時
Index作成する際にはShareLockが発生します。
このLockが発生する場合、selectのみ可能ですが、下記のWorkaroundによってLockが
ShareLock→ShareUpdateExclusiveLockに変更され、dml処理も可能になります。

```
CREATE UNIQUE INDEX CONCURRENTLY index_name ON table_name(coulumn_name);


//Index作成際に発生するLock
=# CREATE INDEX  idx_test3 ON pgbench_accounts(aid);
=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x      mode       x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/254              x 18419 x AccessShareLock x t
 pg_index         x relation x <NULL> x 7/254              x 18419 x AccessShareLock x t
 pg_namespace     x relation x <NULL> x 7/254              x 18419 x AccessShareLock x t
 pgbench_accounts x relation x <NULL> x 8/615              x 20464 x ShareLock       x t
 pgbench_accounts x relation x <NULL> x 9/128              x 20465 x ShareLock       x t
 pgbench_accounts x relation x <NULL> x 6/25308            x 19265 x ShareLock       x t

//CONCURRENTLY Optionを使用する場合、発生するLock
=# CREATE INDEX CONCURRENTLY  idx_test2 ON pgbench_accounts(aid);
=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x           mode           x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/253              x 18419 x AccessShareLock          x t
 pg_index         x relation x <NULL> x 7/253              x 18419 x AccessShareLock          x t
 pg_namespace     x relation x <NULL> x 7/253              x 18419 x AccessShareLock          x t
 pgbench_accounts x relation x <NULL> x 8/273              x 19570 x ShareUpdateExclusiveLock x t
 pgbench_accounts x relation x <NULL> x 9/122              x 19571 x ShareUpdateExclusiveLock x t
 pgbench_accounts x relation x <NULL> x 6/25305            x 19265 x ShareUpdateExclusiveLock x t
(6 rows)

```

## 2.既存ColumnにNot Null条件追加時
元々Not Null条件追加する際は、AccessExclusiveLockが発生してTableのすべての処理ができないですが、
下記のWorkaroundによって発生するLockがshareUpdateexclusiveLockに変更されます。
```
-- 1. column IS NOT NULLを検査するCHECK制約を、NOT VALIDオプションつきで足す
--    この時点では既存の行の正当性はチェックされない
ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_name IS NOT NULL) NOT VALID;
 
-- 2. CHECK制約を既存の行にも適用する
--    この時点で、shareUpdateexclusiveLockが発生しますが、selectとdml作業は問題なくできます。
ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;
 
-- 3. NOT NULL制約を足す (意味的には先ほど足したCHECK制約と同じ)
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
 
-- 4. CHECK制約は不要になるので消す
ALTER TABLE table_name DROP CONSTRAINT constraint_name;


//既存の方法AccessExclusiveLockが発生
=# ALTER TABLE pgbench_accounts alter column bid set NOT NULL;
=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x        mode         x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/255              x 18419 x AccessShareLock     x t
 pg_index         x relation x <NULL> x 7/255              x 18419 x AccessShareLock     x t
 pg_namespace     x relation x <NULL> x 7/255              x 18419 x AccessShareLock     x t
 pgbench_accounts x relation x <NULL> x 6/25309            x 19265 x AccessExclusiveLock x t
(4 rows)

//Workaround適用例
=# ALTER TABLE pgbench_accounts ADD CONSTRAINT con_notnull CHECK (bid IS NOT NULL) NOT VALID;
=# ALTER TABLE pgbench_accounts VALIDATE CONSTRAINT con_notnull;
=# ALTER TABLE pgbench_accounts ALTER COLUMN bid SET NOT NULL;
=# ALTER TABLE pgbench_accounts DROP CONSTRAINT con_notnull;

=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x           mode           x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/256              x 18419 x AccessShareLock          x t
 pg_index         x relation x <NULL> x 7/256              x 18419 x AccessShareLock          x t
 pg_namespace     x relation x <NULL> x 7/256              x 18419 x AccessShareLock          x t
 pgbench_accounts x relation x <NULL> x 6/25312            x 19265 x ShareUpdateExclusiveLock x t
(4 rows)

```

## 3.Constraint追加(Foreign Key)時
Foreign Keyを追加する際は
ShareRowExclusiveLockが発生してselectのみ可能ですが、WorkaroundによってShareUpdateExclusiveLockに変更され、dml処理も可能になります。

```
-- 1. FOREIGN KEYをNOT VALIDオプションつきで足す
ALTER TABLE table_name ADD CONSTRAINT FK_name FOREIGN KEY(column_name) REFERENCES table_name(column_name) NOT VALID;


 
-- 2. FOREIGN KEYを既存の行にも適用する
--    この時点で、ShareUpdateExclusiveLockが発生しますが、selectとdml作業は問題なくできます。
ALTER TABLE table_name VALIDATE CONSTRAINT constraint_name;

//既存の方法で作業する場合、ShareRowExclusiveLock発生
ALTER TABLE pgbench_accounts ADD CONSTRAINT user_id_fkey FOREIGN KEY (bid) REFERENCES pgbench_accounts (aid);
=# SELECT  t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted
FROM pg_locks l,
 pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid   x         mode          x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 5/4809061          x 123768 x AccessShareLock       x t
 pg_index         x relation x <NULL> x 5/4809061          x 123768 x AccessShareLock       x t
 pg_namespace     x relation x <NULL> x 5/4809061          x 123768 x AccessShareLock       x t
 pgbench_accounts x relation x <NULL> x 11/313501          x 123118 x AccessShareLock       x t
 pgbench_accounts x relation x <NULL> x 11/313501          x 123118 x RowShareLock          x t
 pgbench_accounts x relation x <NULL> x 11/313501          x 123118 x ShareRowExclusiveLock x t
(6 rows)

//Workaround適用例
=# ALTER TABLE pgbench_accounts ADD CONSTRAINT user_id_fkey FOREIGN KEY (bid) REFERENCES pgbench_accounts (aid)  NOT VALID;
=# ALTER TABLE pgbench_accounts VALIDATE CONSTRAINT user_id_fkey;
=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x           mode           x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/258              x 18419 x AccessShareLock          x t
 pg_index         x relation x <NULL> x 7/258              x 18419 x AccessShareLock          x t
 pg_namespace     x relation x <NULL> x 7/258              x 18419 x AccessShareLock          x t
 pgbench_accounts x relation x <NULL> x 6/25317            x 19265 x AccessShareLock          x t
 pgbench_accounts x relation x <NULL> x 6/25317            x 19265 x RowShareLock             x t
 pgbench_accounts x relation x <NULL> x 6/25317            x 19265 x ShareUpdateExclusiveLock x t
(6 rows)


```
## 4.既存のColumnにunique constraint追加時
既存の方法でuniqueconstraint追加すると、AccessExclusiveLockが発生してすべての処理ができなくなりますが
Workaround適用によってShareUpdateExclusiveLockに変更されます。

```
-- 1. Unique IndexをConcurrently Optionで作成します。それによってselect及びdml作業は許容されます。
CREATE UNIQUE INDEX CONCURRENTLY index_name ON table_name(column_name);
 
-- 2. Unique Using Index Optionを利用して、Unique Constraintを追加します。
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE USING INDEX index_name;

//既存の方法適用例
=# ALTER TABLE pgbench_accounts ADD CONSTRAINT uni_con UNIQUE (aid);

=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x        mode         x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/259              x 18419 x AccessShareLock     x t
 pg_index         x relation x <NULL> x 7/259              x 18419 x AccessShareLock     x t
 pg_namespace     x relation x <NULL> x 7/259              x 18419 x AccessShareLock     x t
 pgbench_accounts x relation x <NULL> x 6/25320            x 19265 x AccessExclusiveLock x t

//Workaround適用例
=# CREATE UNIQUE INDEX CONCURRENTLY uni_ind1 ON pgbench_accounts(aid);
=# ALTER TABLE pgbench_accounts ADD CONSTRAINT uni_con UNIQUE (aid);

=# SELECT  t.relname,
         l.locktype,
         page,
         virtualtransaction,
         pid,
         mode,
         granted
FROM pg_locks l,
pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;
     relname      x locktype x  page  x virtualtransaction x  pid  x           mode           x granted
qqqqqqqqqqqqqqqqqqnqqqqqqqqqqnqqqqqqqqnqqqqqqqqqqqqqqqqqqqqnqqqqqqqnqqqqqqqqqqqqqqqqqqqqqqqqqqnqqqqqqqqq
 pg_class         x relation x <NULL> x 7/267              x 18419 x AccessShareLock          x t
 pg_index         x relation x <NULL> x 7/267              x 18419 x AccessShareLock          x t
 pg_namespace     x relation x <NULL> x 7/267              x 18419 x AccessShareLock          x t
 pgbench_accounts x relation x <NULL> x 8/8758             x 40298 x ShareUpdateExclusiveLock x t
 pgbench_accounts x relation x <NULL> x 9/178              x 40297 x ShareUpdateExclusiveLock x t
 pgbench_accounts x relation x <NULL> x 6/25322            x 19265 x ShareUpdateExclusiveLock x t
 

```

## まとめ
テーブル変更時に並行でDML処理も可能な方法を紹介しました。
Postgresqlのテーブル変更は色々なロックが発生します。そのため、DBの処理が停止される場合がありますので、
慎重に検討してから行う必要があります。