Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit71f4c8c

Browse files
committed
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY
Allow a partition be detached from its partitioned table withoutblocking concurrent queries, by running in two transactions and onlyrequiring ShareUpdateExclusive in the partitioned table.Because it runs in two transactions, it cannot be used in a transactionblock. This is the main reason to use dedicated syntax: so that userscan choose to use the original mode if they need it. But also, itdoesn't work when a default partition exists (because an exclusive lockwould still need to be obtained on it, in order to change its partitionconstraint.)In case the second transaction is cancelled or a crash occurs, there'sALTER TABLE .. DETACH PARTITION .. FINALIZE, which executes the finalsteps.The main trick to make this work is the addition of columnpg_inherits.inhdetachpending, initially false; can only be set true inthe first part of this command. Once that is committed, concurrenttransactions that use a PartitionDirectory will include or ignorepartitions so marked: in optimizer they are ignored if the row is markedcommitted for the snapshot; in executor they are always included. As aresult, and because of the way PartitionDirectory caches partitiondescriptors, queries that were planned before the detach will see therows in the detached partition and queries that are planned after thedetach, won't.A CHECK constraint is created that duplicates the partition constraint.This is probably not strictly necessary, and some users will prefer toremove it afterwards, but if the partition is re-attached to apartitioned 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
1 parent650d623 commit71f4c8c

File tree

41 files changed

+2266
-168
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

41 files changed

+2266
-168
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4497,6 +4497,16 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
44974497
when using declarative partitioning.
44984498
</para></entry>
44994499
</row>
4500+
4501+
<row>
4502+
<entry role="catalog_table_entry"><para role="column_definition">
4503+
<structfield>inhdetachpending</structfield> <type>bool</type>
4504+
</para>
4505+
<para>
4506+
<literal>true</literal> for a partition that is in the process of
4507+
being detached; <literal>false</literal> otherwise.
4508+
</para></entry>
4509+
</row>
45004510
</tbody>
45014511
</tgroup>
45024512
</table>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 24 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -36,7 +36,7 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
3636
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
3737
ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
3838
ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
39-
DETACH PARTITION <replaceable class="parameter">partition_name</replaceable>
39+
DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]
4040

4141
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
4242

@@ -954,7 +954,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
954954
</varlistentry>
955955

956956
<varlistentry>
957-
<term><literal>DETACH PARTITION</literal> <replaceable class="parameter">partition_name</replaceable></term>
957+
<term><literal>DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> [ CONCURRENTLY | FINALIZE ]</literal></term>
958+
958959
<listitem>
959960
<para>
960961
This form detaches the specified partition of the target table. The detached
@@ -965,6 +966,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
965966
<literal>SHARE</literal> lock is obtained on any tables that reference
966967
this partitioned table in foreign key constraints.
967968
</para>
969+
<para>
970+
If <literal>CONCURRENTLY</literal> is specified, it runs using a reduced
971+
lock level to avoid blocking other sessions that might be accessing the
972+
partitioned table. In this mode, two transactions are used internally.
973+
During the first transaction, a <literal>SHARE UPDATE EXCLUSIVE</literal>
974+
lock is taken on both parent table and partition, and the partition is
975+
marked as undergoing detach; at that point, the transaction is committed
976+
and all other transactions using the partitioned table are waited for.
977+
Once all those transactions have completed, the second transaction
978+
acquires <literal>SHARE UPDATE EXCLUSIVE</literal> on the partitioned
979+
table and <literal>ACCESS EXCLUSIVE</literal> on the partition,
980+
and the detach process completes. A <literal>CHECK</literal> constraint
981+
that duplicates the partition constraint is added to the partition.
982+
<literal>CONCURRENTLY</literal> cannot be run in a transaction block and
983+
is not allowed if the partitioned table contains a default partition.
984+
</para>
985+
<para>
986+
If <literal>FINALIZE</literal> is specified, a previous
987+
<literal>DETACH CONCURRENTLY</literal> invocation that was cancelled or
988+
interrupted is completed.
989+
</para>
968990
</listitem>
969991
</varlistentry>
970992

