PG 14新特性之ALTER TABLE DETACH 支持 CONCURRENTLY
Tags： CONCURRENTLYPGPG 14新特性
👉 本文共约180个字，系统预计阅读时间或需1分钟。
简介
担心ALTER TABLE DETACH因忘记设置statement_timeout而长时间锁表吗？ PostgreSQL 14支持CONCURRENTLY了， 完美解决烦恼。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | mydb=# mydb=# create table test_list(id serial, flag text, location text, create_date date) partition by list(flag); /*创建测试表*/ CREATE TABLE mydb=# mydb=# do --利用匿名块快速创建多个list分区子表 mydb-# $$ mydb$# declare base text; sqlstring text; i int; mydb$# begin mydb$# base = 'create table test_list_%s partition of test_list for values in (''%s'')'; mydb$# for i in 0..9 loop mydb$# sqlstring = format(base, 'flag' || i, 'flag' || i); mydb$# --raise notice '%', sqlstring; mydb$# execute sqlstring; mydb$# end loop; mydb$# end mydb$# $$language plpgsql; DO mydb=# mydb=# \d+ test_list Partitioned table 'public.test_list' Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -------------+---------+-----------+----------+---------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('test_list_id_seq'::regclass) | plain | | | flag | text | | | | extended | pglz | | location | text | | | | extended | pglz | | create_date | date | | | | plain | | | Partition key: LIST (flag) Partitions: test_list_flag0 FOR VALUES IN ('flag0'), test_list_flag1 FOR VALUES IN ('flag1'), test_list_flag2 FOR VALUES IN ('flag2'), test_list_flag3 FOR VALUES IN ('flag3'), test_list_flag4 FOR VALUES IN ('flag4'), test_list_flag5 FOR VALUES IN ('flag5'), test_list_flag6 FOR VALUES IN ('flag6'), test_list_flag7 FOR VALUES IN ('flag7'), test_list_flag8 FOR VALUES IN ('flag8'), test_list_flag9 FOR VALUES IN ('flag9') mydb=# alter table test_list detach partition test_list_flag0 concurrently ; ALTER TABLE mydb=# mydb=# alter table test_list detach partition test_list_flag3 concurrently; /*被另一时事物阻塞 然后取消执行*/ ^CCancel request sent ERROR: canceling statement due to user request mydb=# \d+ test_list Partitioned table 'public.test_list' Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -------------+--------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('test_list_id_seq'::regclass) | plain | | | flag | text | | | | extended | | | location | text | | | | extended | | | create_time | timestamp with time zone | | | | plain | | | Partition key: LIST (flag) Partitions: test_list_flag1 FOR VALUES IN ('flag1'), test_list_flag2 FOR VALUES IN ('flag2'), test_list_flag3 FOR VALUES IN ('flag3') (DETACH PENDING), test_list_flag4 FOR VALUES IN ('flag4'), test_list_flag5 FOR VALUES IN ('flag5'), test_list_flag6 FOR VALUES IN ('flag6'), test_list_flag7 FOR VALUES IN ('flag7'), test_list_flag8 FOR VALUES IN ('flag8'), test_list_flag9 FOR VALUES IN ('flag9') |
当CONCURRENTLY被取消后，detach的分区表会显示DETACH PENDING， 需要使用ALTER TABLE … DETACH PARTITION … FINALIZE去完成被取消的DETACH， 才能去detach其它分区。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | mydb=# alter table test_list detach partition test_list_flag3 concurrently; ERROR: partition 'test_list_flag3' already pending detach in partitioned table 'public.test_list' HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the detach operation. mydb=# mydb=# alter table test_list detach partition test_list_flag2 concurrently; ERROR: partition 'test_list_flag3' already pending detach in partitioned table 'public.test_list' HINT: Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the detach operation. mydb=# mydb=# alter table test_list detach partition test_list_flag3 finalize; ALTER TABLE mydb=# mydb=# \d+ test_list Partitioned table 'public.test_list' Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -------------+--------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('test_list_id_seq'::regclass) | plain | | | flag | text | | | | extended | | | location | text | | | | extended | | | create_time | timestamp with time zone | | | | plain | | | Partition key: LIST (flag) Partitions: test_list_flag1 FOR VALUES IN ('flag1'), test_list_flag2 FOR VALUES IN ('flag2'), test_list_flag4 FOR VALUES IN ('flag4'), test_list_flag5 FOR VALUES IN ('flag5'), test_list_flag6 FOR VALUES IN ('flag6'), test_list_flag7 FOR VALUES IN ('flag7'), test_list_flag8 FOR VALUES IN ('flag8'), test_list_flag9 FOR VALUES IN ('flag9') mydb=# |
