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

Commit555ee77

Browse files
committed
Handle INSERT .. ON CONFLICT with partitioned tables
Commiteb7ed3f enabled unique constraints on partitioned tables,but one thing that was not working properly is INSERT/ON CONFLICT.This commit introduces a new node keeps state related to the ON CONFLICTclause per partition, and fills it when that partition is about to beused for tuple routing.Author: Amit Langote, Álvaro HerreraReviewed-by: Etsuro Fujita, Pavan DeolaseeDiscussion:https://postgr.es/m/20180228004602.cwdyralmg5ejdqkq@alvherre.pgsql
1 parent1b89c21 commit555ee77

File tree

14 files changed

+638
-84
lines changed

14 files changed

+638
-84
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 0 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -3322,21 +3322,6 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
33223322
</para>
33233323
</listitem>
33243324

3325-
<listitem>
3326-
<para>
3327-
Using the <literal>ON CONFLICT</literal> clause with partitioned tables
3328-
will cause an error if the conflict target is specified (see
3329-
<xref linkend="sql-on-conflict" /> for more details on how the clause
3330-
works). Therefore, it is not possible to specify
3331-
<literal>DO UPDATE</literal> as the alternative action, because
3332-
specifying the conflict target is mandatory in that case. On the other
3333-
hand, specifying <literal>DO NOTHING</literal> as the alternative action
3334-
works fine provided the conflict target is not specified. In that case,
3335-
unique constraints (or exclusion constraints) of the individual leaf
3336-
partitions are considered.
3337-
</para>
3338-
</listitem>
3339-
33403325
<listitem>
33413326
<para>
33423327
When an <command>UPDATE</command> causes a row to move from one

‎doc/src/sgml/ref/insert.sgml

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -518,6 +518,14 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
518518
not duplicate each other in terms of attributes constrained by an
519519
arbiter index or constraint.
520520
</para>
521+
522+
<para>
523+
Note that it is currently not supported for the
524+
<literal>ON CONFLICT DO UPDATE</literal> clause of an
525+
<command>INSERT</command> applied to a partitioned table to update the
526+
partition key of a conflicting row such that it requires the row be moved
527+
to a new partition.
528+
</para>
521529
<tip>
522530
<para>
523531
It is often preferable to use unique index inference rather than

‎src/backend/catalog/partition.c

Lines changed: 77 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,10 @@ typedef struct PartitionRangeBound
138138
boollower;/* this is the lower (vs upper) bound */
139139
}PartitionRangeBound;
140140

