diff options
Diffstat (limited to 'contrib/postgres_fdw')
| -rw-r--r-- | contrib/postgres_fdw/deparse.c | 12 | ||||
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 68 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 14 |
3 files changed, 94 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index d7d9b9c77d2..1552c42688b 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -254,6 +254,18 @@ foreign_expr_walker(Node *node, var->varlevelsup == 0) { /* Var belongs to foreign table */ + + /* + * System columns other than ctid should not be sent to + * the remote, since we don't make any effort to ensure + * that local and remote values match (tableoid, in + * particular, almost certainly doesn't match). + */ + if (var->varattno < 0 && + var->varattno != SelfItemPointerAttributeNumber) + return false; + + /* Else check the collation */ collation = var->varcollid; state = OidIsValid(collation) ? FDW_COLLATE_SAFE : FDW_COLLATE_NONE; } diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 2e49ee317a2..86ce3346726 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -826,6 +826,74 @@ DEALLOCATE st2; DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; +-- System columns, except ctid, should not be sent to remote +EXPLAIN (VERBOSE, COSTS false) +SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; + QUERY PLAN +------------------------------------------------------------------------------- + Limit + Output: c1, c2, c3, c4, c5, c6, c7, c8 + -> Foreign Scan on public.ft1 t1 + Output: c1, c2, c3, c4, c5, c6, c7, c8 + Filter: (t1.tableoid = 1259::oid) + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" +(6 rows) + +SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +----+----+-------+------------------------------+--------------------------+----+------------+----- + 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo +(1 row) + +EXPLAIN (VERBOSE, COSTS false) +SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; + QUERY PLAN +------------------------------------------------------------------------------- + Limit + Output: ((tableoid)::regclass), c1, c2, c3, c4, c5, c6, c7, c8 + -> Foreign Scan on public.ft1 t1 + Output: (tableoid)::regclass, 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" +(5 rows) + +SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; + tableoid | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +----------+----+----+-------+------------------------------+--------------------------+----+------------+----- + ft1 | 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo +(1 row) + +EXPLAIN (VERBOSE, COSTS false) +SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; + 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 ((ctid = '(0,2)'::tid)) +(3 rows) + +SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; + c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +----+----+-------+------------------------------+--------------------------+----+------------+----- + 2 | 2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo +(1 row) + +EXPLAIN (VERBOSE, COSTS false) +SELECT ctid, * FROM ft1 t1 LIMIT 1; + QUERY PLAN +------------------------------------------------------------------------------------- + Limit + Output: ctid, c1, c2, c3, c4, c5, c6, c7, c8 + -> Foreign Scan on public.ft1 t1 + Output: ctid, c1, c2, c3, c4, c5, c6, c7, c8 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" +(5 rows) + +SELECT ctid, * FROM ft1 t1 LIMIT 1; + ctid | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 +-------+----+----+-------+------------------------------+--------------------------+----+------------+----- + (0,1) | 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo +(1 row) + -- =================================================================== -- used in pl/pgsql function -- =================================================================== diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6187839453c..d422884f308 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -248,6 +248,20 @@ DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; +-- System columns, except ctid, should not be sent to remote +EXPLAIN (VERBOSE, COSTS false) +SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; +SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; +EXPLAIN (VERBOSE, COSTS false) +SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; +SELECT tableoid::regclass, * FROM ft1 t1 LIMIT 1; +EXPLAIN (VERBOSE, COSTS false) +SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; +SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; +EXPLAIN (VERBOSE, COSTS false) +SELECT ctid, * FROM ft1 t1 LIMIT 1; +SELECT ctid, * FROM ft1 t1 LIMIT 1; + -- =================================================================== -- used in pl/pgsql function -- =================================================================== |