postgres.git / summary / log / commit / refs
commit 0a14bca662a44310305ad3fcc1079ef75d4a6316
Author: Amit Langote <amitlan@postgresql.org>
Date: Fri Jun 30 06:48:54 2023 +0000
First released in: 17.0, 16.0
Add a test case for a316a3bc
a316a3bc fixed the code in build_simpl_rel() that propagates
RelOptInfo.userid from parent to child rels so that it works
correctly for the child rels of a UNION ALL subquery rel, though
no tests were added in that commit. So do so here.
As noted in the discussion, coming up with a test case in the core
regression suite for this fix has turned out to be tricky, so the
test case is added to the postgres_fdw's suite instead.
postgresGetForeignRelSize()'s use of user mapping for the user
specified in RelOptInfo.userid makes it relatively easier to craft
a test case around.
Discussion: https://postgr.es/m/CA%2BHiwqH91GaFNXcXbLAM9L%3DzBwUmSyv699Mtv3i1_xtk9Xec_A%40mail.gmail.com
Backpatch-through: 16
contrib/postgres_fdw/expected/postgres_fdw.out | 42 ++++++++++++++++++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 23 ++++++++++++++
2 files changed, 65 insertions(+)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c8c4614b547..6513b3fff23 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2689,6 +2689,48 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
(10 rows)
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- ====================================================================
+-- Check that userid to use when querying the remote table is correctly
+-- propagated into foreign rels present in subqueries under an UNION ALL
+-- ====================================================================
+CREATE ROLE regress_view_owner_another;
+ALTER VIEW v4 OWNER TO regress_view_owner_another;
+GRANT SELECT ON ft4 TO regress_view_owner_another;
+ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true');
+-- The following should query the remote backing table of ft4 as user
+-- regress_view_owner_another, the view owner, though it fails as expected
+-- due to the lack of a user mapping for that user.
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
+ERROR: user mapping not found for "regress_view_owner_another"
+-- Likewise, but with the query under an UNION ALL
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
+ERROR: user mapping not found for "regress_view_owner_another"
+-- Should not get that error once a user mapping is created
+CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false');
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
+ QUERY PLAN
+--------------------------------------------------
+ Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.c2, ft4.c3
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+ -> Foreign Scan on public.ft4 ft4_1
+ Output: ft4_1.c1, ft4_1.c2, ft4_1.c3
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
+(7 rows)
+
+DROP USER MAPPING FOR regress_view_owner_another SERVER loopback;
+DROP OWNED BY regress_view_owner_another;
+DROP ROLE regress_view_owner_another;
+ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false');
-- cleanup
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index b54903ad8fa..e94abb7da81 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -714,6 +714,29 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- ====================================================================
+-- Check that userid to use when querying the remote table is correctly
+-- propagated into foreign rels present in subqueries under an UNION ALL
+-- ====================================================================
+CREATE ROLE regress_view_owner_another;
+ALTER VIEW v4 OWNER TO regress_view_owner_another;
+GRANT SELECT ON ft4 TO regress_view_owner_another;
+ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true');
+-- The following should query the remote backing table of ft4 as user
+-- regress_view_owner_another, the view owner, though it fails as expected
+-- due to the lack of a user mapping for that user.
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
+-- Likewise, but with the query under an UNION ALL
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
+-- Should not get that error once a user mapping is created
+CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false');
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
+DROP USER MAPPING FOR regress_view_owner_another SERVER loopback;
+DROP OWNED BY regress_view_owner_another;
+DROP ROLE regress_view_owner_another;
+ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false');
+
-- cleanup
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
[parent: f4691e2e76a4]