Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@arisiel00inDB-plus

Postgresql テーブル定義変更

Last updated at Posted at 2024-03-11

はじめに

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の処理が停止される場合がありますので、
慎重に検討してから行う必要があります。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?