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

Commit958e20e

Browse files
committed
postgres_fdw: Attmempt to stabilize regression tests.
Even after commit882ea50, somebuildfarm members are still failing in the postgres_fdw tests.Try to fix that by disabling use of remote statistics for sometest cases.Etsuro FujitaDiscussion:http://postgr.es/m/5A7D76CF.8080601@lab.ntt.co.jp
1 parentb78d016 commit958e20e

File tree

2 files changed

+34
-18
lines changed

2 files changed

+34
-18
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 25 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -4244,6 +4244,10 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
42444244
-- ===================================================================
42454245
-- test writable foreign table stuff
42464246
-- ===================================================================
4247+
-- Autovacuum on the remote side might affect remote estimates,
4248+
-- so use local stats on ft2 as well
4249+
ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'false');
4250+
ANALYZE ft2;
42474251
EXPLAIN (verbose, costs off)
42484252
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
42494253
QUERY PLAN
@@ -5520,32 +5524,32 @@ UPDATE ft2 SET c3 = 'baz'
55205524
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
55215525
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
55225526
RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down
5523-
QUERY PLAN
5524-
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5527+
QUERY PLAN
5528+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55255529
Update on public.ft2
55265530
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
55275531
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
5528-
->Nested Loop
5532+
->Hash Join
55295533
Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
5530-
Join Filter: (ft2.c2 === ft4.c1)
5531-
-> Foreign Scan on public.ft2
5532-
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
5533-
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
5534+
Hash Cond: (ft4.c1 = ft5.c1)
55345535
-> Foreign Scan
5535-
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
5536-
Relations: (public.ft4) INNER JOIN (public.ft5)
5537-
Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))
5538-
-> Hash Join
5539-
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
5540-
Hash Cond: (ft4.c1 = ft5.c1)
5536+
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3
5537+
Filter: (ft2.c2 === ft4.c1)
5538+
Relations: (public.ft2) INNER JOIN (public.ft4)
5539+
Remote SQL: SELECT r1."C 1", r1.c2, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) FOR UPDATE OF r1
5540+
-> Nested Loop
5541+
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3
5542+
-> Foreign Scan on public.ft2
5543+
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
5544+
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
55415545
-> Foreign Scan on public.ft4
55425546
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3
55435547
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
5544-
-> Hash
5545-
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
5546-
-> Foreign Scan on public.ft5
5547-
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
5548-
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
5548+
-> Hash
5549+
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
5550+
-> Foreign Scan on public.ft5
5551+
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
5552+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
55495553
(24 rows)
55505554

55515555
UPDATE ft2 SET c3 = 'baz'
@@ -5999,6 +6003,9 @@ select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
59996003
407 | 100
60006004
(13 rows)
60016005

6006+
-- Go back to use remote-estimate mode on ft2
6007+
VACUUM ANALYZE "S 1"."T 1";
6008+
ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'true');
60026009
-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
60036010
-- FIRST behavior here.
60046011
-- ORDER BY DESC NULLS LAST options

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1068,6 +1068,11 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
10681068
-- ===================================================================
10691069
-- test writable foreign table stuff
10701070
-- ===================================================================
1071+
-- Autovacuum on the remote side might affect remote estimates,
1072+
-- so use local stats on ft2 as well
1073+
ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate'false');
1074+
ANALYZE ft2;
1075+
10711076
EXPLAIN (verbose, costs off)
10721077
INSERT INTO ft2 (c1,c2,c3)SELECT c1+1000,c2+100, c3|| c3FROM ft2LIMIT20;
10731078
INSERT INTO ft2 (c1,c2,c3)SELECT c1+1000,c2+100, c3|| c3FROM ft2LIMIT20;
@@ -1208,6 +1213,10 @@ commit;
12081213
select c2,count(*)from ft2where c2<500group by1order by1;
12091214
select c2,count(*)from"S 1"."T 1"where c2<500group by1order by1;
12101215

1216+
-- Go back to use remote-estimate mode on ft2
1217+
VACUUM ANALYZE"S 1"."T 1";
1218+
ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate'true');
1219+
12111220
-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
12121221
-- FIRST behavior here.
12131222
-- ORDER BY DESC NULLS LAST options

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp