Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit054ff3b

Browse files
committed
Add a test case fora316a3b
a316a3b fixed the code in build_simpl_rel() that propagatesRelOptInfo.userid from parent to child rels so that it workscorrectly for the child rels of a UNION ALL subquery rel, thoughno tests were added in that commit. So do so here.As noted in the discussion, coming up with a test case in the coreregression suite for this fix has turned out to be tricky, so thetest case is added to the postgres_fdw's suite instead.postgresGetForeignRelSize()'s use of user mapping for the userspecified in RelOptInfo.userid makes it relatively easier to crafta test case around.Discussion:https://postgr.es/m/CA%2BHiwqH91GaFNXcXbLAM9L%3DzBwUmSyv699Mtv3i1_xtk9Xec_A%40mail.gmail.comBackpatch-through: 16
1 parentdd7c60f commit054ff3b

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
SELECTt1.c1,t2.c2FROM v4 t1LEFT JOIN ft5 t2ON (t1.c1=t2.c1)ORDER BYt1.c1,t2.c1 OFFSET10LIMIT10;
715715
ALTERVIEW 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+
ALTERVIEW v4 OWNER TO regress_view_owner_another;
723+
GRANTSELECTON 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 v4UNION ALLSELECT*FROM v4);
731+
-- Should not get that error once a user mapping is created
732+
CREATEUSERMAPPING 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 v4UNION ALLSELECT*FROM v4);
735+
DROPUSER 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)
[8]ページ先頭
©2009-2025 Movatter.jp