Skip to content

Commit 054ff3b

Browse files
amitlanamitlan
committed
Add a test case for a316a3b
a316a3b 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
1 parent dd7c60f commit 054ff3b

File tree

2 files changed

+65
-0
lines changed

2 files changed

+65
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -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
26892689
(10 rows)
26902690

26912691
ALTER VIEW v4 OWNER TO regress_view_owner;
2692+
-- ====================================================================
2693+
-- Check that userid to use when querying the remote table is correctly
2694+
-- propagated into foreign rels present in subqueries under an UNION ALL
2695+
-- ====================================================================
2696+
CREATE ROLE regress_view_owner_another;
2697+
ALTER VIEW v4 OWNER TO regress_view_owner_another;
2698+
GRANT SELECT ON ft4 TO regress_view_owner_another;
2699+
ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true');
2700+
-- The following should query the remote backing table of ft4 as user
2701+
-- regress_view_owner_another, the view owner, though it fails as expected
2702+
-- due to the lack of a user mapping for that user.
2703+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
2704+
ERROR: user mapping not found for "regress_view_owner_another"
2705+
-- Likewise, but with the query under an UNION ALL
2706+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
2707+
ERROR: user mapping not found for "regress_view_owner_another"
2708+
-- Should not get that error once a user mapping is created
2709+
CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false');
2710+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
2711+
QUERY PLAN
2712+
--------------------------------------------------
2713+
Foreign Scan on public.ft4
2714+
Output: ft4.c1, ft4.c2, ft4.c3
2715+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
2716+
(3 rows)
2717+
2718+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
2719+
QUERY PLAN
2720+
--------------------------------------------------------
2721+
Append
2722+
-> Foreign Scan on public.ft4
2723+
Output: ft4.c1, ft4.c2, ft4.c3
2724+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
2725+
-> Foreign Scan on public.ft4 ft4_1
2726+
Output: ft4_1.c1, ft4_1.c2, ft4_1.c3
2727+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
2728+
(7 rows)
2729+
2730+
DROP USER MAPPING FOR regress_view_owner_another SERVER loopback;
2731+
DROP OWNED BY regress_view_owner_another;
2732+
DROP ROLE regress_view_owner_another;
2733+
ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false');
26922734
-- cleanup
26932735
DROP OWNED BY regress_view_owner;
26942736
DROP ROLE regress_view_owner;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -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
714714
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;
715715
ALTER VIEW v4 OWNER TO regress_view_owner;
716716

717+
-- ====================================================================
718+
-- Check that userid to use when querying the remote table is correctly
719+
-- propagated into foreign rels present in subqueries under an UNION ALL
720+
-- ====================================================================
721+
CREATE ROLE regress_view_owner_another;
722+
ALTER VIEW v4 OWNER TO regress_view_owner_another;
723+
GRANT SELECT ON ft4 TO regress_view_owner_another;
724+
ALTER FOREIGN TABLE ft4 OPTIONS (ADD use_remote_estimate 'true');
725+
-- The following should query the remote backing table of ft4 as user
726+
-- regress_view_owner_another, the view owner, though it fails as expected
727+
-- due to the lack of a user mapping for that user.
728+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
729+
-- Likewise, but with the query under an UNION ALL
730+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
731+
-- Should not get that error once a user mapping is created
732+
CREATE USER MAPPING FOR regress_view_owner_another SERVER loopback OPTIONS (password_required 'false');
733+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM v4;
734+
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM (SELECT * FROM v4 UNION ALL SELECT * FROM v4);
735+
DROP USER MAPPING FOR regress_view_owner_another SERVER loopback;
736+
DROP OWNED BY regress_view_owner_another;
737+
DROP ROLE regress_view_owner_another;
738+
ALTER FOREIGN TABLE ft4 OPTIONS (SET use_remote_estimate 'false');
739+
717740
-- cleanup
718741
DROP OWNED BY regress_view_owner;
719742
DROP ROLE regress_view_owner;

0 commit comments

Comments
 (0)