0001-This-change-adds-capability-to-fetch-explain-plans-f.patch
application/octet-stream
Filename: 0001-This-change-adds-capability-to-fetch-explain-plans-f.patch
Type: application/octet-stream
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0001
Subject: This change adds capability to fetch explain plans for foreign tables. We have introduced new option "remote_plans" to achieve the same. This option does not work with ANALYZE option yet.
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 581 | 0 |
| contrib/postgres_fdw/option.c | 71 | 0 |
| contrib/postgres_fdw/postgres_fdw.c | 247 | 9 |
| contrib/postgres_fdw/postgres_fdw.h | 26 | 0 |
| contrib/postgres_fdw/sql/postgres_fdw.sql | 39 | 0 |
From b5908a7ac4ea6f40493cb0d5638d6a254a4fb768 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <salved@amazon.com>
Date: Wed, 5 Nov 2025 05:49:11 +0000
Subject: [PATCH 1/1] This change adds capability to fetch explain plans for
foreign tables. We have introduced new option "remote_plans" to achieve the
same. This option does not work with ANALYZE option yet.
---
.../postgres_fdw/expected/postgres_fdw.out | 581 ++++++++++++++++++
contrib/postgres_fdw/option.c | 71 +++
contrib/postgres_fdw/postgres_fdw.c | 256 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 26 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 39 ++
5 files changed, 964 insertions(+), 9 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..5496a6ddea5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -441,6 +441,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -5086,6 +5253,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6303,6 +6837,25 @@ DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
ft2
(1 row)
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12260,6 +12813,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 04788b7e8b3..a3b87d2bbaf 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,8 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
+#include "commands/explain_state.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +42,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -561,6 +570,57 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -587,4 +647,15 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 06b52c65300..9b99a2386b3 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -43,6 +43,7 @@
#include "utils/builtins.h"
#include "utils/float.h"
#include "utils/guc.h"
+#include "utils/json.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -133,6 +134,20 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+static const char *const explain_formats[] = {
+ [EXPLAIN_FORMAT_TEXT] = "TEXT",
+ [EXPLAIN_FORMAT_JSON] = "JSON",
+ [EXPLAIN_FORMAT_XML] = "XML",
+ [EXPLAIN_FORMAT_YAML] = "YAML",
+};
+
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+#define GET_EXTENSION_ID() ((extension_id == -1) ? \
+ GetExplainExtensionId("postgres_fdw"): extension_id)
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -2822,6 +2837,65 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState * pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ appendStringInfo(&explain_sql, "EXPLAIN (\
+ GENERIC_PLAN 1, \
+ FORMAT %s, \
+ VERBOSE %d, \
+ COSTS %d, \
+ SETTINGS %d) \
+ %s",
+ explain_formats[es->format],
+ (es->verbose) ? 1 : 0,
+ (es->costs) ? 1 : 0,
+ (es->settings) ? 1 : 0,
+ sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", pstrdup(PQgetvalue(res, i, 0)));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -2831,6 +2905,9 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ List *foreign_scan_table = NIL;
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -2892,6 +2969,14 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * add one of the tables to foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table = lappend_oid(foreign_scan_table, rte->relid);
+
if (es->verbose)
{
char *namespace;
@@ -2917,15 +3002,38 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
+ ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
{
- char *sql;
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
- ExplainPropertyText("Remote SQL", sql, es);
+ if (node && !node->ss.ss_currentRelation &&
+ foreign_scan_table == NIL)
+ return;
+
+ if (node && node->ss.ss_currentRelation)
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+ else
+ table = GetForeignTable(list_nth_oid(foreign_scan_table, 0));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -2940,11 +3048,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -2954,6 +3063,24 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(rinfo->ri_RelationDesc->rd_rel->oid);
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(mtstate->ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+ }
}
/*
@@ -2966,12 +3093,31 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -7886,3 +8032,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans * explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index e69735298d7..b135664b933 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,21 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +194,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 9a8f9e28135..25823a7ebe7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -281,6 +281,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -1489,6 +1494,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1538,6 +1570,10 @@ EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -4138,6 +4174,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
--
2.43.0