postgres_fdw: Add more test coverage for EvalPlanQual testing.

postgres_fdw supports EvalPlanQual testing by using the infrastructure
provided by the core with the RecheckForeignScan callback routine (cf.
commits 5fc4c26db and 385f337c9), but there has been no test coverage
for that, except that recent commit 12609fbac, which fixed an issue in
commit 385f337c9, added a test case to exercise only a code path added
by that commit to the core infrastructure.  So let's add test cases to
exercise other code paths as well at this time.

Like commit 12609fbac, back-patch to all supported branches.

Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>
Author: Etsuro Fujita <etsuro.fujita@gmail.com>
Discussion: https://postgr.es/m/CAPmGK15%2B6H%3DkDA%3D-y3Y28OAPY7fbAdyMosVofZZ%2BNc769epVTQ%40mail.gmail.com
Backpatch-through: 13

2 files changed, 155 insertions, 14 deletions

diff --git a/contrib/postgres_fdw/expected/eval_plan_qual.out b/contrib/postgres_fdw/expected/eval_plan_qual.out
index f3e3a22b336..5361fe6f329 100644
--- a/contrib/postgres_fdw/expected/eval_plan_qual.out
+++ b/contrib/postgres_fdw/expected/eval_plan_qual.out
@@ -1,11 +1,105 @@
 Parsed test spec with 2 sessions
 
-starting permutation: s0_begin s0_update s1_begin s1_tuplock s0_commit s1_commit
-step s0_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
-step s0_update: UPDATE a SET i = i + 1;
-step s1_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
-step s1_tuplock: 
-    -- Verify if the sub-select has a foreign-join plan
+starting permutation: s0_update_l s1_tuplock_l_0 s0_commit s1_commit
+step s0_update_l: UPDATE l SET i = i + 1;
+step s1_tuplock_l_0: 
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
+ <waiting ...>
+step s0_commit: COMMIT;
+step s1_tuplock_l_0: <... completed>
+QUERY PLAN                                                           
+---------------------------------------------------------------------
+LockRows                                                             
+  Output: l.i, l.v, l.ctid, ft.*                                     
+  ->  Nested Loop                                                    
+        Output: l.i, l.v, l.ctid, ft.*                               
+        ->  Seq Scan on public.l                                     
+              Output: l.i, l.v, l.ctid                               
+              Filter: (l.i = 123)                                    
+        ->  Foreign Scan on public.ft                                
+              Output: ft.*, ft.i                                     
+              Remote SQL: SELECT i, v FROM public.t WHERE ((i = 123))
+(10 rows)
+
+i|v
+-+-
+(0 rows)
+
+step s1_commit: COMMIT;
+
+starting permutation: s0_update_l s1_tuplock_l_1 s0_commit s1_commit
+step s0_update_l: UPDATE l SET i = i + 1;
+step s1_tuplock_l_1: 
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
+ <waiting ...>
+step s0_commit: COMMIT;
+step s1_tuplock_l_1: <... completed>
+QUERY PLAN                                                                   
+-----------------------------------------------------------------------------
+LockRows                                                                     
+  Output: l.i, l.v, l.ctid, ft.*                                             
+  ->  Nested Loop                                                            
+        Output: l.i, l.v, l.ctid, ft.*                                       
+        ->  Seq Scan on public.l                                             
+              Output: l.i, l.v, l.ctid                                       
+              Filter: (l.v = 'foo'::text)                                    
+        ->  Foreign Scan on public.ft                                        
+              Output: ft.*, ft.i                                             
+              Remote SQL: SELECT i, v FROM public.t WHERE ((i = $1::integer))
+(10 rows)
+
+i|v
+-+-
+(0 rows)
+
+step s1_commit: COMMIT;
+
+starting permutation: s0_update_a s1_tuplock_a_0 s0_commit s1_commit
+step s0_update_a: UPDATE a SET i = i + 1;
+step s1_tuplock_a_0: 
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
+    SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
+ <waiting ...>
+step s0_commit: COMMIT;
+step s1_tuplock_a_0: <... completed>
+QUERY PLAN                                                                                                                                                                                                              
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+LockRows                                                                                                                                                                                                                
+  Output: a.i, a.ctid, fb.*, fc.*                                                                                                                                                                                       
+  ->  Nested Loop                                                                                                                                                                                                       
+        Output: a.i, a.ctid, fb.*, fc.*                                                                                                                                                                                 
+        Join Filter: (fb.i = a.i)                                                                                                                                                                                       
+        ->  Foreign Scan                                                                                                                                                                                                
+              Output: fb.*, fb.i, fc.*, fc.i                                                                                                                                                                            
+              Relations: (public.fb) INNER JOIN (public.fc)                                                                                                                                                             
+              Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.i) END, r2.i, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.i) END, r3.i FROM (public.b r2 INNER JOIN public.c r3 ON (((r2.i = r3.i))))
+              ->  Nested Loop                                                                                                                                                                                           
+                    Output: fb.*, fb.i, fc.*, fc.i                                                                                                                                                                      
+                    Join Filter: (fb.i = fc.i)                                                                                                                                                                          
+                    ->  Foreign Scan on public.fb                                                                                                                                                                       
+                          Output: fb.*, fb.i                                                                                                                                                                            
+                          Remote SQL: SELECT i FROM public.b ORDER BY i ASC NULLS LAST                                                                                                                                  
+                    ->  Foreign Scan on public.fc                                                                                                                                                                       
+                          Output: fc.*, fc.i                                                                                                                                                                            
+                          Remote SQL: SELECT i FROM public.c                                                                                                                                                            
+        ->  Seq Scan on public.a                                                                                                                                                                                        
+              Output: a.i, a.ctid                                                                                                                                                                                       
+(20 rows)
+
+i
+-
+(0 rows)
+
+step s1_commit: COMMIT;
+
+starting permutation: s0_update_a s1_tuplock_a_1 s0_commit s1_commit
+step s0_update_a: UPDATE a SET i = i + 1;
+step s1_tuplock_a_1: 
     EXPLAIN (VERBOSE, COSTS OFF)
     SELECT a.i,
         (SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
@@ -15,7 +109,7 @@ step s1_tuplock:
     FROM a FOR UPDATE;
  <waiting ...>
 step s0_commit: COMMIT;
-step s1_tuplock: <... completed>
+step s1_tuplock_a_1: <... completed>
 QUERY PLAN                                                                                                                              
 ----------------------------------------------------------------------------------------------------------------------------------------
 LockRows                                                                                                                                
diff --git a/contrib/postgres_fdw/specs/eval_plan_qual.spec b/contrib/postgres_fdw/specs/eval_plan_qual.spec
index 30a83e04058..9f52270db69 100644
--- a/contrib/postgres_fdw/specs/eval_plan_qual.spec
+++ b/contrib/postgres_fdw/specs/eval_plan_qual.spec
@@ -6,12 +6,22 @@ setup
         BEGIN
             EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
                 OPTIONS (dbname '$$||current_database()||$$',
-                         port '$$||current_setting('port')||$$'
+                         port '$$||current_setting('port')||$$',
+                         use_remote_estimate 'true'
                 )$$;
         END;
     $d$;
     CREATE USER MAPPING FOR PUBLIC SERVER loopback;
 
+    CREATE TABLE l (i int, v text);
+    CREATE TABLE t (i int, v text);
+    CREATE FOREIGN TABLE ft (i int, v text) SERVER loopback OPTIONS (table_name 't');
+
+    INSERT INTO l VALUES (123, 'foo'), (456, 'bar'), (789, 'baz');
+    INSERT INTO t SELECT i, to_char(i, 'FM0000') FROM generate_series(1, 1000) i;
+    CREATE INDEX t_idx ON t (i);
+    ANALYZE l, t;
+
     CREATE TABLE a (i int);
     CREATE TABLE b (i int);
     CREATE TABLE c (i int);
@@ -21,10 +31,13 @@ setup
     INSERT INTO a VALUES (1);
     INSERT INTO b VALUES (1);
     INSERT INTO c VALUES (1);
+    ANALYZE a, b, c;
 }
 
 teardown
 {
+    DROP TABLE l;
+    DROP TABLE t;
     DROP TABLE a;
     DROP TABLE b;
     DROP TABLE c;
@@ -32,14 +45,38 @@ teardown
 }
 
 session s0
-step s0_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
-step s0_update { UPDATE a SET i = i + 1; }
+setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s0_update_l { UPDATE l SET i = i + 1; }
+step s0_update_a { UPDATE a SET i = i + 1; }
 step s0_commit { COMMIT; }
 
 session s1
-step s1_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
-step s1_tuplock {
-    -- Verify if the sub-select has a foreign-join plan
+setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
+
+# Test for EPQ with a foreign scan pushing down a qual
+step s1_tuplock_l_0 {
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
+}
+
+# Same test, except that the qual is parameterized
+step s1_tuplock_l_1 {
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
+    SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
+}
+
+# Test for EPQ with a foreign scan pushing down a join
+step s1_tuplock_a_0 {
+    EXPLAIN (VERBOSE, COSTS OFF)
+    SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
+    SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
+}
+
+# Same test, except that the join is contained in a SubLink sub-select, not
+# in the main query
+step s1_tuplock_a_1 {
     EXPLAIN (VERBOSE, COSTS OFF)
     SELECT a.i,
         (SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
@@ -48,8 +85,18 @@ step s1_tuplock {
         (SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
     FROM a FOR UPDATE;
 }
+
 step s1_commit { COMMIT; }
 
+# This test checks the case of rechecking a pushed-down qual.
+permutation s0_update_l s1_tuplock_l_0 s0_commit s1_commit
+
+# This test checks the same case, except that the qual is parameterized.
+permutation s0_update_l s1_tuplock_l_1 s0_commit s1_commit
+
+# This test checks the case of rechecking a pushed-down join.
+permutation s0_update_a s1_tuplock_a_0 s0_commit s1_commit
+
 # This test exercises EvalPlanQual with a SubLink sub-select (which should
 # be unaffected by any EPQ recheck behavior in the outer query).
-permutation s0_begin s0_update s1_begin s1_tuplock s0_commit s1_commit
+permutation s0_update_a s1_tuplock_a_1 s0_commit s1_commit