On 2017/03/10 9:10, Amit Langote wrote:
> On 2017/03/09 23:25, Robert Haas wrote:
>> On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote wrote:
>>> I updated the patch. Now it's reduced to simply removing the check in
>>> transformInsertStmt() that prevented using *any* ON CONFLICT on
>>> partitioned tables at all.
>>
>> This patch no longer applies.
>
> Rebased patch is attached.
Oops, really attached this time,
Thanks,
Amit
>From a6377bf0a060676ae461314f62f8da4aac8896f8 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Fri, 17 Feb 2017 14:18:01 +0900
Subject: [PATCH] ON CONFLICT DO NOTHING should work with partitioned tables
Currently, a check in transformInsertStmt() prevents *any*
ON CONFLICT clause from being specified on a partitioned table,
even those specifying DO NOTHING as the alternative action. It
is harmless to allow those, so remove that check. It would still
not be possible to use DO UPDATE with partitioned table though,
because infer_arbiter_indexes() will eventually error out upon
failing to find a unique/exclusion constraint. Remember it is
not at the moment possible to create these constraints on
partitioned tables.
Adds a test and updates the note in document about using ON CONFLICT
with partitioned tables.
---
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..de57930520 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3854,8 +3854,12 @@ ANALYZE measurement;
<listitem>
<para>
- <command>INSERT</command> statements with <literal>ON CONFLICT</>
- clause are currently not allowed on partitioned tables.
+ Using the <literal>ON CONFLICT</literal> clause with partitioned tables
+ will cause an error if <literal>DO UPDATE</literal> is specified as the
+ alternative action, because it requires specifying a unique or exclusion
+ constraint to determine if there is a conflict. Currently, it is not
+ possible to create indexes on partitioned tables required to implement
+ such constraints.
</para>
</listitem>
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.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
