Skip to content

Commit 87c37e3

Browse files
robertmhaasrobertmhaas
committed
Re-allow INSERT .. ON CONFLICT DO NOTHING on partitioned tables.
Commit 8355a01 was reverted in f052307, but this attempt is hopefully better-considered: we now pass the correct value to ExecOpenIndices, which should avoid the crash that we hit before. Amit Langote, reviewed by Simon Riggs and by me. Some final editing by me. Discussion: http://postgr.es/m/7ff1e8ec-dc39-96b1-7f47-ff5965dceeac@lab.ntt.co.jp
1 parent 1cbc17a commit 87c37e3

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
int num_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(Relation rel,
66+
ExecSetupPartitionTupleRouting(ModifyTableState *mtstate,
67+
Relation rel,
6768
Index resultRTindex,
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
int num_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
typedef struct PartitionDispatchData *PartitionDispatch;
5151

52-
extern void ExecSetupPartitionTupleRouting(Relation rel,
52+
extern void ExecSetupPartitionTupleRouting(ModifyTableState *mtstate,
53+
Relation rel,
5354
Index resultRTindex,
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
drop table selfconflict;
474+
475+
-- check that the following works:
476+
-- insert into partitioned_table on conflict do nothing
477+
create table parted_conflict_test (a int, b char) partition by list (a);
478+
create table parted_conflict_test_1 partition of parted_conflict_test (b unique) for values in (1);
479+
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
480+
insert into parted_conflict_test values (1, 'a') on conflict do nothing;
481+
-- however, on conflict do update is not supported yet
482+
insert into parted_conflict_test values (1) on conflict (b) do update set a = excluded.a;
483+
-- but it works OK if we target the partition directly
484+
insert into parted_conflict_test_1 values (1) on conflict (b) do
485+
update set a = excluded.a;
486+
drop table parted_conflict_test;

0 commit comments

Comments
 (0)