From 9d71323daca412e6e175595e1e42809fb5e1172d Mon Sep 17 00:00:00 2001
From: Robert Haas
Date: Thu, 7 Sep 2017 10:55:45 -0400
Subject: Even if some partitions are foreign, allow tuple routing.

This doesn't allow routing tuple to the foreign partitions themselves,
but it permits tuples to be routed to regular partitions despite the
presence of foreign partitions in the same inheritance hierarchy.

Etsuro Fujita, reviewed by Amit Langote and by me.

Discussion: http://postgr.es/m/bc3db4c1-1693-3b8a-559f-33ad2b50b7ad@lab.ntt.co.jp
---
 contrib/file_fdw/input/file_fdw.source  | 21 +++++++++
 contrib/file_fdw/output/file_fdw.source | 81 +++++++++++++++++++++++++++++++++
 2 files changed, 102 insertions(+)

(limited to 'contrib/file_fdw')

diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source
index 685561fc2a0..e6821d64d44 100644
--- a/contrib/file_fdw/input/file_fdw.source
+++ b/contrib/file_fdw/input/file_fdw.source
@@ -162,6 +162,27 @@ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
 ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
 DROP TABLE agg;
 
+-- declarative partitioning tests
+SET ROLE regress_file_fdw_superuser;
+CREATE TABLE pt (a int, b text) partition by list (a);
+CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
+OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
+CREATE TABLE p2 partition of pt for values in (2);
+SELECT tableoid::regclass, * FROM pt;
+SELECT tableoid::regclass, * FROM p1;
+SELECT tableoid::regclass, * FROM p2;
+COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
+COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
+SELECT tableoid::regclass, * FROM pt;
+SELECT tableoid::regclass, * FROM p1;
+SELECT tableoid::regclass, * FROM p2;
+INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
+INSERT INTO pt VALUES (2, 'xyzzy');
+SELECT tableoid::regclass, * FROM pt;
+SELECT tableoid::regclass, * FROM p1;
+SELECT tableoid::regclass, * FROM p2;
+DROP TABLE pt;
+
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source
index 01e2690a825..709c43ec804 100644
--- a/contrib/file_fdw/output/file_fdw.source
+++ b/contrib/file_fdw/output/file_fdw.source
@@ -289,6 +289,87 @@ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
 
 ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
 DROP TABLE agg;
+-- declarative partitioning tests
+SET ROLE regress_file_fdw_superuser;
+CREATE TABLE pt (a int, b text) partition by list (a);
+CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
+OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
+CREATE TABLE p2 partition of pt for values in (2);
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a |  b  
+----------+---+-----
+ p1       | 1 | foo
+ p1       | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a |  b  
+----------+---+-----
+ p1       | 1 | foo
+ p1       | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a | b 
+----------+---+---
+(0 rows)
+
+COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
+ERROR:  cannot route inserted tuples to a foreign table
+CONTEXT:  COPY pt, line 2: "1,qux"
+COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a |  b  
+----------+---+-----
+ p1       | 1 | foo
+ p1       | 1 | bar
+ p2       | 2 | baz
+ p2       | 2 | qux
+(4 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a |  b  
+----------+---+-----
+ p1       | 1 | foo
+ p1       | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a |  b  
+----------+---+-----
+ p2       | 2 | baz
+ p2       | 2 | qux
+(2 rows)
+
+INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
+ERROR:  cannot route inserted tuples to a foreign table
+INSERT INTO pt VALUES (2, 'xyzzy');
+SELECT tableoid::regclass, * FROM pt;
+ tableoid | a |   b   
+----------+---+-------
+ p1       | 1 | foo
+ p1       | 1 | bar
+ p2       | 2 | baz
+ p2       | 2 | qux
+ p2       | 2 | xyzzy
+(5 rows)
+
+SELECT tableoid::regclass, * FROM p1;
+ tableoid | a |  b  
+----------+---+-----
+ p1       | 1 | foo
+ p1       | 1 | bar
+(2 rows)
+
+SELECT tableoid::regclass, * FROM p2;
+ tableoid | a |   b   
+----------+---+-------
+ p2       | 2 | baz
+ p2       | 2 | qux
+ p2       | 2 | xyzzy
+(3 rows)
+
+DROP TABLE pt;
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
-- 
cgit v1.2.3