From ae025a15988f5491903cd3a2075f308c2773f711 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas Date: Fri, 26 Aug 2016 16:33:57 +0300 Subject: Support OID system column in postgres_fdw. You can use ALTER FOREIGN TABLE SET WITH OIDS on a foreign table, but the oid column read out as zeros, because the postgres_fdw didn't know about it. Teach postgres_fdw how to fetch it. Etsuro Fujita, with an additional test case by me. Discussion: <56E90A76.5000503@lab.ntt.co.jp> --- contrib/postgres_fdw/sql/postgres_fdw.sql | 13 ++++++++++++- 1 file changed, 12 insertions(+), 1 deletion(-) (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql') diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6f684a1b0c3..4f68e8904ee 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -136,6 +136,14 @@ CREATE FOREIGN TABLE ft6 ( c3 text ) SERVER loopback2 OPTIONS (schema_name 'S 1', table_name 'T 4'); +-- A table with oids. CREATE FOREIGN TABLE doesn't support the +-- WITH OIDS option, but ALTER does. +CREATE FOREIGN TABLE ft_pg_type ( + typname name, + typlen smallint +) SERVER loopback OPTIONS (schema_name 'pg_catalog', table_name 'pg_type'); +ALTER TABLE ft_pg_type SET WITH OIDS; + -- =================================================================== -- tests for validator -- =================================================================== @@ -577,7 +585,7 @@ DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; --- System columns, except ctid, should not be sent to remote +-- System columns, except ctid and oid, should not be sent to remote EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.tableoid = 'pg_class'::regclass LIMIT 1; SELECT * FROM ft1 t1 WHERE t1.tableoid = 'ft1'::regclass LIMIT 1; @@ -590,6 +598,9 @@ SELECT * FROM ft1 t1 WHERE t1.ctid = '(0,2)'; EXPLAIN (VERBOSE, COSTS OFF) SELECT ctid, * FROM ft1 t1 LIMIT 1; SELECT ctid, * FROM ft1 t1 LIMIT 1; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT oid, * FROM ft_pg_type WHERE typname = 'int4'; +SELECT oid, * FROM ft_pg_type WHERE typname = 'int4'; -- =================================================================== -- used in pl/pgsql function -- cgit v1.2.3