digoal
2021-03-26
PostgreSQL , rewrite table , 2阶段 routine , 无锁 ddl , 无锁 整理数据 , 无锁 rewrite table
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY 支持ShareUpdateExclusive 锁, 不堵塞查询.
这个操作基于2阶段routines管理机制, 如果第二阶段失败, 需要使用ALTER TABLE .. DETACH PARTITION .. FINALIZE来结束第二阶段.
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY
author Alvaro Herrera <alvherre@alvh.no-ip.org>
Thu, 25 Mar 2021 21:00:28 +0000 (18:00 -0300)
committer Alvaro Herrera <alvherre@alvh.no-ip.org>
Thu, 25 Mar 2021 21:00:28 +0000 (18:00 -0300)
commit 71f4c8c6f74ba021e55d35b1128d22fb8c6e1629
tree c53d5e70ef2c8ec1723c9fb62fc8174ba6381e29 tree
parent 650d623530c884c087c565f1d3b8cd76f8fe2b95 commit | diff
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY
Allow a partition be detached from its partitioned table without
blocking concurrent queries, by running in two transactions and only
requiring ShareUpdateExclusive in the partitioned table.
Because it runs in two transactions, it cannot be used in a transaction
block. This is the main reason to use dedicated syntax: so that users
can choose to use the original mode if they need it. But also, it
doesn't work when a default partition exists (because an exclusive lock
would still need to be obtained on it, in order to change its partition
constraint.)
In case the second transaction is cancelled or a crash occurs, there's
ALTER TABLE .. DETACH PARTITION .. FINALIZE, which executes the final
steps.
The main trick to make this work is the addition of column
pg_inherits.inhdetachpending, initially false; can only be set true in
the first part of this command. Once that is committed, concurrent
transactions that use a PartitionDirectory will include or ignore
partitions so marked: in optimizer they are ignored if the row is marked
committed for the snapshot; in executor they are always included. As a
result, and because of the way PartitionDirectory caches partition
descriptors, queries that were planned before the detach will see the
rows in the detached partition and queries that are planned after the
detach, won't.
A CHECK constraint is created that duplicates the partition constraint.
This is probably not strictly necessary, and some users will prefer to
remove it afterwards, but if the partition is re-attached to a
partitioned table, the constraint needn't be rechecked.
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Reviewed-by: Amit Langote <amitlangote09@gmail.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Discussion: https://postgr.es/m/20200803234854.GA24158@alvherre.pgsql
Document lock obtained during partition detach
author Alvaro Herrera <alvherre@alvh.no-ip.org>
Thu, 25 Mar 2021 19:30:22 +0000 (16:30 -0300)
committer Alvaro Herrera <alvherre@alvh.no-ip.org>
Thu, 25 Mar 2021 19:30:22 +0000 (16:30 -0300)
commit 650d623530c884c087c565f1d3b8cd76f8fe2b95
tree 6f2ef7103d21b351d5b24b8b766a02dcca866b84 tree
parent cc121d5596964f8aac93607e6f14607184558b16 commit | diff
Document lock obtained during partition detach
On partition detach, we acquire a SHARE lock on all tables that
reference the partitioned table that we're detaching a partition from,
but failed to document this fact. My oversight in commit f56f8f8da6af.
Repair. Backpatch to 12.
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Discussion: https://postgr.es/m/20210325180244.GA12738@alvherre.pgsql
doc/src/sgml/ref/alter_table.sgml diff | blob | blame | history
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