‎src/backend/catalog/heap.c

Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1923,7 +1923,12 @@ heap_drop_with_catalog(Oid relid)
19231923
elog(ERROR,"cache lookup failed for relation %u",relid);
19241924
if (((Form_pg_class)GETSTRUCT(tuple))->relispartition)
19251925
{
1926-
parentOid=get_partition_parent(relid);
1926+
/*
1927+
* We have to lock the parent if the partition is being detached,
1928+
* because it's possible that some query still has a partition
1929+
* descriptor that includes this partition.
1930+
*/
1931+
parentOid=get_partition_parent(relid, true);
19271932
LockRelationOid(parentOid,AccessExclusiveLock);
19281933

19291934
/*
@@ -2559,10 +2564,12 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
25592564
* Returns a list of CookedConstraint nodes that shows the cooked form of
25602565
* the default and constraint expressions added to the relation.
25612566
*
2562-
* NB: caller should have opened rel with AccessExclusiveLock, and should
2563-
* hold that lock till end of transaction. Also, we assume the caller has
2564-
* done a CommandCounterIncrement if necessary to make the relation's catalog
2565-
* tuples visible.
2567+
* NB: caller should have opened rel with some self-conflicting lock mode,
2568+
* and should hold that lock till end of transaction; for normal cases that'll
2569+
* be AccessExclusiveLock, but if caller knows that the constraint is already
2570+
* enforced by some other means, it can be ShareUpdateExclusiveLock. Also, we
2571+
* assume the caller has done a CommandCounterIncrement if necessary to make
2572+
* the relation's catalog tuples visible.
25662573
*/
25672574
List*
25682575
AddRelationNewConstraints(Relationrel,
@@ -3831,7 +3838,8 @@ StorePartitionBound(Relation rel, Relation parent, PartitionBoundSpec *bound)
38313838
* relcache entry for that partition every time a partition is added or
38323839
* removed.
38333840
*/
3834-
defaultPartOid=get_default_oid_from_partdesc(RelationGetPartitionDesc(parent));
3841+
defaultPartOid=
3842+
get_default_oid_from_partdesc(RelationGetPartitionDesc(parent, false));
38353843
if (OidIsValid(defaultPartOid))
38363844
CacheInvalidateRelcacheByRelid(defaultPartOid);
38373845

‎src/backend/catalog/index.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1837,7 +1837,7 @@ index_concurrently_swap(Oid newIndexId, Oid oldIndexId, const char *oldName)
18371837
List*ancestors=get_partition_ancestors(oldIndexId);
18381838
OidparentIndexRelid=linitial_oid(ancestors);
18391839

1840-
DeleteInheritsTuple(oldIndexId,parentIndexRelid);
1840+
DeleteInheritsTuple(oldIndexId,parentIndexRelid, false,NULL);
18411841
StoreSingleInheritance(newIndexId,parentIndexRelid,1);
18421842

18431843
list_free(ancestors);
@@ -2487,7 +2487,7 @@ index_drop(Oid indexId, bool concurrent, bool concurrent_lock_mode)
24872487
/*
24882488
* fix INHERITS relation
24892489
*/
2490-
DeleteInheritsTuple(indexId,InvalidOid);
2490+
DeleteInheritsTuple(indexId,InvalidOid, false,NULL);
24912491

24922492
/*
24932493
* We are presently too lazy to attempt to compute the new correct value

‎src/backend/catalog/partition.c

Lines changed: 30 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -32,7 +32,8 @@
3232
#include"utils/rel.h"
3333
#include"utils/syscache.h"
3434

35-
staticOidget_partition_parent_worker(RelationinhRel,Oidrelid);
35+
staticOidget_partition_parent_worker(RelationinhRel,Oidrelid,
36+
bool*detach_pending);
3637
staticvoidget_partition_ancestors_worker(RelationinhRel,Oidrelid,
3738
List**ancestors);
3839

@@ -42,23 +43,32 @@ static void get_partition_ancestors_worker(Relation inhRel, Oid relid,
4243
*
4344
* Returns inheritance parent of a partition by scanning pg_inherits
4445
*
46+
* If the partition is in the process of being detached, an error is thrown,
47+
* unless even_if_detached is passed as true.
48+
*
4549
* Note: Because this function assumes that the relation whose OID is passed
4650
* as an argument will have precisely one parent, it should only be called
4751
* when it is known that the relation is a partition.
4852
*/
4953
Oid
50-
get_partition_parent(Oidrelid)
54+
get_partition_parent(Oidrelid,booleven_if_detached)
5155
{
5256
RelationcatalogRelation;
5357
Oidresult;
58+
booldetach_pending;
5459

5560
catalogRelation=table_open(InheritsRelationId,AccessShareLock);
5661

57-
result=get_partition_parent_worker(catalogRelation,relid);
62+
result=get_partition_parent_worker(catalogRelation,relid,
63+
&detach_pending);
5864

5965
if (!OidIsValid(result))
6066
elog(ERROR,"could not find tuple for parent of relation %u",relid);
6167

68+
if (detach_pending&& !even_if_detached)
69+
elog(ERROR,"relation %u has no parent because it's being detached",
70+
relid);
71+
6272
table_close(catalogRelation,AccessShareLock);
6373

6474
returnresult;
@@ -68,15 +78,20 @@ get_partition_parent(Oid relid)
6878
* get_partition_parent_worker
6979
*Scan the pg_inherits relation to return the OID of the parent of the
7080
*given relation
81+
*
82+
* If the partition is being detached, *detach_pending is set true (but the
83+
* original parent is still returned.)
7184
*/
7285
staticOid
73-
get_partition_parent_worker(RelationinhRel,Oidrelid)
86+
get_partition_parent_worker(RelationinhRel,Oidrelid,bool*detach_pending)
7487
{
7588
SysScanDescscan;
7689
ScanKeyDatakey[2];
7790
Oidresult=InvalidOid;
7891
HeapTupletuple;
7992

93+
*detach_pending= false;
94+
8095
ScanKeyInit(&key[0],
8196
Anum_pg_inherits_inhrelid,
8297
BTEqualStrategyNumber,F_OIDEQ,
@@ -93,6 +108,9 @@ get_partition_parent_worker(Relation inhRel, Oid relid)
93108
{
94109
Form_pg_inheritsform= (Form_pg_inherits)GETSTRUCT(tuple);
95110

111+
/* Let caller know of partition being detached */
112+
if (form->inhdetachpending)
113+
*detach_pending= true;
96114
result=form->inhparent;
97115
}
98116

@@ -134,10 +152,14 @@ static void
134152
get_partition_ancestors_worker(RelationinhRel,Oidrelid,List**ancestors)
135153
{
136154
OidparentOid;
155+
booldetach_pending;
137156

138-
/* Recursion ends at the topmost level, ie., when there's no parent */
139-
parentOid=get_partition_parent_worker(inhRel,relid);
140-
if (parentOid==InvalidOid)
157+
/*
158+
* Recursion ends at the topmost level, ie., when there's no parent; also
159+
* when the partition is being detached.
160+
*/
161+
parentOid=get_partition_parent_worker(inhRel,relid,&detach_pending);
162+
if (parentOid==InvalidOid||detach_pending)
141163
return;
142164

143165
*ancestors=lappend_oid(*ancestors,parentOid);
@@ -170,7 +192,7 @@ index_get_partition(Relation partition, Oid indexId)
170192
ReleaseSysCache(tup);
171193
if (!ispartition)
172194
continue;
173-
if (get_partition_parent(partIdx)==indexId)
195+
if (get_partition_parent(partIdx, false)==indexId)
174196
{
175197
list_free(idxlist);
176198
returnpartIdx;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp