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

Commit87c37e3

Browse files
committed
Re-allow INSERT .. ON CONFLICT DO NOTHING on partitioned tables.
Commit8355a01 was reverted inf052307, but this attempt ishopefully better-considered: we now pass the correct value toExecOpenIndices, which should avoid the crash that we hit before.Amit Langote, reviewed by Simon Riggs and by me. Some finalediting by me.Discussion:http://postgr.es/m/7ff1e8ec-dc39-96b1-7f47-ff5965dceeac@lab.ntt.co.jp
1 parent1cbc17a commit87c37e3

File tree

8 files changed

+51
-20
lines changed

8 files changed

+51
-20
lines changed

‎doc/src/sgml/ddl.sgml‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3288,10 +3288,15 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
32883288
<listitem>
32893289
<para>
32903290
Using the <literal>ON CONFLICT</literal> clause with partitioned tables
3291-
will cause an error, because unique or exclusion constraints can only be
3292-
created on individual partitions. There is no support for enforcing
3293-
uniqueness (or an exclusion constraint) across an entire partitioning
3294-
hierarchy.
3291+
will cause an error if the conflict target is specified (see
3292+
<xref linkend="sql-on-conflict" /> for more details on how the clause
3293+
works). Therefore, it is not possible to specify
3294+
<literal>DO UPDATE</literal> as the alternative action, because
3295+
specifying the conflict target is mandatory in that case. On the other
3296+
hand, specifying <literal>DO NOTHING</literal> as the alternative action
3297+
works fine provided the conflict target is not specified. In that case,
3298+
unique constraints (or exclusion constraints) of the individual leaf
3299+
partitions are considered.
32953300
</para>
32963301
</listitem>
32973302

‎src/backend/commands/copy.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2478,7 +2478,8 @@ CopyFrom(CopyState cstate)
24782478
intnum_parted,
24792479
num_partitions;
24802480

2481-
ExecSetupPartitionTupleRouting(cstate->rel,
2481+
ExecSetupPartitionTupleRouting(NULL,
2482+
cstate->rel,
24822483
1,
24832484
estate,
24842485
&partition_dispatch_info,

‎src/backend/executor/execPartition.c‎

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,8 @@ static char *ExecBuildSlotPartitionKeyDescription(Relation rel,
6363
* RowExclusiveLock mode upon return from this function.
6464
*/
6565
void
66-
ExecSetupPartitionTupleRouting(Relationrel,
66+
ExecSetupPartitionTupleRouting(ModifyTableState*mtstate,
67+
Relationrel,
6768
IndexresultRTindex,
6869
EState*estate,
6970
PartitionDispatch**pd,
@@ -133,13 +134,17 @@ ExecSetupPartitionTupleRouting(Relation rel,
133134
CheckValidResultRel(leaf_part_rri,CMD_INSERT);
134135

135136
/*
136-
* Open partition indices (remember we do not support ON CONFLICT in
137-
* case of partitioned tables, so we do not need support information
138-
* for speculative insertion)
137+
* Open partition indices. The user may have asked to check for
138+
* conflicts within this leaf partition and do "nothing" instead of
139+
* throwing an error. Be prepared in that case by initializing the
140+
* index information needed by ExecInsert() to perform speculative
141+
* insertions.
139142
*/
140143
if (leaf_part_rri->ri_RelationDesc->rd_rel->relhasindex&&
141144
leaf_part_rri->ri_IndexRelationDescs==NULL)
142-
ExecOpenIndices(leaf_part_rri, false);
145+
ExecOpenIndices(leaf_part_rri,
146+
mtstate!=NULL&&
147+
mtstate->mt_onconflict!=ONCONFLICT_NONE);
143148

144149
estate->es_leaf_result_relations=
145150
lappend(estate->es_leaf_result_relations,leaf_part_rri);

‎src/backend/executor/nodeModifyTable.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1953,7 +1953,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
19531953
intnum_parted,
19541954
num_partitions;
19551955

1956-
ExecSetupPartitionTupleRouting(rel,
1956+
ExecSetupPartitionTupleRouting(mtstate,
1957+
rel,
19571958
node->nominalRelation,
19581959
estate,
19591960
&partition_dispatch_info,

‎src/backend/parser/analyze.c‎

Lines changed: 0 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -847,16 +847,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
847847

848848
/* Process ON CONFLICT, if any. */
849849
if (stmt->onConflictClause)
850-
{
851-
/* Bail out if target relation is partitioned table */
852-
if (pstate->p_target_rangetblentry->relkind==RELKIND_PARTITIONED_TABLE)
853-
ereport(ERROR,
854-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
855-
errmsg("ON CONFLICT clause is not supported with partitioned tables")));
856-
857850
qry->onConflict=transformOnConflictClause(pstate,
858851
stmt->onConflictClause);
859-
}
860852

861853
/*
862854
* If we have a RETURNING clause, we need to add the target relation to

‎src/include/executor/execPartition.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,8 @@ typedef struct PartitionDispatchData
4949

5050
typedefstructPartitionDispatchData*PartitionDispatch;
5151

52-
externvoidExecSetupPartitionTupleRouting(Relationrel,
52+
externvoidExecSetupPartitionTupleRouting(ModifyTableState*mtstate,
53+
Relationrel,
5354
IndexresultRTindex,
5455
EState*estate,
5556
PartitionDispatch**pd,

‎src/test/regress/expected/insert_conflict.out‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -786,3 +786,16 @@ select * from selfconflict;
786786
(3 rows)
787787

788788
drop table selfconflict;
789+
-- check that the following works:
790+
-- insert into partitioned_table on conflict do nothing
791+
create table parted_conflict_test (a int, b char) partition by list (a);
792+
create table parted_conflict_test_1 partition of parted_conflict_test (b unique) for values in (1);
793+
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
794+
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
795+
-- however, on conflict do update is not supported yet
796+
insert into parted_conflict_test values (1) on conflict (b) do update set a = excluded.a;
797+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
798+
-- but it works OK if we target the partition directly
799+
insert into parted_conflict_test_1 values (1) on conflict (b) do
800+
update set a = excluded.a;
801+
drop table parted_conflict_test;

‎src/test/regress/sql/insert_conflict.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -471,3 +471,16 @@ commit;
471471
select*from selfconflict;
472472

473473
droptable selfconflict;
474+
475+
-- check that the following works:
476+
-- insert into partitioned_table on conflict do nothing
477+
createtableparted_conflict_test (aint, bchar) partition by list (a);
478+
createtableparted_conflict_test_1 partition of parted_conflict_test (b unique) forvaluesin (1);
479+
insert into parted_conflict_testvalues (1,'a')on conflict do nothing;
480+
insert into parted_conflict_testvalues (1,'a')on conflict do nothing;
481+
-- however, on conflict do update is not supported yet
482+
insert into parted_conflict_testvalues (1)on conflict (b) doupdateset a=excluded.a;
483+
-- but it works OK if we target the partition directly
484+
insert into parted_conflict_test_1values (1)on conflict (b) do
485+
updateset a=excluded.a;
486+
droptable parted_conflict_test;

0 commit comments

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