Re: [HACKERS] postgres_fdw: Add support for INSERT OVERRIDING clause

[Michael Paquier]

On Tue, Oct 31, 2017 at 3:45 PM, Peter Eisentraut
 wrote:
> It has been pointed out to me that the command deparsing in postgres_fdw
> does not support the INSERT OVERRIDING clause that was added in PG10.
> Here is a patch that seems to fix that.  I don't know much about this,
> whether anything else needs to be added or whether there should be
> tests.  Perhaps someone more familiar with postgres_fdw details can
> check it.

Trying to insert some data using OVERRIDING SYSTEM VALUE on a foreign
table with a remote relation defined with GENERATED ALWAYS would just
fail:
=# insert into id_always_foreign OVERRIDING system VALUE values (8);
ERROR:  428C9: cannot insert into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

And that's confusing because there is no actual way to avoid this
error if postgres_fdw is unpatched.

I think that you should add some tests, and make sure that the
documentation of postgres-fdw.sgml mentions that those two clauses are
pushed down.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] postgres_fdw: Add support for INSERT OVERRIDING clause

[Peter Eisentraut]

It has been pointed out to me that the command deparsing in postgres_fdw
does not support the INSERT OVERRIDING clause that was added in PG10.
Here is a patch that seems to fix that.  I don't know much about this,
whether anything else needs to be added or whether there should be
tests.  Perhaps someone more familiar with postgres_fdw details can
check it.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 097ecf61a9c2740b02a21be19a92aed92388346a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut 
Date: Tue, 31 Oct 2017 11:44:14 -0400
Subject: [PATCH] postgres_fdw: Add support for INSERT OVERRIDING clause

---
 contrib/postgres_fdw/deparse.c | 16 +++-
 1 file changed, 15 insertions(+), 1 deletion(-)

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 2af8364010..8eb227605f 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1573,7 +1573,21 @@ deparseInsertSql(StringInfo buf, PlannerInfo *root,
deparseColumnRef(buf, rtindex, attnum, root, false);
}
 
-   appendStringInfoString(buf, ") VALUES (");
+   appendStringInfoString(buf, ") ");
+
+   switch (root->parse->override)
+   {
+   case OVERRIDING_USER_VALUE:
+   appendStringInfoString(buf, "OVERRIDING USER 
VALUE ");
+   break;
+   case OVERRIDING_SYSTEM_VALUE:
+   appendStringInfoString(buf, "OVERRIDING SYSTEM 
VALUE ");
+   break;
+   default:
+   break;
+   }
+
+   appendStringInfoString(buf, "VALUES (");
 
pindex = 1;
first = true;
-- 
2.14.3


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers