Skip to content

Commit 958e20e

Browse files
robertmhaasrobertmhaas
committed
postgres_fdw: Attmempt to stabilize regression tests.
Even after commit 882ea50, some buildfarm members are still failing in the postgres_fdw tests. Try to fix that by disabling use of remote statistics for some test cases. Etsuro Fujita Discussion: http://postgr.es/m/5A7D76CF.8080601@lab.ntt.co.jp
1 parent b78d016 commit 958e20e

File tree

2 files changed

+34
-18
lines changed

2 files changed

+34
-18
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

+25-18
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

+9
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 || c3 FROM ft2 LIMIT 20;
10731078
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
@@ -1208,6 +1213,10 @@ commit;
12081213
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
12091214
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
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)