141+
142+
staticOidget_partition_parent_worker(RelationinhRel,Oidrelid);
143+
staticvoidget_partition_ancestors_worker(RelationinhRel,Oidrelid,
144+
List**ancestors);
141145
staticint32qsort_partition_hbound_cmp(constvoid*a,constvoid*b);
142146
staticint32qsort_partition_list_value_cmp(constvoid*a,constvoid*b,
143147
void*arg);
@@ -1377,6 +1381,7 @@ check_default_allows_bound(Relation parent, Relation default_rel,
13771381

13781382
/*
13791383
* get_partition_parent
1384+
*Obtain direct parent of given relation
13801385
*
13811386
* Returns inheritance parent of a partition by scanning pg_inherits
13821387
*
@@ -1387,15 +1392,34 @@ check_default_allows_bound(Relation parent, Relation default_rel,
13871392
Oid
13881393
get_partition_parent(Oidrelid)
13891394
{
1390-
Form_pg_inheritsform;
13911395
RelationcatalogRelation;
1392-
SysScanDescscan;
1393-
ScanKeyDatakey[2];
1394-
HeapTupletuple;
13951396
Oidresult;
13961397

13971398
catalogRelation=heap_open(InheritsRelationId,AccessShareLock);
13981399

1400+
result=get_partition_parent_worker(catalogRelation,relid);
1401+
1402+
if (!OidIsValid(result))
1403+
elog(ERROR,"could not find tuple for parent of relation %u",relid);
1404+
1405+
heap_close(catalogRelation,AccessShareLock);
1406+
1407+
returnresult;
1408+
}
1409+
1410+
/*
1411+
* get_partition_parent_worker
1412+
*Scan the pg_inherits relation to return the OID of the parent of the
1413+
*given relation
1414+
*/
1415+
staticOid
1416+
get_partition_parent_worker(RelationinhRel,Oidrelid)
1417+
{
1418+
SysScanDescscan;
1419+
ScanKeyDatakey[2];
1420+
Oidresult=InvalidOid;
1421+
HeapTupletuple;
1422+
13991423
ScanKeyInit(&key[0],
14001424
Anum_pg_inherits_inhrelid,
14011425
BTEqualStrategyNumber,F_OIDEQ,
@@ -1405,22 +1429,64 @@ get_partition_parent(Oid relid)
14051429
BTEqualStrategyNumber,F_INT4EQ,
14061430
Int32GetDatum(1));
14071431

1408-
scan=systable_beginscan(catalogRelation,InheritsRelidSeqnoIndexId, true,
1432+
scan=systable_beginscan(inhRel,InheritsRelidSeqnoIndexId, true,
14091433
NULL,2,key);
1410-
14111434
tuple=systable_getnext(scan);
1412-
if (!HeapTupleIsValid(tuple))
1413-
elog(ERROR,"could not find tuple for parent of relation %u",relid);
1435+
if (HeapTupleIsValid(tuple))
1436+
{
1437+
Form_pg_inheritsform= (Form_pg_inherits)GETSTRUCT(tuple);
14141438

1415-
form=(Form_pg_inherits)GETSTRUCT(tuple);
1416-
result=form->inhparent;
1439+
result=form->inhparent;
1440+
}
14171441

14181442
systable_endscan(scan);
1419-
heap_close(catalogRelation,AccessShareLock);
14201443

14211444
returnresult;
14221445
}
14231446

1447+
/*
1448+
* get_partition_ancestors
1449+
*Obtain ancestors of given relation
1450+
*
1451+
* Returns a list of ancestors of the given relation.
1452+
*
1453+
* Note: Because this function assumes that the relation whose OID is passed
1454+
* as an argument and each ancestor will have precisely one parent, it should
1455+
* only be called when it is known that the relation is a partition.
1456+
*/
1457+
List*
1458+
get_partition_ancestors(Oidrelid)
1459+
{
1460+
List*result=NIL;
1461+
RelationinhRel;
1462+
1463+
inhRel=heap_open(InheritsRelationId,AccessShareLock);
1464+
1465+
get_partition_ancestors_worker(inhRel,relid,&result);
1466+
1467+
heap_close(inhRel,AccessShareLock);
1468+
1469+
returnresult;
1470+
}
1471+
1472+
/*
1473+
* get_partition_ancestors_worker
1474+
*recursive worker for get_partition_ancestors
1475+
*/
1476+
staticvoid
1477+
get_partition_ancestors_worker(RelationinhRel,Oidrelid,List**ancestors)
1478+
{
1479+
OidparentOid;
1480+
1481+
/* Recursion ends at the topmost level, ie., when there's no parent */
1482+
parentOid=get_partition_parent_worker(inhRel,relid);
1483+
if (parentOid==InvalidOid)
1484+
return;
1485+
1486+
*ancestors=lappend_oid(*ancestors,parentOid);
1487+
get_partition_ancestors_worker(inhRel,parentOid,ancestors);
1488+
}
1489+
14241490
/*
14251491
* get_qual_from_partbound
14261492
*Given a parser node for partition bound, return the list of executable

‎src/backend/executor/execMain.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1347,11 +1347,15 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
13471347
resultRelInfo->ri_FdwRoutine=GetFdwRoutineForRelation(resultRelationDesc, true);
13481348
else
13491349
resultRelInfo->ri_FdwRoutine=NULL;
1350+
1351+
/* The following fields are set later if needed */
13501352
resultRelInfo->ri_FdwState=NULL;
13511353
resultRelInfo->ri_usesFdwDirectModify= false;
13521354
resultRelInfo->ri_ConstraintExprs=NULL;
13531355
resultRelInfo->ri_junkFilter=NULL;
13541356
resultRelInfo->ri_projectReturning=NULL;
1357+
resultRelInfo->ri_onConflictArbiterIndexes=NIL;
1358+
resultRelInfo->ri_onConflict=NULL;
13551359

13561360
/*
13571361
* Partition constraint, which also includes the partition constraint of

0 commit comments

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