From 8355a011a0124bdf7ccbada206a967d427039553 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Mon, 27 Mar 2017 10:37:29 -0400 Subject: [PATCH] Allow ON CONFLICT .. DO NOTHING on a partitioned table. ON CONFLICT .. DO UPDATE still doesn't work, for lack of a way of enforcing uniqueness across partitions, but we can still allow this case. Amit Langote, per discussion with Peter Geoghegan. Additional wordsmithing by me. Discussion: http://postgr.es/m/CAA-aLv7Z4uygtq-Q5CvDi9Y=VZxUyEnuWjL=EwCfOof=L04hgg@mail.gmail.com --- doc/src/sgml/ddl.sgml | 8 ++++++-- src/backend/parser/analyze.c | 8 -------- src/test/regress/expected/insert_conflict.out | 10 ++++++++++ src/test/regress/sql/insert_conflict.sql | 10 ++++++++++ 4 files changed, 26 insertions(+), 10 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 09b5b3ff70..d1e915c11a 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -3854,8 +3854,12 @@ ANALYZE measurement; - INSERT statements with ON CONFLICT - clause are currently not allowed on partitioned tables. + Using the ON CONFLICT clause with partitioned tables + will cause an error if DO UPDATE is specified as the + alternative action, because unique or exclusion constraints can only be + created on individual partitions. There is no support for enforcing + uniqueness (or an exclusion constraint) across an entire partitioning + hierarchy. diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 3571e50aea..25699fbc4a 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -842,16 +842,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) /* Process ON CONFLICT, if any. */ if (stmt->onConflictClause) - { - /* Bail out if target relation is partitioned table */ - if (pstate->p_target_rangetblentry->relkind == RELKIND_PARTITIONED_TABLE) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("ON CONFLICT clause is not supported with partitioned tables"))); - qry->onConflict = transformOnConflictClause(pstate, stmt->onConflictClause); - } /* * If we have a RETURNING clause, we need to add the target relation to diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out index 8d005fddd4..c90d381b34 100644 --- a/src/test/regress/expected/insert_conflict.out +++ b/src/test/regress/expected/insert_conflict.out @@ -786,3 +786,13 @@ select * from selfconflict; (3 rows) drop table selfconflict; +-- check that the following works: +-- insert into partitioned_table on conflict do nothing +create table parted_conflict_test (a int, b char) partition by list (a); +create table parted_conflict_test_1 partition of parted_conflict_test for values in (1); +insert into parted_conflict_test values (1, 'a') on conflict do nothing; +insert into parted_conflict_test values (1, 'a') on conflict do nothing; +-- however, on conflict do update not supported yet +insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where excluded.a = 1; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +drop table parted_conflict_test, parted_conflict_test_1; diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql index df3a9b59b5..78bffc783d 100644 --- a/src/test/regress/sql/insert_conflict.sql +++ b/src/test/regress/sql/insert_conflict.sql @@ -471,3 +471,13 @@ commit; select * from selfconflict; drop table selfconflict; + +-- check that the following works: +-- insert into partitioned_table on conflict do nothing +create table parted_conflict_test (a int, b char) partition by list (a); +create table parted_conflict_test_1 partition of parted_conflict_test for values in (1); +insert into parted_conflict_test values (1, 'a') on conflict do nothing; +insert into parted_conflict_test values (1, 'a') on conflict do nothing; +-- however, on conflict do update not supported yet +insert into parted_conflict_test values (1) on conflict (a) do update set b = excluded.b where excluded.a = 1; +drop table parted_conflict_test, parted_conflict_test_1; -- 2.40.0