diff options
Diffstat (limited to 'contrib/postgres_fdw')
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 7 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 5 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 15 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.h | 2 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 3 |
5 files changed, 28 insertions, 4 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 94fab18c425..81cb2b447d8 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -847,8 +847,8 @@ appendWhereClause(StringInfo buf, void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, - List **retrieved_attrs) + List *targetAttrs, bool doNothing, + List *returningList, List **retrieved_attrs) { AttrNumber pindex; bool first; @@ -892,6 +892,9 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root, else appendStringInfoString(buf, " DEFAULT VALUES"); + if (doNothing) + appendStringInfoString(buf, " ON CONFLICT DO NOTHING"); + deparseReturningList(buf, root, rtindex, rel, rel->trigdesc && rel->trigdesc->trig_insert_after_row, returningList, retrieved_attrs); diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 93e9836cf0d..1f417b30be8 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2327,6 +2327,11 @@ INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key ERROR: duplicate key value violates unique constraint "t1_pkey" DETAIL: Key ("C 1")=(11) already exists. CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive ERROR: new row for relation "T 1" violates check constraint "c2positive" DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index de732319d79..173b4f06e65 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1171,6 +1171,7 @@ postgresPlanForeignModify(PlannerInfo *root, List *targetAttrs = NIL; List *returningList = NIL; List *retrieved_attrs = NIL; + bool doNothing = false; initStringInfo(&sql); @@ -1223,13 +1224,25 @@ postgresPlanForeignModify(PlannerInfo *root, returningList = (List *) list_nth(plan->returningLists, subplan_index); /* + * ON CONFLICT DO UPDATE and DO NOTHING case with inference specification + * should have already been rejected in the optimizer, as presently there + * is no way to recognize an arbiter index on a foreign table. Only DO + * NOTHING is supported without an inference specification. + */ + if (plan->onConflictAction == ONCONFLICT_NOTHING) + doNothing = true; + else if (plan->onConflictAction != ONCONFLICT_NONE) + elog(ERROR, "unexpected ON CONFLICT specification: %d", + (int) plan->onConflictAction); + + /* * Construct the SQL command string. */ switch (operation) { case CMD_INSERT: deparseInsertSql(&sql, root, resultRelation, rel, - targetAttrs, returningList, + targetAttrs, doNothing, returningList, &retrieved_attrs); break; case CMD_UPDATE: diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 950c6f79a22..3835ddb79ac 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -60,7 +60,7 @@ extern void appendWhereClause(StringInfo buf, List **params); extern void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, - List *targetAttrs, List *returningList, + List *targetAttrs, bool doNothing, List *returningList, List **retrieved_attrs); extern void deparseUpdateSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 4a23457e796..fcdd92e280b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -372,6 +372,9 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT DO NOTHING; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO NOTHING; -- unsupported +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) DO UPDATE SET c3 = 'ffg'; -- unsupported INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive |