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

Commitc57cdc9

Browse files
committed
Fix mishandling of system columns in FDW queries.
postgres_fdw would send query conditions involving system columns to theremote server, even though it makes no effort to ensure that systemcolumns other than CTID match what the remote side thinks. tableoid,in particular, probably won't match and might have some use in queries.Hence, prevent sending conditions that include non-CTID system columns.Also, create_foreignscan_plan neglected to check local restrictionconditions while determining whether to set fsSystemCol for a foreignscan plan node. This again would bollix the results for queries thattest a foreign table's tableoid.Back-patch the first fix to 9.3 where postgres_fdw was introduced.Back-patch the second to 9.2. The code is probably broken in 9.1 aswell, but the patch doesn't apply cleanly there; given the weak stateof support for FDWs in 9.1, it doesn't seem worth fixing.Etsuro Fujita, reviewed by Ashutosh Bapat, and somewhat modified by me
1 parentbe2dfe4 commitc57cdc9

File tree

4 files changed

+116
-2
lines changed

4 files changed

+116
-2
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -253,6 +253,18 @@ foreign_expr_walker(Node *node,
253253
var->varlevelsup==0)
254254
{
255255
/* Var belongs to foreign table */
256+
257+
/*
258+
* System columns other than ctid should not be sent to
259+
* the remote, since we don't make any effort to ensure
260+
* that local and remote values match (tableoid, in
261+
* particular, almost certainly doesn't match).
262+
*/
263+
if (var->varattno<0&&
264+
var->varattno!=SelfItemPointerAttributeNumber)
265+
return false;
266+
267+
/* Else check the collation */
256268
collation=var->varcollid;
257269
state=OidIsValid(collation) ?FDW_COLLATE_SAFE :FDW_COLLATE_NONE;
258270
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -826,6 +826,74 @@ DEALLOCATE st2;
826826
DEALLOCATE st3;
827827
DEALLOCATE st4;
828828
DEALLOCATE st5;
829+
-- System columns, except ctid, should not be sent to remote
830+
EXPLAIN (VERBOSE, COSTS false)
831+
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1;
832+
QUERY PLAN
833+
-------------------------------------------------------------------------------
834+
Limit
835+
Output: c1, c2, c3, c4, c5, c6, c7, c8
836+
-> Foreign Scan on public.ft1 t1
837+
Output: c1, c2, c3, c4, c5, c6, c7, c8
838+
Filter: (t1.tableoid = 1259::oid)
839+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
840+
(6 rows)
841+
842+
SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1;
843+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
844+
----+----+-------+------------------------------+--------------------------+----+------------+-----
845+
1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
846+
(1 row)
847+
848+
EXPLAIN (VERBOSE, COSTS false)
849+
SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
850+
QUERY PLAN
851+
-------------------------------------------------------------------------------
852+
Limit
853+
Output: ((tableoid)::regclass), c1, c2, c3, c4, c5, c6, c7, c8
854+
-> Foreign Scan on public.ft1 t1
855+
Output: (tableoid)::regclass, c1, c2, c3, c4, c5, c6, c7, c8
856+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
857+
(5 rows)
858+
859+
SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1;
860+
tableoid | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
861+
----------+----+----+-------+------------------------------+--------------------------+----+------------+-----
862+
ft1 | 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
863+
(1 row)
864+
865+
EXPLAIN (VERBOSE, COSTS false)
866+
SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
867+
QUERY PLAN
868+
-------------------------------------------------------------------------------------------------------
869+
Foreign Scan on public.ft1 t1
870+
Output: c1, c2, c3, c4, c5, c6, c7, c8
871+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((ctid = '(0,2)'::tid))
872+
(3 rows)
873+
874+
SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)';
875+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
876+
----+----+-------+------------------------------+--------------------------+----+------------+-----
877+
2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo
878+
(1 row)
879+
880+
EXPLAIN (VERBOSE, COSTS false)
881+
SELECT ctid, * FROM ft1 t1 LIMIT 1;
882+
QUERY PLAN
883+
-------------------------------------------------------------------------------------
884+
Limit
885+
Output: ctid, c1, c2, c3, c4, c5, c6, c7, c8
886+
-> Foreign Scan on public.ft1 t1
887+
Output: ctid, c1, c2, c3, c4, c5, c6, c7, c8
888+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1"
889+
(5 rows)
890+
891+
SELECT ctid, * FROM ft1 t1 LIMIT 1;
892+
ctid | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
893+
-------+----+----+-------+------------------------------+--------------------------+----+------------+-----
894+
(0,1) | 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo
895+
(1 row)
896+
829897
-- ===================================================================
830898
-- used in pl/pgsql function
831899
-- ===================================================================

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -248,6 +248,20 @@ DEALLOCATE st3;
248248
DEALLOCATE st4;
249249
DEALLOCATE st5;
250250

251+
-- System columns, except ctid, should not be sent to remote
252+
EXPLAIN (VERBOSE, COSTS false)
253+
SELECT*FROM ft1 t1WHEREt1.tableoid='pg_class'::regclassLIMIT1;
254+
SELECT*FROM ft1 t1WHEREt1.tableoid='ft1'::regclassLIMIT1;
255+
EXPLAIN (VERBOSE, COSTS false)
256+
SELECT tableoid::regclass,*FROM ft1 t1LIMIT1;
257+
SELECT tableoid::regclass,*FROM ft1 t1LIMIT1;
258+
EXPLAIN (VERBOSE, COSTS false)
259+
SELECT*FROM ft1 t1WHEREt1.ctid='(0,2)';
260+
SELECT*FROM ft1 t1WHEREt1.ctid='(0,2)';
261+
EXPLAIN (VERBOSE, COSTS false)
262+
SELECT ctid,*FROM ft1 t1LIMIT1;
263+
SELECT ctid,*FROM ft1 t1LIMIT1;
264+
251265
-- ===================================================================
252266
-- used in pl/pgsql function
253267
-- ===================================================================

‎src/backend/optimizer/plan/createplan.c

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
#include<math.h>
2121

2222
#include"access/skey.h"
23+
#include"access/sysattr.h"
2324
#include"catalog/pg_class.h"
2425
#include"foreign/fdwapi.h"
2526
#include"miscadmin.h"
@@ -1954,6 +1955,8 @@ create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
19541955
RelOptInfo*rel=best_path->path.parent;
19551956
Indexscan_relid=rel->relid;
19561957
RangeTblEntry*rte;
1958+
Bitmapset*attrs_used=NULL;
1959+
ListCell*lc;
19571960
inti;
19581961

19591962
/* it should be a base rel... */
@@ -2001,17 +2004,34 @@ create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path,
20012004
* Detect whether any system columns are requested from rel. This is a
20022005
* bit of a kluge and might go away someday, so we intentionally leave it
20032006
* out of the API presented to FDWs.
2007+
*
2008+
* First, examine all the attributes needed for joins or final output.
2009+
* Note: we must look at reltargetlist, not the attr_needed data, because
2010+
* attr_needed isn't computed for inheritance child rels.
20042011
*/
2012+
pull_varattnos((Node*)rel->reltargetlist,rel->relid,&attrs_used);
2013+
2014+
/* Add all the attributes used by restriction clauses. */
2015+
foreach(lc,rel->baserestrictinfo)
2016+
{
2017+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
2018+
2019+
pull_varattnos((Node*)rinfo->clause,rel->relid,&attrs_used);
2020+
}
2021+
2022+
/* Now, are any system columns requested from rel? */
20052023
scan_plan->fsSystemCol= false;
2006-
for (i=rel->min_attr;i<0;i++)
2024+
for (i=FirstLowInvalidHeapAttributeNumber+1;i<0;i++)
20072025
{
2008-
if (!bms_is_empty(rel->attr_needed[i-rel->min_attr]))
2026+
if (bms_is_member(i-FirstLowInvalidHeapAttributeNumber,attrs_used))
20092027
{
20102028
scan_plan->fsSystemCol= true;
20112029
break;
20122030
}
20132031
}
20142032

2033+
bms_free(attrs_used);
2034+
20152035
returnscan_plan;
20162036
}
20172037

0 commit comments

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