From c52910d129f198551751e2d08022ac2545eb6ffa Mon Sep 17 00:00:00 2001 From: Shigeru Hanada Date: Fri, 1 Oct 2010 14:44:48 +0900 Subject: [PATCH] Apply fdw_select patch. --- contrib/Makefile | 1 + contrib/README | 4 + contrib/pageinspect/rawpage.c | 5 + contrib/pgstattuple/pgstattuple.c | 3 + contrib/postgresql_fdw/Makefile | 22 + .../expected/postgresql_fdw.out | 536 +++++++++++++ contrib/postgresql_fdw/postgresql_fdw.c | 732 ++++++++++++++++++ contrib/postgresql_fdw/postgresql_fdw.h | 18 + contrib/postgresql_fdw/postgresql_fdw.sql.in | 10 + contrib/postgresql_fdw/sql/postgresql_fdw.sql | 286 +++++++ .../uninstall_postgresql_fdw.sql | 7 + doc/src/sgml/catalogs.sgml | 99 +++ doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/postgresql-fdw.sgml | 140 ++++ .../sgml/ref/alter_foreign_data_wrapper.sgml | 29 +- .../sgml/ref/create_foreign_data_wrapper.sgml | 20 +- doc/src/sgml/ref/create_foreign_table.sgml | 4 + doc/src/sgml/ref/lock.sgml | 3 +- doc/src/sgml/ref/select.sgml | 2 + src/backend/catalog/system_views.sql | 10 + src/backend/commands/analyze.c | 2 +- src/backend/commands/discard.c | 1 + src/backend/commands/explain.c | 6 + src/backend/commands/foreigncmds.c | 201 ++++- src/backend/commands/lockcmds.c | 7 +- src/backend/commands/tablecmds.c | 27 +- src/backend/commands/vacuum.c | 2 +- src/backend/executor/Makefile | 2 +- src/backend/executor/execAmi.c | 5 + src/backend/executor/execProcnode.c | 14 + src/backend/executor/nodeForeignscan.c | 253 ++++++ src/backend/foreign/Makefile | 2 +- src/backend/foreign/foreign.c | 55 +- src/backend/foreign/fsconnection.c | 329 ++++++++ src/backend/nodes/copyfuncs.c | 28 +- src/backend/nodes/equalfuncs.c | 9 +- src/backend/nodes/outfuncs.c | 11 + src/backend/optimizer/path/allpaths.c | 25 +- src/backend/optimizer/path/costsize.c | 22 + src/backend/optimizer/plan/createplan.c | 70 ++ src/backend/optimizer/plan/setrefs.c | 11 + src/backend/optimizer/plan/subselect.c | 4 + src/backend/optimizer/prep/prepunion.c | 9 + src/backend/optimizer/util/pathnode.c | 19 + src/backend/optimizer/util/plancat.c | 6 +- src/backend/parser/analyze.c | 11 + src/backend/parser/gram.y | 51 +- src/backend/utils/adt/pseudotypes.c | 26 + src/backend/utils/init/miscinit.c | 10 + src/bin/pg_dump/pg_dump.c | 14 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/psql/describe.c | 2 + src/bin/psql/tab-complete.c | 99 ++- src/include/catalog/pg_foreign_data_wrapper.h | 8 +- src/include/catalog/pg_proc.h | 6 + src/include/catalog/pg_type.h | 2 + src/include/executor/nodeForeignscan.h | 26 + src/include/foreign/foreign.h | 16 + src/include/nodes/execnodes.h | 61 ++ src/include/nodes/nodes.h | 2 + src/include/nodes/parsenodes.h | 9 +- src/include/nodes/plannodes.h | 9 + src/include/optimizer/cost.h | 1 + src/include/optimizer/pathnode.h | 1 + src/include/utils/builtins.h | 2 + src/test/regress/expected/foreign_data.out | 164 ++-- src/test/regress/expected/rules.out | 3 +- 68 files changed, 3375 insertions(+), 202 deletions(-) create mode 100644 contrib/postgresql_fdw/Makefile create mode 100644 contrib/postgresql_fdw/expected/postgresql_fdw.out create mode 100644 contrib/postgresql_fdw/postgresql_fdw.c create mode 100644 contrib/postgresql_fdw/postgresql_fdw.h create mode 100644 contrib/postgresql_fdw/postgresql_fdw.sql.in create mode 100644 contrib/postgresql_fdw/sql/postgresql_fdw.sql create mode 100644 contrib/postgresql_fdw/uninstall_postgresql_fdw.sql create mode 100644 doc/src/sgml/postgresql-fdw.sgml create mode 100644 src/backend/executor/nodeForeignscan.c create mode 100644 src/backend/foreign/fsconnection.c create mode 100644 src/include/executor/nodeForeignscan.h diff --git a/contrib/Makefile b/contrib/Makefile index b777325534..a1c9b80647 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -39,6 +39,7 @@ SUBDIRS = \ pgcrypto \ pgrowlocks \ pgstattuple \ + postgresql_fdw \ seg \ spi \ tablefunc \ diff --git a/contrib/README b/contrib/README index a04c04346e..0e57e28fe5 100644 --- a/contrib/README +++ b/contrib/README @@ -150,6 +150,10 @@ pgstattuple - space within a table by Tatsuo Ishii +postgresql_fdw - + A function to handle access to foreign tables on other + PostgreSQL servers. + seg - Confidence-interval datatype (GiST indexing example) by Gene Selkov, Jr. diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c index f341a7247d..822a31f080 100644 --- a/contrib/pageinspect/rawpage.c +++ b/contrib/pageinspect/rawpage.c @@ -119,6 +119,11 @@ get_raw_page_internal(text *relname, ForkNumber forknum, BlockNumber blkno) (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot get raw page from composite type \"%s\"", RelationGetRelationName(rel)))); + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot get raw page from foreign table \"%s\"", + RelationGetRelationName(rel)))); /* * Reject attempts to read non-local temporary relations; we would be diff --git a/contrib/pgstattuple/pgstattuple.c b/contrib/pgstattuple/pgstattuple.c index 3a5d9c27b4..e5ddd87091 100644 --- a/contrib/pgstattuple/pgstattuple.c +++ b/contrib/pgstattuple/pgstattuple.c @@ -242,6 +242,9 @@ pgstat_relation(Relation rel, FunctionCallInfo fcinfo) case RELKIND_COMPOSITE_TYPE: err = "composite type"; break; + case RELKIND_FOREIGN_TABLE: + err = "foreign table"; + break; default: err = "unknown"; break; diff --git a/contrib/postgresql_fdw/Makefile b/contrib/postgresql_fdw/Makefile new file mode 100644 index 0000000000..c20cf80f9f --- /dev/null +++ b/contrib/postgresql_fdw/Makefile @@ -0,0 +1,22 @@ +# contrib/postgresql_fdw/Makefile + +MODULE_big = postgresql_fdw +PG_CPPFLAGS = -I$(libpq_srcdir) +OBJS = postgresql_fdw.o +SHLIB_LINK = $(libpq) + +DATA_built = postgresql_fdw.sql +DATA = uninstall_postgresql_fdw.sql +REGRESS = postgresql_fdw + + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/postgresql_fdw +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/postgresql_fdw/expected/postgresql_fdw.out b/contrib/postgresql_fdw/expected/postgresql_fdw.out new file mode 100644 index 0000000000..984d0a3cb8 --- /dev/null +++ b/contrib/postgresql_fdw/expected/postgresql_fdw.out @@ -0,0 +1,536 @@ +SET SEARCH_PATH = public; +SET DATESTYLE = 'Postgres, MDY'; +-- ============================================================================= +-- Prepare section +-- ============================================================================= +-- connect database for regression test +\c contrib_regression +-- install handler function +\i postgresql_fdw.sql +/* contrib/postgresql/postgresql.sql.in */ +-- Adjust this setting to control where the objects get created. +set search_path = public; +CREATE OR REPLACE FUNCTION postgresql_fdw_handler () +RETURNS fdw_handler +AS '$libdir/postgresql_fdw','postgresql_fdw_handler' +LANGUAGE C STRICT; +-- create test user +CREATE ROLE contrib_regression_role_1; +-- create remote database 1 +CREATE DATABASE contrib_regression_f1; +\c contrib_regression_f1 +CREATE TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person primary key (id) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_person" for table "person" +BEGIN; +INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person VALUES(3, 'buz', NULL, NULL); +COMMIT; +-- create remote database 2 +CREATE DATABASE contrib_regression_f2; +\c contrib_regression_f2 +CREATE TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person primary key (id) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_person" for table "person" +BEGIN; +INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person VALUES(3, 'buz', NULL, NULL); +COMMIT; +-- connect database for regression test +\c contrib_regression +-- create FOREIGN DATA WRAPPER for PostgresSQL +CREATE FOREIGN DATA WRAPPER contrib_regression_wrapper + HANDLER postgresql_fdw_handler + VALIDATOR postgresql_fdw_validator; +-- create FOREIGN SERVER for remote database 1 +CREATE SERVER contrib_regression_srv_1 + FOREIGN DATA WRAPPER contrib_regression_wrapper + OPTIONS (host 'localhost', dbname 'contrib_regression_f1'); +CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_1; +-- create FOREIGN SERVER for remote database 2 +CREATE SERVER contrib_regression_srv_2 + FOREIGN DATA WRAPPER contrib_regression_wrapper + OPTIONS (host 'localhost', dbname 'contrib_regression_f2'); +CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_2; +-- Check ALTER FOREIGN TABLE OWNER TO before create various test tables +CREATE FOREIGN TABLE ft1 (c1 integer) SERVER contrib_regression_srv_2; +ALTER FOREIGN TABLE ft1 OWNER TO contrib_regression_role_1; +\d + List of relations + Schema | Name | Type | Owner +--------+------+---------------+--------------------------- + public | ft1 | foreign table | contrib_regression_role_1 +(1 row) + +DROP FOREIGN TABLE ft1; +-- create entity of local table with same contents +CREATE TABLE person_l ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person_l primary key (id) +); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_person_l" for table "person_l" +BEGIN; +INSERT INTO person_l VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person_l VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person_l VALUES(4, 'bar', NULL, NULL); +COMMIT; +-- create foreign table which references table 'person' +CREATE FOREIGN TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone +) SERVER contrib_regression_srv_1; +CREATE FOREIGN TABLE person2 ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone +) SERVER contrib_regression_srv_2 OPTIONS (nspname 'public', relname 'person'); +\det+ + List of foreign tables + Table | Server | Options +---------+--------------------------+--------------------------------- + person | contrib_regression_srv_1 | + person2 | contrib_regression_srv_2 | {nspname=public,relname=person} +(2 rows) + +-- ============================================================================= +-- Misc statement section +-- ============================================================================= +ALTER FOREIGN TABLE person INHERIT person_l; +-- row lock via view is not allowed too. +CREATE OR REPLACE VIEW v_person AS SELECT * FROM person; +SELECT * FROM v_person FOR UPDATE NOWAIT; +ERROR: SELECT FOR UPDATE/SHARE is not allowed with foreign tables +DROP VIEW v_person; +-- row lock via CTE is not allowed but no error occurs. +WITH t AS (SELECT * FROM person) SELECT * FROM t ORDER BY id FOR UPDATE NOWAIT; -- not error + id | name | birthday | update_ts +----+------+------------+------------------------------ + 1 | foo | 01-31-2000 | Sun Jan 30 16:00:00 2000 PST + 2 | bar | 01-31-1900 | Tue Jan 30 16:00:00 1900 PST + 3 | buz | | +(3 rows) + +-- row lock in CTE is not allowed and an error occurs. +WITH t AS (SELECT * FROM person FOR UPDATE) SELECT * FROM t ORDER BY id; -- error +ERROR: SELECT FOR UPDATE/SHARE is not allowed with foreign tables +-- cleanup +ALTER FOREIGN TABLE person NO INHERIT person_l; +-- ============================================================================= +-- Connection cache test section +-- XXX: some of these tests should been moved to regression test of core. +-- ============================================================================= +-- clear connection cache +DISCARD ALL; +-- access foreign table (no result needed) +SELECT * FROM person WHERE 1 = 0; + id | name | birthday | update_ts +----+------+----------+----------- +(0 rows) + +-- one connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + srvname | usename | fdwname +--------------------------+---------+---------------------------- + contrib_regression_srv_1 | t | contrib_regression_wrapper +(1 row) + +-- access remote database 2 (no result needed) +SELECT id, name, birthday FROM person2 WHERE 1 = 0; + id | name | birthday +----+------+---------- +(0 rows) + +-- two connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + srvname | usename | fdwname +--------------------------+---------+---------------------------- + contrib_regression_srv_1 | t | contrib_regression_wrapper + contrib_regression_srv_2 | t | contrib_regression_wrapper +(2 rows) + +-- disconnect from all servers +DISCARD ALL; +-- connection cache must be empty +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + srvname | usename | fdwname +---------+---------+--------- +(0 rows) + +-- access remote database 1 and 2 (no result needed) +SELECT id, name, birthday FROM person WHERE 1 = 0; + id | name | birthday +----+------+---------- +(0 rows) + +SELECT id, name, birthday FROM person2 WHERE 1 = 0; + id | name | birthday +----+------+---------- +(0 rows) + +-- two connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + srvname | usename | fdwname +--------------------------+---------+---------------------------- + contrib_regression_srv_1 | t | contrib_regression_wrapper + contrib_regression_srv_2 | t | contrib_regression_wrapper +(2 rows) + +-- change authorization identifier +SET SESSION AUTHORIZATION contrib_regression_role_1; +-- connection cache must be empty +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + srvname | usename | fdwname +---------+---------+--------- +(0 rows) + +-- cleanup +RESET SESSION AUTHORIZATION; +DROP FOREIGN TABLE person2; +\det+ + List of foreign tables + Table | Server | Options +--------+--------------------------+--------- + person | contrib_regression_srv_1 | +(1 row) + +-- ============================================================================= +-- Query test section +-- ============================================================================= +-- all tuples with ORDER BY clause +SELECT id, name, birthday, + xmin, xmax, cmin, cmax, ctid, (tableoid = 'person'::regclass) tableoid + FROM person ORDER BY id; + id | name | birthday | xmin | xmax | cmin | cmax | ctid | tableoid +----+------+------------+------+------+------+------+----------------+---------- + 1 | foo | 01-31-2000 | 0 | 0 | 0 | 0 | (4294967295,0) | t + 2 | bar | 01-31-1900 | 0 | 0 | 0 | 0 | (4294967295,0) | t + 3 | buz | | 0 | 0 | 0 | 0 | (4294967295,0) | t +(3 rows) + +-- operator and function call in SELECT clause +SELECT id + 10 id, upper(name) upper_name, birthday FROM person ORDER BY id; + id | upper_name | birthday +----+------------+------------ + 11 | FOO | 01-31-2000 + 12 | BAR | 01-31-1900 + 13 | BUZ | +(3 rows) + +-- various join/subquery situations +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; + id | name | birthday | id | name | birthday +----+------+------------+----+------+------------ + 1 | foo | 01-31-2000 | 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 | 2 | bar | 01-31-1900 +(2 rows) + +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f LEFT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; + id | name | birthday | id | name | birthday +----+------+------------+----+------+------------ + 1 | foo | 01-31-2000 | 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 | 2 | bar | 01-31-1900 + 3 | buz | | | | +(3 rows) + +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f RIGHT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; + id | name | birthday | id | name | birthday +----+------+------------+----+------+------------ + 1 | foo | 01-31-2000 | 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 | 2 | bar | 01-31-1900 + | | | 4 | bar | +(3 rows) + +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f FULL OUTER JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; + id | name | birthday | id | name | birthday +----+------+------------+----+------+------------ + 1 | foo | 01-31-2000 | 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 | 2 | bar | 01-31-1900 + 3 | buz | | | | + | | | 4 | bar | +(4 rows) + +SELECT id, name, birthday FROM person f WHERE f.id = (SELECT min(p.id) FROM person_l p) ORDER BY f.id; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 +(1 row) + +SELECT id, name, birthday FROM person f WHERE (f.id, f.name) IN (SELECT p.id, p.name FROM person_l p) ORDER BY f.id; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 +(2 rows) + +-- union/intersect/except +SELECT id, name, birthday FROM person f UNION SELECT id, name, birthday FROM person_l p ORDER BY id; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 + 2 | bar | 01-31-1900 + 3 | buz | + 4 | bar | +(4 rows) + +SELECT name FROM person f INTERSECT SELECT name FROM person_l p ORDER BY name; + name +------ + bar + foo +(2 rows) + +SELECT name FROM person f EXCEPT SELECT name FROM person_l p ORDER BY name; + name +------ + buz +(1 row) + +-- WHERE clause evaluation in the foreign server +SELECT id, name, birthday FROM person WHERE id = 1; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 +(1 row) + +SELECT id, name, birthday FROM person WHERE birthday IS NULL ORDER BY id; + id | name | birthday +----+------+---------- + 3 | buz | +(1 row) + +SELECT id, name, birthday FROM person WHERE id IN (1, -1, 5) ORDER BY id; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 +(1 row) + +SELECT id, name, birthday FROM person WHERE id IS DISTINCT from 1 ORDER BY id; + id | name | birthday +----+------+------------ + 2 | bar | 01-31-1900 + 3 | buz | +(2 rows) + +SELECT id, name, birthday FROM person WHERE ARRAY[0,id,2] = ARRAY[0,1,2] ORDER BY id; + id | name | birthday +----+------+------------ + 1 | foo | 01-31-2000 +(1 row) + +-- WHERE clause evaluation in local server +SELECT id, name, birthday FROM person WHERE update_ts < '01-31-1999'::date ORDER BY id; + id | name | birthday +----+------+------------ + 2 | bar | 01-31-1900 +(1 row) + +-- limit/offset +SELECT id, name, birthday FROM person f ORDER BY id LIMIT 1 OFFSET 1; + id | name | birthday +----+------+------------ + 2 | bar | 01-31-1900 +(1 row) + +-- PREPARE/EXECUTE +PREPARE ST1(integer, integer) AS SELECT $1 param, id, name, birthday FROM person f WHERE f.id = $2; +EXECUTE ST1(0, 1); + param | id | name | birthday +-------+----+------+------------ + 0 | 1 | foo | 01-31-2000 +(1 row) + +EXECUTE ST1(1, 2); + param | id | name | birthday +-------+----+------+------------ + 1 | 2 | bar | 01-31-1900 +(1 row) + +DEALLOCATE ST1; +-- ============================================================================= +-- DDL test section +-- ============================================================================= +-- NOT NULL and CHECK constraints in column constraint syntax +CREATE FOREIGN TABLE ft2 (c1 integer NOT NULL, c2 text CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1; +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | Storage | Description +--------+---------+-----------+----------+------------- + c1 | integer | not null | plain | + c2 | text | | extended | +Check constraints: + "ft2_c2_check" CHECK (length(c2) > 0) +Server: contrib_regression_srv_1 +Has OIDs: no + +-- CHECK constraints in table constraint syntax +CREATE FOREIGN TABLE ft3 (c1 integer, c2 text, CONSTRAINT ft3_c2_check CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1; +\d+ ft3 + Foreign table "public.ft3" + Column | Type | Modifiers | Storage | Description +--------+---------+-----------+----------+------------- + c1 | integer | | plain | + c2 | text | | extended | +Check constraints: + "ft3_c2_check" CHECK (length(c2) > 0) +Server: contrib_regression_srv_1 +Has OIDs: no + +-- PRIMARY KEY in column constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer PRIMARY KEY) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "PRIMARY" +LINE 1: CREATE FOREIGN TABLE ft4 (c1 integer PRIMARY KEY) SERVER con... + ^ +-- UNIQUE in column constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer UNIQUE) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "UNIQUE" +LINE 1: CREATE FOREIGN TABLE ft4 (c1 integer UNIQUE) SERVER contrib_... + ^ +-- FOREIGN KEY in column constraint syntax - error +CREATE TABLE t1 (c1 integer PRIMARY KEY); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" +CREATE FOREIGN TABLE ft4 (c1 integer REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "REFERENCES" +LINE 1: CREATE FOREIGN TABLE ft4 (c1 integer REFERENCES t1 (c1)) SER... + ^ +-- PRIMARY KEY in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_pkey PRIMARY KEY (c1)) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "PRIMARY" +LINE 1: ...OREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_pkey PRIMARY KE... + ^ +-- UNIQUE in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_unique UNIQUE (c1)) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "UNIQUE" +LINE 1: ...N TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_unique UNIQUE (c1... + ^ +-- FOREIGN KEY in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_fkey FOREIGN KEY (c1) REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error +ERROR: syntax error at or near "FOREIGN" +LINE 1: ...IGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_fkey FOREIGN KE... + ^ +-- cleanup +DROP FOREIGN TABLE ft2; +DROP FOREIGN TABLE ft3; +DROP TABLE t1; +-- delete all data to use as inherited (parent) table +DELETE FROM person_l; +-- change table definition +\c contrib_regression_f1 +ALTER TABLE person ADD COLUMN dummy text; +UPDATE person SET dummy = 'dummy value'; +\c contrib_regression +ALTER FOREIGN TABLE person INHERIT person_l; +ALTER TABLE person_l ADD COLUMN dummy text; -- added to person too +CREATE RULE rl_person_insert AS ON INSERT TO person DO INSTEAD INSERT INTO person_l VALUES (NEW.*); +\d+ person; + Foreign table "public.person" + Column | Type | Modifiers | Storage | Description +-----------+--------------------------+-----------+----------+------------- + id | integer | not null | plain | + name | text | not null | extended | + birthday | date | | plain | + update_ts | timestamp with time zone | | plain | + dummy | text | | extended | +Rules: + rl_person_insert AS + ON INSERT TO person DO INSTEAD INSERT INTO person_l (id, name, birthday, update_ts, dummy) + VALUES (new.id, new.name, new.birthday, new.update_ts, new.dummy) +Server: contrib_regression_srv_1 +Inherits: person_l +Has OIDs: no + +\d+ person_l; + Table "public.person_l" + Column | Type | Modifiers | Storage | Description +-----------+--------------------------+-----------+----------+------------- + id | integer | not null | plain | + name | text | not null | extended | + birthday | date | | plain | + update_ts | timestamp with time zone | | plain | + dummy | text | | extended | +Indexes: + "pk_person_l" PRIMARY KEY, btree (id) +Child tables: person +Has OIDs: no + +-- content of person must be showed +INSERT INTO person VALUES (-1, 'FOO', '2100-01-31', null, 'DUMMY'); +SELECT * FROM person_l ORDER BY id; + id | name | birthday | update_ts | dummy +----+------+------------+------------------------------+------------- + -1 | FOO | 01-31-2100 | | DUMMY + 1 | foo | 01-31-2000 | Sun Jan 30 16:00:00 2000 PST | dummy value + 2 | bar | 01-31-1900 | Tue Jan 30 16:00:00 1900 PST | dummy value + 3 | buz | | | dummy value +(4 rows) + +-- restore table definition +\c contrib_regression_f1 +ALTER TABLE person DROP COLUMN dummy; +\c contrib_regression +DROP RULE rl_person_insert ON person; +ALTER TABLE person_l DROP COLUMN dummy; +ALTER FOREIGN TABLE person NO INHERIT person_l; +\d+ person; + Foreign table "public.person" + Column | Type | Modifiers | Storage | Description +-----------+--------------------------+-----------+----------+------------- + id | integer | not null | plain | + name | text | not null | extended | + birthday | date | | plain | + update_ts | timestamp with time zone | | plain | +Server: contrib_regression_srv_1 +Has OIDs: no + +-- no child table, result must be its own content +SELECT * FROM person_l ORDER BY id; + id | name | birthday | update_ts +----+------+------------+----------- + -1 | FOO | 01-31-2100 | +(1 row) + +-- query for foreign table which has no column +ALTER FOREIGN TABLE person DROP COLUMN id; +ALTER FOREIGN TABLE person DROP COLUMN name; +ALTER FOREIGN TABLE person DROP COLUMN birthday; +ALTER FOREIGN TABLE person DROP COLUMN update_ts; +SELECT * FROM person; +-- +(3 rows) + +-- ============================================================================= +-- Cleanup section +-- ============================================================================= +DISCARD ALL; +DROP FOREIGN DATA WRAPPER contrib_regression_wrapper CASCADE; +NOTICE: drop cascades to 5 other objects +DETAIL: drop cascades to server contrib_regression_srv_1 +drop cascades to user mapping for public +drop cascades to foreign table person +drop cascades to server contrib_regression_srv_2 +drop cascades to user mapping for public +DROP DATABASE contrib_regression_f1; +DROP DATABASE contrib_regression_f2; +DROP ROLE contrib_regression_role_1; diff --git a/contrib/postgresql_fdw/postgresql_fdw.c b/contrib/postgresql_fdw/postgresql_fdw.c new file mode 100644 index 0000000000..77fc107339 --- /dev/null +++ b/contrib/postgresql_fdw/postgresql_fdw.c @@ -0,0 +1,732 @@ +/*------------------------------------------------------------------------- + * + * postgresql_fdw.c + * foreign-data wrapper for PostgreSQL + * + * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group + * + * IDENTIFICATION + * $PostgreSQL$ + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "catalog/pg_operator.h" +#include "catalog/pg_proc.h" +#include "funcapi.h" +#include "libpq-fe.h" +#include "mb/pg_wchar.h" +#include "miscadmin.h" +#include "nodes/nodeFuncs.h" +#include "nodes/makefuncs.h" +#include "optimizer/clauses.h" +#include "parser/scansup.h" +#include "utils/builtins.h" +#include "utils/lsyscache.h" +#include "utils/memutils.h" +#include "utils/syscache.h" + +#include "postgresql_fdw.h" + +PG_MODULE_MAGIC; + +/* + * WHERE caluse optimization level + */ +#define EVAL_QUAL_LOCAL 0 /* evaluate none in foreign, all in local */ +#define EVAL_QUAL_BOTH 1 /* evaluate some in foreign, all in local */ +#define EVAL_QUAL_FOREIGN 2 /* evaluate some in foreign, rest in local */ + +#define OPTIMIZE_WHERE_CLAUSE EVAL_QUAL_FOREIGN + +extern Datum postgresql_fdw_handler(PG_FUNCTION_ARGS); + +/* + * FDW routines + */ +static FSConnection* pgConnectServer(ForeignServer *server, UserMapping *user); +static void pgFreeFSConnection(FSConnection *conn); +static void pgOpen(ForeignScanState *scanstate); +static void pgIterate(ForeignScanState *scanstate); +static void pgClose(ForeignScanState *scanstate); +static void pgReOpen(ForeignScanState *scanstate); + +/* deparse SQL from the request */ +static bool is_immutable_func(Oid funcid); +static bool is_foreign_qual(ExprState *state); +static bool foreign_qual_walker(Node *node, void *context); +static char *deparseSql(ForeignScanState *scanstate); +static int flatten_deflist(List *options, + const char **keywords, const char **values); +static void check_conn_params(const char **keywords, const char **values); + +/* tuple handling */ +static void storeResult(Tuplestorestate *tupstore, bool is_sql_cmd, + TupleDesc tupdesc, PGresult *res); + +/* + * Concrete cursor for a foreign table on a PostgreSQL + */ +typedef struct pgFdwReply +{ + char *sql; /* SQL text sent to foreign server */ + Tuplestorestate *tupstore; /* result set */ +} pgFdwReply; + +FdwRoutine postgresql_fdw_routine = +{ + pgConnectServer, + pgFreeFSConnection, + pgOpen, + pgIterate, + pgClose, + pgReOpen +}; + +/* + * return foreign-data wrapper handler object to execute foreign-data wrapper + * routines. + */ +PG_FUNCTION_INFO_V1(postgresql_fdw_handler); +Datum +postgresql_fdw_handler(PG_FUNCTION_ARGS) +{ + PG_RETURN_POINTER(&postgresql_fdw_routine); +} + +/* + * Connect to foreign PostgreSQL server with libpq. + */ +static FSConnection * +pgConnectServer(ForeignServer *server, UserMapping *user) +{ + PGconn *conn; + const char **keywords; + const char **values; + int n; + + /* + * construct connection params from options of ForeignDataWrapper, + * ForeignServer and UserMapping. Assuming all GENERIC OPTIONS are + * conneciton information. + * + * TODO: allow non-connection options and ignore them during constructing + * connection string. + */ + n = list_length(server->options) + list_length(user->options) + 1; + keywords = (const char **) palloc(sizeof(char *) * n); + values = (const char **) palloc(sizeof(char *) * n); + n = 0; + n += flatten_deflist(server->options, keywords + n, values + n); + n += flatten_deflist(user->options, keywords + n, values + n); + keywords[n] = values[n] = NULL; + + /* verify connection parameters and do connect */ + check_conn_params(keywords, values); + conn = PQconnectdbParams(keywords, values, 0); + pfree(keywords); + pfree(values); + + return (FSConnection *) conn; +} + +/* + * Disconnect from the foreign server and free memory. + * The cache entry of the hash table will be remove by executor. + */ +static void +pgFreeFSConnection(FSConnection *conn) +{ + Assert(conn != NULL); + + PQfinish((PGconn *) conn); +} + +/* + * Check whether the function is IMMUTABLE. + */ +static bool +is_immutable_func(Oid funcid) +{ + HeapTuple tp; + bool isnull; + Datum datum; + + tp = SearchSysCache1(PROCOID, funcid); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for function %u", funcid); + +#ifdef DEBUG_FDW + /* print function name and its immutability */ + { + char *proname; + datum = SysCacheGetAttr(PROCOID, tp, Anum_pg_proc_proname, &isnull); + proname = pstrdup(DatumGetName(datum)->data); + elog(DEBUG1, "func %s(%u) is%s immutable", proname, funcid, + (DatumGetChar(datum) == PROVOLATILE_IMMUTABLE) ? "" : " not"); + pfree(proname); + } +#endif + + datum = SysCacheGetAttr(PROCOID, tp, Anum_pg_proc_provolatile, &isnull); + ReleaseSysCache(tp); + + return (DatumGetChar(datum) == PROVOLATILE_IMMUTABLE); +} + +/* + * Check whether the ExprState node should be evaluated in foreign server. + * + * An expression which consists of expressions below will be evaluated in + * the foreign server. + * - constant value + * - variable (foreign table column) + * - external parameter (parameter of prepared statement) + * - array + * - bool expression (AND/OR/NOT) + * - NULL test (IS [NOT] NULL) + * - operator + * - IMMUTABLE only + * - It is required that the meaning of the operator be the same as the + * local server in the foreign server. + * - function + * - IMMUTABLE only + * - It is required that the meaning of the operator be the same as the + * local server in the foreign server. + * - scalar array operator (ANY/ALL) + */ +static bool +is_foreign_qual(ExprState *state) +{ + return !foreign_qual_walker((Node *) state->expr, NULL); +} + +/* + * return true if node cannot be evaluatated in foreign server. + */ +static bool +foreign_qual_walker(Node *node, void *context) +{ + if (node == NULL) + return false; + + switch (nodeTag(node)) + { + case T_Param: + /* TODO: pass internal parameters to the foreign server */ + if (((Param *) node)->paramkind != PARAM_EXTERN) + return true; + break; + case T_DistinctExpr: + case T_OpExpr: + /* + * An operator which uses IMMUTABLE function can be evaluated in + * foreign server . It is not necessary to worry about oprrest + * and oprjoin here because they are invoked by planner but not + * executor. DistinctExpr is a typedef of OpExpr. + */ + if (!is_immutable_func(((OpExpr*) node)->opfuncid)) + return true; + break; + case T_ScalarArrayOpExpr: + if (!is_immutable_func(((ScalarArrayOpExpr*) node)->opfuncid)) + return true; + break; + case T_FuncExpr: + /* IMMUTABLE function can be evaluated in foreign server */ + if (!is_immutable_func(((FuncExpr*) node)->funcid)) + return true; + break; + case T_TargetEntry: + case T_PlaceHolderVar: + case T_AppendRelInfo: + case T_PlaceHolderInfo: + /* TODO: research whether those complex nodes are evaluatable. */ + return true; + default: + break; + } + + return expression_tree_walker(node, foreign_qual_walker, context); +} + +/* + * Deparse SQL string from query request. + * + * The expressions in Plan.qual are deparsed when it satisfies is_foreign_qual() + * and removed. + */ +static char * +deparseSql(ForeignScanState *scanstate) +{ + EState *estate = scanstate->ss.ps.state; + bool prefix; + List *context; + StringInfoData sql; + ForeignScan *scan; + RangeTblEntry *rte; + ForeignTable *table; + char *nspname = NULL; + char *relname = NULL; + const char *nspname_q; + const char *relname_q; + const char *aliasname_q; + ListCell *lc; + int i; + TupleDesc tupdesc; + bool first; + + /* extract ForeignScan and RangeTblEntry */ + scan = (ForeignScan *)scanstate->ss.ps.plan; + rte = list_nth(estate->es_range_table, scan->scan.scanrelid - 1); + + /* prepare to deparse plan */ + initStringInfo(&sql); + context = deparse_context_for_planstate((Node *)&scanstate->ss.ps, NULL, + estate->es_range_table); + + /* + * Scanning multiple relations in a ForeignScan node is not supported. + */ + prefix = false; +#if 0 + prefix = list_length(estate->es_range_table) > 1; +#endif + + /* The alias of relation is used in both SELECT clause and FROM clause. */ + aliasname_q = quote_identifier(rte->eref->aliasname); + + /* deparse SELECT clause */ + appendStringInfo(&sql, "SELECT "); + + /* + * TODO: omit (deparse to "NULL") columns which are not used in the + * original SQL. + * + * We must parse nodes parents of this ForeignScan node to determine unused + * columns because some columns may be used only in parent Sort/Agg/Limit + * nodes. + */ + tupdesc = scanstate->ss.ss_currentRelation->rd_att; + first = true; + for (i = 0; i < tupdesc->natts; i++) + { + /* skip dropped attributes */ + if (tupdesc->attrs[i]->attisdropped) + continue; + + if (!first) + appendStringInfoString(&sql, ", "); + + if (prefix) + appendStringInfo(&sql, "%s.%s", + aliasname_q, tupdesc->attrs[i]->attname.data); + else + appendStringInfo(&sql, "%s", tupdesc->attrs[i]->attname.data); + first = false; + } + + /* if target list is composed only of system attributes, add dummy column */ + if (first) + appendStringInfo(&sql, "NULL"); + + /* deparse FROM clause */ + appendStringInfo(&sql, " FROM "); + + /* + * If the foreign table has generic option "nspname" and/or "relname", use + * them in the foreign query. Otherwise, use local catalog names. + * Each identifier must be quoted if they are case sensitive. + */ + table = GetForeignTable(rte->relid); + foreach(lc, table->options) + { + DefElem *opt = lfirst(lc); + if (strcmp(opt->defname, "nspname") == 0) + nspname = pstrdup(strVal(opt->arg)); + else if (strcmp(opt->defname, "relname") == 0) + relname = pstrdup(strVal(opt->arg)); + } + if (nspname == NULL) + nspname = get_namespace_name(get_rel_namespace(rte->relid)); + if (relname == NULL) + relname = get_rel_name(rte->relid); + nspname_q = quote_identifier(nspname); + relname_q = quote_identifier(relname); + appendStringInfo(&sql, "%s.%s %s", nspname_q, relname_q, aliasname_q); + pfree(nspname); + pfree(relname); + if (nspname_q != nspname) + pfree((char *) nspname_q); + if (relname_q != relname) + pfree((char * ) relname_q); + if (aliasname_q != rte->eref->aliasname) + pfree((char *) aliasname_q); + + /* + * deparse WHERE cluase + * + * The expressions which satisfy is_foreign_qual() are deparsed into WHERE + * clause of result SQL string, and they could be removed from qual of + * PlanState to avoid duplicate evaluation at ExecScan(). + * + * The Plan.qual is never changed, so multiple use of the Plan with + * PREPARE/EXECUTE work properly. + */ +#if OPTIMIZE_WHERE_CLAUSE > EVAL_QUAL_LOCAL + if (scanstate->ss.ps.plan->qual) + { + List *local_qual = NIL; + List *foreign_qual = NIL; + List *foreign_expr = NIL; + ListCell *lc; + + /* + * Divide qual of PlanState into two lists, one for local evaluation + * and one for foreign evaluation. + */ + foreach (lc, scanstate->ss.ps.qual) + { + ExprState *state = lfirst(lc); + + if (is_foreign_qual(state)) + { + elog(DEBUG1, "foreign qual: %s", nodeToString(state->expr)); + foreign_qual = lappend(foreign_qual, state); + foreign_expr = lappend(foreign_expr, state->expr); + } + else + { + elog(DEBUG1, "local qual: %s", nodeToString(state->expr)); + local_qual = lappend(local_qual, state); + } + } +#if OPTIMIZE_WHERE_CLAUSE == EVAL_QUAL_FOREIGN + /* + * If the optimization level is EVAL_QUAL_FOREIGN, replace the original + * qual with the list of ExprStates which should be evaluated in the + * local server. + */ + scanstate->ss.ps.qual = local_qual; +#endif + + /* + * Deparse quals to be evaluated in the foreign server if any. + * TODO: modify deparse_expression() to deparse conditions which use + * internal parameters. + */ + if (foreign_expr != NIL) + { + Node *node; + node = (Node *) make_ands_explicit(foreign_expr); + appendStringInfo(&sql, " WHERE "); + appendStringInfo(&sql, + deparse_expression(node, context, prefix, false)); + /* + * The contents of the list MUST NOT be free-ed because they are + * referenced from Plan.qual list. + */ + list_free(foreign_expr); + } + } +#endif + + elog(DEBUG1, "deparsed SQL is \"%s\"", sql.data); + + return sql.data; +} + +/* + * pgOpen() + * - deparse SQL statement from ForeignScanState and EState + */ +static void +pgOpen(ForeignScanState *scanstate) +{ + pgFdwReply *reply; + + /* FWD-specific portion */ + reply = (pgFdwReply *) palloc0(sizeof(*reply)); + reply->sql = deparseSql(scanstate); + scanstate->reply = (FdwReply *) reply; +} + +/* + * return tuples one by one. + * - execute SQL statement which was deparsed in pgOpen() + * + * The all of result are fetched at once when pgIterate() is called first after + * pgOpen() or pgReOpen(). + * pgIterate() takes out a tuple from tupstore with tupslot and returns it. + */ +static void +pgIterate(ForeignScanState *scanstate) +{ + pgFdwReply *reply = (pgFdwReply *) scanstate->reply; + TupleTableSlot *slot = scanstate->ss.ss_ScanTupleSlot; + + /* + * Execute query with current parameters. + */ + if (reply->tupstore == NULL) + { + PGconn *conn = (PGconn *) scanstate->conn; + PGresult *res; + ParamListInfo info = scanstate->ss.ps.state->es_param_list_info; + int numParams = info ? info->numParams : 0; + Oid *types = NULL; + const char **values = NULL; + + /* construct parameter array in text format */ + /* TODO: omit unused parameter */ + if (numParams > 0) + { + int i; + + types = palloc0(sizeof(Oid) * numParams); + values = palloc0(sizeof(char *) * numParams); + for (i = 0; i < numParams; i++) + { + types[i] = info->params[i].ptype; + if (info->params[i].isnull) + values[i] = NULL; + else + { + Oid out_func_oid; + bool isvarlena; + FmgrInfo func; + + /* TODO: cache FmgrInfo to use it again after pgReOpen() */ + /* TODO: send parameters in binary format rather than text */ + getTypeOutputInfo(types[i], &out_func_oid, &isvarlena); + fmgr_info(out_func_oid, &func); + values[i] = + OutputFunctionCall(&func, info->params[i].value); + } + } + } + + /* + * Execute query with the parameters. + * TODO: support internal parameters(PARAM_EXTERN) + * TODO: support cursor mode for huge result sets. + */ + res = PQexecParams(conn, reply->sql, + numParams, types, values, NULL, NULL, 0); + if (numParams > 0) + { + int i; + pfree(types); + for (i = 0; i < numParams; i++) + pfree((char *) values[i]); + pfree(values); + } + if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) + { + PQclear(res); + ereport(ERROR, ( + errmsg("could not execute foreign query"), + errdetail("%s", PQerrorMessage(conn)), + errhint("%s", reply->sql))); + } + + /* Note: use PG_TRY to ensure freeing PGresult. */ + PG_TRY(); + { + TupleDesc tupdesc = ExecGetScanType((ScanState *) scanstate); + + /* create tuplestore to store results */ + reply->tupstore = tuplestore_begin_heap(true, false, work_mem); + + storeResult(reply->tupstore, false, tupdesc, res); + + PQclear(res); + } + PG_CATCH(); + { + PQclear(res); + PG_RE_THROW(); + } + PG_END_TRY(); + } + + /* store the next tuple into the slot from the tuplestore */ + if (tuplestore_gettupleslot(reply->tupstore, true, false, slot)) + { + /* + * Because the tuples stored in the tupstore are minimal tuples, + * they have to be materialized to retrieve system attributes. + */ + ExecMaterializeSlot(slot); + } + else + { + /* TODO: if cursor mode, reset tuple slot and fetch the next batch. */ + } +} + +/* + * Finish scanning foreign table and dispose objects used for this scan. + */ +static void +pgClose(ForeignScanState *scanstate) +{ + pgFdwReply *reply = (pgFdwReply *) scanstate->reply; + + if (reply == NULL) + return; + + if (reply->tupstore != NULL) + tuplestore_end(reply->tupstore); + + /* + * reply->conn is not free-ed here because foreign connections are + * managed by executor, not FDW. + */ + pfree(reply); + scanstate->reply = NULL; +} + +/* + * Execute query with new parameter. + */ +static void +pgReOpen(ForeignScanState *scanstate) +{ + pgFdwReply *reply = (pgFdwReply *) scanstate->reply; + + /* Free Tuplestore to execute query again */ + /* TODO: reuse tupstore through the scan to avoid overhead */ + if (reply->tupstore) + { + tuplestore_end(reply->tupstore); + reply->tupstore = NULL; + elog(DEBUG1, "tuplestore disposed"); + } +} + +/* + * Flattern options into keywords and values buffers. + */ +static int +flatten_deflist(List *options, const char **keywords, const char **values) +{ + ListCell *cell; + int n = 0; + + foreach(cell, options) + { + DefElem *def = lfirst(cell); + + keywords[n] = def->defname; + values[n] = strVal(def->arg); + n++; + } + return n; +} + +/* + * For non-superusers, insist that the connstr specify a password. This + * prevents a password from being picked up from .pgpass, a service file, + * the environment, etc. We don't want the postgres user's passwords + * to be accessible to non-superusers. + */ +static void +check_conn_params(const char **keywords, const char **values) +{ + int i; + + /* no check required if superuser */ + if (superuser()) + return; + + /* ok if params contain a non-empty password */ + for (i = 0; keywords[i] != NULL; i++) + { + if (strcmp(keywords[i], "password") == 0 && values[i][0] != '\0') + return; + } + + ereport(ERROR, + (errcode(ERRCODE_S_R_E_PROHIBITED_SQL_STATEMENT_ATTEMPTED), + errmsg("password is required"), + errdetail("Non-superusers must provide a password in the connection string."))); +} + +/* + * Store a PGresult into tuplestore. + */ +static void +storeResult(Tuplestorestate *tupstore, + bool is_sql_cmd, + TupleDesc tupdesc, + PGresult *res) +{ + int i; + int row; + int ntuples; + int nfields; + int attnum; /* number of non-dropped columns */ + char **values; + AttInMetadata *attinmeta; + Form_pg_attribute *attrs; + + ntuples = PQntuples(res); + nfields = is_sql_cmd ? 1 : PQnfields(res); + attrs = tupdesc->attrs; + + /* count non-dropped columns */ + for (attnum = 0, i = 0; i < tupdesc->natts; i++) + if (!attrs[i]->attisdropped) + attnum++; + + /* check result and tuple descriptor have the same number of columns */ + if (attnum > 0 && attnum != nfields) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("remote query result rowtype does not match " + "the specified FROM clause rowtype"))); + + /* buffer should include dropped columns */ + values = palloc(sizeof(char *) * tupdesc->natts); + + /* put all tuples into the tuplestore */ + attinmeta = TupleDescGetAttInMetadata(tupdesc); + for (row = 0; row < ntuples; row++) + { + int j; + HeapTuple tuple; + + if (!is_sql_cmd) + { + for (i = 0, j = 0; i < tupdesc->natts; i++) + { + /* skip dropped columns. */ + if (attrs[i]->attisdropped) + { + values[i] = NULL; + continue; + } + + if (PQgetisnull(res, row, j)) + values[i] = NULL; + else + values[i] = PQgetvalue(res, row, j); + j++; + } + } + else + { + values[0] = PQcmdStatus(res); + } + + /* build the tuple and put it into the tuplestore. */ + tuple = BuildTupleFromCStrings(attinmeta, values); + tuplestore_puttuple(tupstore, tuple); + } + + /* clean up and return the tuplestore */ + tuplestore_donestoring(tupstore); + pfree(values); +} diff --git a/contrib/postgresql_fdw/postgresql_fdw.h b/contrib/postgresql_fdw/postgresql_fdw.h new file mode 100644 index 0000000000..0af367cafa --- /dev/null +++ b/contrib/postgresql_fdw/postgresql_fdw.h @@ -0,0 +1,18 @@ +/* + * postgresql_fdw.h + * + * Foreign-data wrapper handler for PostgreSQL + * + * contrib/postgresql_fdw/postgresql_fdw.h + * Copyright (c) 2010, PostgreSQL Global Development Group + * ALL RIGHTS RESERVED; + * + */ + +#ifndef POSTGRESQL_FDW_H +#define POSTGRESQL_FDW_H + +/* Connection name used for unnamed connection */ +#define UNNAMED_CONN_NAME "unnamed" + +#endif /* POSTGRESQL_FDW_H */ diff --git a/contrib/postgresql_fdw/postgresql_fdw.sql.in b/contrib/postgresql_fdw/postgresql_fdw.sql.in new file mode 100644 index 0000000000..1f784e110d --- /dev/null +++ b/contrib/postgresql_fdw/postgresql_fdw.sql.in @@ -0,0 +1,10 @@ +/* contrib/postgresql/postgresql.sql.in */ + +-- Adjust this setting to control where the objects get created. +set search_path = public; + +CREATE OR REPLACE FUNCTION postgresql_fdw_handler () +RETURNS fdw_handler +AS 'MODULE_PATHNAME','postgresql_fdw_handler' +LANGUAGE C STRICT; + diff --git a/contrib/postgresql_fdw/sql/postgresql_fdw.sql b/contrib/postgresql_fdw/sql/postgresql_fdw.sql new file mode 100644 index 0000000000..3c3245602a --- /dev/null +++ b/contrib/postgresql_fdw/sql/postgresql_fdw.sql @@ -0,0 +1,286 @@ +SET SEARCH_PATH = public; +SET DATESTYLE = 'Postgres, MDY'; + +-- ============================================================================= +-- Prepare section +-- ============================================================================= +-- connect database for regression test +\c contrib_regression + +-- install handler function +\i postgresql_fdw.sql + +-- create test user +CREATE ROLE contrib_regression_role_1; + +-- create remote database 1 +CREATE DATABASE contrib_regression_f1; +\c contrib_regression_f1 +CREATE TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person primary key (id) +); +BEGIN; +INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person VALUES(3, 'buz', NULL, NULL); +COMMIT; + +-- create remote database 2 +CREATE DATABASE contrib_regression_f2; +\c contrib_regression_f2 +CREATE TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person primary key (id) +); +BEGIN; +INSERT INTO person VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person VALUES(3, 'buz', NULL, NULL); +COMMIT; + +-- connect database for regression test +\c contrib_regression + +-- create FOREIGN DATA WRAPPER for PostgresSQL +CREATE FOREIGN DATA WRAPPER contrib_regression_wrapper + HANDLER postgresql_fdw_handler + VALIDATOR postgresql_fdw_validator; + +-- create FOREIGN SERVER for remote database 1 +CREATE SERVER contrib_regression_srv_1 + FOREIGN DATA WRAPPER contrib_regression_wrapper + OPTIONS (host 'localhost', dbname 'contrib_regression_f1'); +CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_1; + +-- create FOREIGN SERVER for remote database 2 +CREATE SERVER contrib_regression_srv_2 + FOREIGN DATA WRAPPER contrib_regression_wrapper + OPTIONS (host 'localhost', dbname 'contrib_regression_f2'); +CREATE USER MAPPING FOR PUBLIC SERVER contrib_regression_srv_2; + +-- Check ALTER FOREIGN TABLE OWNER TO before create various test tables +CREATE FOREIGN TABLE ft1 (c1 integer) SERVER contrib_regression_srv_2; +ALTER FOREIGN TABLE ft1 OWNER TO contrib_regression_role_1; +\d +DROP FOREIGN TABLE ft1; + +-- create entity of local table with same contents +CREATE TABLE person_l ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone, + constraint pk_person_l primary key (id) +); +BEGIN; +INSERT INTO person_l VALUES(1, 'foo', '01-31-2000', '01-31-2000 00:00:00+00:00'); +INSERT INTO person_l VALUES(2, 'bar', '01-31-1900', '01-31-1900 00:00:00+00:00'); +INSERT INTO person_l VALUES(4, 'bar', NULL, NULL); +COMMIT; + +-- create foreign table which references table 'person' +CREATE FOREIGN TABLE person ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone +) SERVER contrib_regression_srv_1; +CREATE FOREIGN TABLE person2 ( + id integer not null, + name text not null, + birthday date, + update_ts timestamp with time zone +) SERVER contrib_regression_srv_2 OPTIONS (nspname 'public', relname 'person'); +\det+ + +-- ============================================================================= +-- Misc statement section +-- ============================================================================= +ALTER FOREIGN TABLE person INHERIT person_l; +-- row lock via view is not allowed too. +CREATE OR REPLACE VIEW v_person AS SELECT * FROM person; +SELECT * FROM v_person FOR UPDATE NOWAIT; +DROP VIEW v_person; +-- row lock via CTE is not allowed but no error occurs. +WITH t AS (SELECT * FROM person) SELECT * FROM t ORDER BY id FOR UPDATE NOWAIT; -- not error +-- row lock in CTE is not allowed and an error occurs. +WITH t AS (SELECT * FROM person FOR UPDATE) SELECT * FROM t ORDER BY id; -- error + +-- cleanup +ALTER FOREIGN TABLE person NO INHERIT person_l; + +-- ============================================================================= +-- Connection cache test section +-- XXX: some of these tests should been moved to regression test of core. +-- ============================================================================= +-- clear connection cache +DISCARD ALL; + +-- access foreign table (no result needed) +SELECT * FROM person WHERE 1 = 0; + +-- one connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + +-- access remote database 2 (no result needed) +SELECT id, name, birthday FROM person2 WHERE 1 = 0; + +-- two connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + +-- disconnect from all servers +DISCARD ALL; + +-- connection cache must be empty +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + +-- access remote database 1 and 2 (no result needed) +SELECT id, name, birthday FROM person WHERE 1 = 0; +SELECT id, name, birthday FROM person2 WHERE 1 = 0; + +-- two connection must be cached +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + +-- change authorization identifier +SET SESSION AUTHORIZATION contrib_regression_role_1; + +-- connection cache must be empty +SELECT srvname, (user = usename) usename, fdwname + FROM pg_foreign_connections ORDER BY srvname; + +-- cleanup +RESET SESSION AUTHORIZATION; +DROP FOREIGN TABLE person2; +\det+ + +-- ============================================================================= +-- Query test section +-- ============================================================================= +-- all tuples with ORDER BY clause +SELECT id, name, birthday, + xmin, xmax, cmin, cmax, ctid, (tableoid = 'person'::regclass) tableoid + FROM person ORDER BY id; + +-- operator and function call in SELECT clause +SELECT id + 10 id, upper(name) upper_name, birthday FROM person ORDER BY id; + +-- various join/subquery situations +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f LEFT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f RIGHT JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; +SELECT f.id, f.name, f.birthday, p.id, p.name, p.birthday FROM person f FULL OUTER JOIN person_l p ON (f.id = p.id) ORDER BY f.id, p.id; +SELECT id, name, birthday FROM person f WHERE f.id = (SELECT min(p.id) FROM person_l p) ORDER BY f.id; +SELECT id, name, birthday FROM person f WHERE (f.id, f.name) IN (SELECT p.id, p.name FROM person_l p) ORDER BY f.id; + +-- union/intersect/except +SELECT id, name, birthday FROM person f UNION SELECT id, name, birthday FROM person_l p ORDER BY id; +SELECT name FROM person f INTERSECT SELECT name FROM person_l p ORDER BY name; +SELECT name FROM person f EXCEPT SELECT name FROM person_l p ORDER BY name; + +-- WHERE clause evaluation in the foreign server +SELECT id, name, birthday FROM person WHERE id = 1; +SELECT id, name, birthday FROM person WHERE birthday IS NULL ORDER BY id; +SELECT id, name, birthday FROM person WHERE id IN (1, -1, 5) ORDER BY id; +SELECT id, name, birthday FROM person WHERE id IS DISTINCT from 1 ORDER BY id; +SELECT id, name, birthday FROM person WHERE ARRAY[0,id,2] = ARRAY[0,1,2] ORDER BY id; + +-- WHERE clause evaluation in local server +SELECT id, name, birthday FROM person WHERE update_ts < '01-31-1999'::date ORDER BY id; + +-- limit/offset +SELECT id, name, birthday FROM person f ORDER BY id LIMIT 1 OFFSET 1; + +-- PREPARE/EXECUTE +PREPARE ST1(integer, integer) AS SELECT $1 param, id, name, birthday FROM person f WHERE f.id = $2; +EXECUTE ST1(0, 1); +EXECUTE ST1(1, 2); +DEALLOCATE ST1; + +-- ============================================================================= +-- DDL test section +-- ============================================================================= +-- NOT NULL and CHECK constraints in column constraint syntax +CREATE FOREIGN TABLE ft2 (c1 integer NOT NULL, c2 text CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1; +\d+ ft2 + +-- CHECK constraints in table constraint syntax +CREATE FOREIGN TABLE ft3 (c1 integer, c2 text, CONSTRAINT ft3_c2_check CHECK (length(c2) > 0)) SERVER contrib_regression_srv_1; +\d+ ft3 + +-- PRIMARY KEY in column constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer PRIMARY KEY) SERVER contrib_regression_srv_1; -- error + +-- UNIQUE in column constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer UNIQUE) SERVER contrib_regression_srv_1; -- error + +-- FOREIGN KEY in column constraint syntax - error +CREATE TABLE t1 (c1 integer PRIMARY KEY); +CREATE FOREIGN TABLE ft4 (c1 integer REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error + +-- PRIMARY KEY in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_pkey PRIMARY KEY (c1)) SERVER contrib_regression_srv_1; -- error + +-- UNIQUE in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_unique UNIQUE (c1)) SERVER contrib_regression_srv_1; -- error + +-- FOREIGN KEY in table constraint syntax - error +CREATE FOREIGN TABLE ft4 (c1 integer, CONSTRAINT ft4_c1_fkey FOREIGN KEY (c1) REFERENCES t1 (c1)) SERVER contrib_regression_srv_1; -- error + +-- cleanup +DROP FOREIGN TABLE ft2; +DROP FOREIGN TABLE ft3; +DROP TABLE t1; + +-- delete all data to use as inherited (parent) table +DELETE FROM person_l; +-- change table definition +\c contrib_regression_f1 +ALTER TABLE person ADD COLUMN dummy text; +UPDATE person SET dummy = 'dummy value'; +\c contrib_regression +ALTER FOREIGN TABLE person INHERIT person_l; +ALTER TABLE person_l ADD COLUMN dummy text; -- added to person too +CREATE RULE rl_person_insert AS ON INSERT TO person DO INSTEAD INSERT INTO person_l VALUES (NEW.*); +\d+ person; +\d+ person_l; +-- content of person must be showed +INSERT INTO person VALUES (-1, 'FOO', '2100-01-31', null, 'DUMMY'); +SELECT * FROM person_l ORDER BY id; + +-- restore table definition +\c contrib_regression_f1 +ALTER TABLE person DROP COLUMN dummy; +\c contrib_regression +DROP RULE rl_person_insert ON person; +ALTER TABLE person_l DROP COLUMN dummy; +ALTER FOREIGN TABLE person NO INHERIT person_l; +\d+ person; +-- no child table, result must be its own content +SELECT * FROM person_l ORDER BY id; + +-- query for foreign table which has no column +ALTER FOREIGN TABLE person DROP COLUMN id; +ALTER FOREIGN TABLE person DROP COLUMN name; +ALTER FOREIGN TABLE person DROP COLUMN birthday; +ALTER FOREIGN TABLE person DROP COLUMN update_ts; +SELECT * FROM person; + +-- ============================================================================= +-- Cleanup section +-- ============================================================================= +DISCARD ALL; +DROP FOREIGN DATA WRAPPER contrib_regression_wrapper CASCADE; +DROP DATABASE contrib_regression_f1; +DROP DATABASE contrib_regression_f2; +DROP ROLE contrib_regression_role_1; diff --git a/contrib/postgresql_fdw/uninstall_postgresql_fdw.sql b/contrib/postgresql_fdw/uninstall_postgresql_fdw.sql new file mode 100644 index 0000000000..7052d8d477 --- /dev/null +++ b/contrib/postgresql_fdw/uninstall_postgresql_fdw.sql @@ -0,0 +1,7 @@ +/* contrib/postgresql_fdw/uninstall_postgresql_fdw.sql.in */ + +-- Adjust this setting to control where the objects get dropped. +set search_path = public; + +DROP FUNCTION postgresql_fdw_handler (); + diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 3eeecb9385..3c6265a094 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2724,6 +2724,17 @@ + + fdwhandler + oid + pg_proc.oid + + References a handler function that is responsible for + supplying foreign-data wrapper routines. + Zero if no handler is provided. + + + fdwacl aclitem[] @@ -5984,6 +5995,11 @@ open cursors + + pg_foreign_connections + established foreign connections + + pg_group groups of database users @@ -6189,6 +6205,89 @@ + + <structname>pg_foreign_connections</structname> + + + pg_foreign_connections + + + + The pg_foreign_connections view lists + the foreign connections that are currently available. + Foreign connections are established in several ways: + + + + via the query for a foreign table + + + + + + via the use of dblink functions, see + + + + + The pg_foreign_connections view displays + foreign connections created by any of these means. + Foreign connections only exist for the duration + of the backend lifetime which established the connection, + and cannot be used from other backends. + Use DISCARD ALL command to close all foreign + connections. + + + + <structname>pg_foreign_connections</> Columns + + + + + Name + Type + Description + + + + + + conname + text + The name of the connection + + + + srvname + name + The name of the foreign server which was used to + establish the connection + + + + usename + name + The name of the local user which was used to + establish the connection + + + + fdwname + name + The name of the foreign-data wrapper which was + used to establish the connection + + + +
+ + + The pg_cursors view is read only. + + + + <structname>pg_group</structname> diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index c31041614b..0d393d7c4c 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -112,6 +112,7 @@ psql -d dbname -f SHAREDIR/contrib/module.sql &pgstattuple; &pgtrgm; &pgupgrade; + &postgresql-fdw; &seg; &contrib-spi; &sslinfo; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 9b1de856b0..52322955cc 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -124,6 +124,7 @@ + diff --git a/doc/src/sgml/postgresql-fdw.sgml b/doc/src/sgml/postgresql-fdw.sgml new file mode 100644 index 0000000000..2855e2f2ce --- /dev/null +++ b/doc/src/sgml/postgresql-fdw.sgml @@ -0,0 +1,140 @@ + + + + postgresql_fdw + + + postgresql_fdw + + + + The postgresql_fdw module provides foreign-data wrapper + handler function postgresql_fdw_handler which can be + used to access external PostgreSQL server via plain SQL. + + + + Functions + + + + + postgresql_fdw_handler() returns fdw_handler + + + + + postgresql_fdw_handler is a foreign-data wrapper + handler function which returns foreign-data wrapper handler for + PostgreSQL in type of fdw_handler. + Since fdw_hanlder is a pseudo type, postgresql_fdw_handler can't be + called from a SQL statement. + + + Internally, it returns a pointer to a FdwRoutine + object which has set of foreign-data wrapper API functions for handling + foreign scan on the external PostgreSQL server. + + + + + + + + + Details of postgresql_fdw + + + Connection options + + The postgresql_fdw retrieves connection information from generic options of + user mapping and foriegn server. All of generic options of these objects + are passed to PQconnectdbParams(). + + + Currently, all of the generic options which are allowed in the context of + user mapping and foreign server are libpq connection options. + + + + + Transaction management + + The postgresql_fdw never emit transaction command such as BEGIN, + ROLLBACK and COMMIT. Thus, all SQL statements are + executed in each transaction when 'autocommit' was set to 'on'. + + + + + Retrieving all tuples at once + + The postgresql_fdw retrieves all of the result tuples at once via libpq + when the query was executed. Note that huge result set causes huge memory + consumption. The memory for the result set will be freed at the end of the + each query. + + + + + WHERE clause push-down + + The postgresql_fdw pushes some part of WHERE clause down to the remote + server, only if the evaluating the part of clause doesn't break the + consistency of the query. If a clause consist of elements below, the + clause will be pushed down. + + + push-down-able elements + + + + Element + Note + + + + + Constant value and column reference + + + + Array of push-down-able type + + + + Parameter of EXECUTE + + + + Bool expression such as A AND B or + A OR B + + + + Immutable operator + + + + DISTINCT operator, such as + A IS DISTINCT FROM B + + + + Scalar array operator, such as ALL(...) and + ANY(...) + + + + Immutable function call + + + + +
+ + + + + + diff --git a/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml b/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml index 4e9e8a2e28..b9f7b48dcc 100644 --- a/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml +++ b/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation ALTER FOREIGN DATA WRAPPER name [ VALIDATOR valfunction | NO VALIDATOR ] + [ HANDLER handler | NO HANDLER ] [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ] ALTER FOREIGN DATA WRAPPER name OWNER TO new_owner @@ -85,6 +86,30 @@ ALTER FOREIGN DATA WRAPPER name OWN + + HANDLER handler + + + Specifies a new foreign-data wrapper handler function. + + + + + + NO HANDLER + + + This is used to specify that the foreign-data wrapper should no + longer have a handler function. + + + Note that it is not allowed to remove handler of a foreign-data wrapper + which has any foreign table. Therefore, it is necessary to drop all + foreign tables which use the foreign-data wrapper. + + + + OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) @@ -127,8 +152,8 @@ ALTER FOREIGN DATA WRAPPER dbi VALIDATOR bob.myvalidator; ALTER FOREIGN DATA WRAPPER conforms to ISO/IEC 9075-9 (SQL/MED). The standard does not specify the - VALIDATOR and OWNER TO variants of the - command. + VALIDATOR, HANDLER and OWNER TO + variants of the command. diff --git a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml index f626d56665..29816d7c0b 100644 --- a/doc/src/sgml/ref/create_foreign_data_wrapper.sgml +++ b/doc/src/sgml/ref/create_foreign_data_wrapper.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE FOREIGN DATA WRAPPER name [ VALIDATOR valfunction | NO VALIDATOR ] + [ HANDLER handler | NO HANDLER ] [ OPTIONS ( option 'value' [, ... ] ) ] @@ -81,6 +82,21 @@ CREATE FOREIGN DATA WRAPPER name + + HANDLER handler + + + handler is the + name of a previously registered function that will be called to + retrieve a set of functions for foreign tables. + If no handler function or NO HANDLER is specified, + then the forign-data wrapper cannot be used for CREATE FOREIGN + TABLE. The validator function must take no arguments. + The return type must be fdw_handler. + + + + OPTIONS ( option 'value' [, ... ] ) @@ -151,8 +167,8 @@ CREATE FOREIGN DATA WRAPPER mywrapper CREATE FOREIGN DATA WRAPPER conforms to ISO/IEC - 9075-9 (SQL/MED), with the exception that - the VALIDATOR clause is an extension and the + 9075-9 (SQL/MED), with the exception that the VALIDATOR + and HANDLER clauses are extensions and the clauses LIBRARY and LANGUAGE are not yet implemented in PostgreSQL. diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index d012298fdd..ac6e2e868e 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -105,6 +105,10 @@ CHECK ( expression ) constraint only affects one column. + + To create a foreign table, the foreign-data wrapper of the foreign + server must have handler function. + diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index baaf31ad8a..ed65275ec9 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -108,7 +108,8 @@ LOCK [ TABLE ] [ ONLY ] name [, ... name - The name (optionally schema-qualified) of an existing table to + The name (optionally schema-qualified) of an existing table + or a existing foreign table to lock. If ONLY is specified, only that table is locked. If ONLY is not specified, the table and all its descendant tables (if any) are locked. diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index e840070873..10bd434f38 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -191,6 +191,8 @@ TABLE { [ ONLY ] table_name [ * ] | or FOR SHARE requires UPDATE privilege as well (for at least one column of each table so selected). + So you cannot lock contents of a foreign table because only SELECT + privilege can be granted on foreign tables. diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 09574c3e82..f0366b6c9c 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -288,6 +288,16 @@ CREATE VIEW pg_timezone_abbrevs AS CREATE VIEW pg_timezone_names AS SELECT * FROM pg_timezone_names(); +CREATE VIEW pg_foreign_connections AS + SELECT C.connection_name AS conname, + S.srvname AS srvname, + U.rolname AS usename, + D.fdwname AS fdwname + FROM pg_foreign_connections() AS C + LEFT JOIN pg_authid U ON C.userid = U.oid + LEFT JOIN pg_foreign_server S ON C.serverid = S.oid + LEFT JOIN pg_foreign_data_wrapper D ON S.srvfdw = D.oid; + -- Statistics views CREATE VIEW pg_stat_all_tables AS diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index 812384e349..14b66dd35b 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -187,7 +187,7 @@ analyze_rel(Oid relid, VacuumStmt *vacstmt, /* No need for a WARNING if we already complained during VACUUM */ if (!(vacstmt->options & VACOPT_VACUUM)) ereport(WARNING, - (errmsg("skipping \"%s\" --- cannot analyze indexes, views, or special system tables", + (errmsg("skipping \"%s\" --- cannot analyze indexes, views, foreign tables or special system tables", RelationGetRelationName(onerel)))); relation_close(onerel, ShareUpdateExclusiveLock); return; diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c index de8d75bce2..33130b4502 100644 --- a/src/backend/commands/discard.c +++ b/src/backend/commands/discard.c @@ -69,4 +69,5 @@ DiscardAll(bool isTopLevel) LockReleaseAll(USER_LOCKMETHOD, true); ResetPlanCache(); ResetTempTableNamespace(); + DisconnectAllForeignServers(); } diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index b66caa4c08..83b203d9fc 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -697,6 +697,9 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_WorkTableScan: pname = sname = "WorkTable Scan"; break; + case T_ForeignScan: + pname = sname = "Foreign Scan"; + break; case T_Material: pname = sname = "Materialize"; break; @@ -846,6 +849,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_ValuesScan: case T_CteScan: case T_WorkTableScan: + case T_ForeignScan: ExplainScanTarget((Scan *) plan, es); break; case T_BitmapIndexScan: @@ -1023,6 +1027,7 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_ValuesScan: case T_CteScan: case T_WorkTableScan: + case T_ForeignScan: case T_SubqueryScan: show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); break; @@ -1525,6 +1530,7 @@ ExplainScanTarget(Scan *plan, ExplainState *es) case T_IndexScan: case T_BitmapHeapScan: case T_TidScan: + case T_ForeignScan: /* Assert it's on a real relation */ Assert(rte->rtekind == RTE_RELATION); objectname = get_rel_name(rte->relid); diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c index ea39403b9a..d029b511f6 100644 --- a/src/backend/commands/foreigncmds.c +++ b/src/backend/commands/foreigncmds.c @@ -317,16 +317,69 @@ AlterForeignServerOwner(const char *name, Oid newOwnerId) * Convert a validator function name passed from the parser to an Oid. */ static Oid -lookup_fdw_validator_func(List *validator) +lookup_fdw_validator_func(DefElem *validator) { Oid funcargtypes[2]; + if (validator == NULL || validator->arg == NULL) + return InvalidOid; + funcargtypes[0] = TEXTARRAYOID; funcargtypes[1] = OIDOID; - return LookupFuncName(validator, 2, funcargtypes, false); + return LookupFuncName((List *) validator->arg, 2, funcargtypes, false); /* return value is ignored, so we don't check the type */ } +static Oid +lookup_fdw_handler_func(DefElem *handler) +{ + Oid handlerOid; + + if (handler == NULL || handler->arg == NULL) + return InvalidOid; + + /* check that handler have correct return type */ + handlerOid = LookupFuncName((List *) handler->arg, 0, NULL, false); + if (get_func_rettype(handlerOid) != FDW_HANDLEROID) + { + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("function %s must return type \"fdw_handler\"", + NameListToString((List *) handler->arg)))); + } + + return handlerOid; +} + +static void +parse_fdw_options(List *fdw_options, DefElem **validator, DefElem **handler) +{ + ListCell *cell; + + *validator = NULL; + *handler = NULL; + foreach (cell, fdw_options) + { + DefElem *def = lfirst(cell); + + if (pg_strcasecmp(def->defname, "validator") == 0) + { + if (*validator) + elog(ERROR, "duplicated VALIDATOR"); + *validator = def; + } + else if (pg_strcasecmp(def->defname, "handler") == 0) + { + if (*handler) + elog(ERROR, "duplicated HANDLER"); + *handler = def; + } + else + { + elog(ERROR, "invalid option"); + } + } +} /* * Create a foreign-data wrapper @@ -339,7 +392,10 @@ CreateForeignDataWrapper(CreateFdwStmt *stmt) bool nulls[Natts_pg_foreign_data_wrapper]; HeapTuple tuple; Oid fdwId; + DefElem *defvalidator; + DefElem *defhandler; Oid fdwvalidator; + Oid fdwhandler; Datum fdwoptions; Oid ownerId; @@ -375,18 +431,19 @@ CreateForeignDataWrapper(CreateFdwStmt *stmt) DirectFunctionCall1(namein, CStringGetDatum(stmt->fdwname)); values[Anum_pg_foreign_data_wrapper_fdwowner - 1] = ObjectIdGetDatum(ownerId); - if (stmt->validator) - fdwvalidator = lookup_fdw_validator_func(stmt->validator); - else - fdwvalidator = InvalidOid; + /* determin which validator to be used (or not used at all) */ + parse_fdw_options(stmt->fdw_options, &defvalidator, &defhandler); + fdwvalidator = lookup_fdw_validator_func(defvalidator); + fdwhandler = lookup_fdw_handler_func(defhandler); values[Anum_pg_foreign_data_wrapper_fdwvalidator - 1] = fdwvalidator; + values[Anum_pg_foreign_data_wrapper_fdwhandler - 1] = fdwhandler; nulls[Anum_pg_foreign_data_wrapper_fdwacl - 1] = true; fdwoptions = transformGenericOptions(ForeignDataWrapperRelationId, PointerGetDatum(NULL), - stmt->options, + stmt->gen_options, fdwvalidator); if (PointerIsValid(DatumGetPointer(fdwoptions))) @@ -401,7 +458,7 @@ CreateForeignDataWrapper(CreateFdwStmt *stmt) heap_freetuple(tuple); - if (fdwvalidator) + if (fdwvalidator != InvalidOid) { ObjectAddress myself; ObjectAddress referenced; @@ -416,12 +473,87 @@ CreateForeignDataWrapper(CreateFdwStmt *stmt) recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); } + if (fdwhandler != InvalidOid) + { + ObjectAddress myself; + ObjectAddress referenced; + + myself.classId = ForeignDataWrapperRelationId; + myself.objectId = fdwId; + myself.objectSubId = 0; + + referenced.classId = ProcedureRelationId; + referenced.objectId = fdwhandler; + referenced.objectSubId = 0; + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + } + recordDependencyOnOwner(ForeignDataWrapperRelationId, fdwId, ownerId); heap_close(rel, NoLock); } +/* + * Check whether the foreign-data wrapper have any foreign table. + * + * pg_foreign_table and pg_foreign_server are accessed via heap_xxx() because + * they have no suitable index. + */ +static bool +have_foreign_table(Oid fdwId) +{ + bool found = false; + Relation srvrel; + Relation ftrel; + ScanKeyData srvkey[1]; + HeapScanDesc srvscan; + HeapTuple srvtup; + + /* + * Open pg_foreign_server and search tuples which uses the foreign-data + * wrapper identified with fdwId. + */ + srvrel = heap_open(ForeignServerRelationId, AccessShareLock); + ftrel = heap_open(ForeignTableRelationId, AccessShareLock); + ScanKeyInit(&srvkey[0], + Anum_pg_foreign_server_srvfdw, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(fdwId)); + srvscan = heap_beginscan(srvrel, SnapshotNow, 1, srvkey); + + while (HeapTupleIsValid(srvtup = heap_getnext(srvscan, ForwardScanDirection))) + { + ScanKeyData ftkey[1]; + HeapScanDesc ftscan; + HeapTuple fttup; + + ScanKeyInit(&ftkey[0], + Anum_pg_foreign_table_ftserver, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(HeapTupleGetOid(srvtup))); + ftscan = heap_beginscan(ftrel, SnapshotNow, 1, ftkey); + + /* only one valid tuple can finish this check */ + if (HeapTupleIsValid(fttup = heap_getnext(ftscan, ForwardScanDirection))) + { + found = true; + heap_endscan(ftscan); + break; + } + + heap_endscan(ftscan); + } + + /* cleanup */ + heap_endscan(srvscan); + heap_close(srvrel, AccessShareLock); + heap_close(ftrel, AccessShareLock); + + return found; +} + + /* * Alter foreign-data wrapper */ @@ -436,7 +568,10 @@ AlterForeignDataWrapper(AlterFdwStmt *stmt) Oid fdwId; bool isnull; Datum datum; + DefElem *defvalidator; + DefElem *defhandler; Oid fdwvalidator; + Oid fdwhandler; /* Must be super user */ if (!superuser()) @@ -460,9 +595,11 @@ AlterForeignDataWrapper(AlterFdwStmt *stmt) memset(repl_null, false, sizeof(repl_null)); memset(repl_repl, false, sizeof(repl_repl)); - if (stmt->change_validator) + parse_fdw_options(stmt->fdw_options, &defvalidator, &defhandler); + + if (defvalidator) { - fdwvalidator = stmt->validator ? lookup_fdw_validator_func(stmt->validator) : InvalidOid; + fdwvalidator = lookup_fdw_validator_func(defvalidator); repl_val[Anum_pg_foreign_data_wrapper_fdwvalidator - 1] = ObjectIdGetDatum(fdwvalidator); repl_repl[Anum_pg_foreign_data_wrapper_fdwvalidator - 1] = true; @@ -470,7 +607,7 @@ AlterForeignDataWrapper(AlterFdwStmt *stmt) * It could be that the options for the FDW, SERVER and USER MAPPING * are no longer valid with the new validator. Warn about this. */ - if (stmt->validator) + if (defvalidator->arg) ereport(WARNING, (errmsg("changing the foreign-data wrapper validator can cause " "the options for dependent objects to become invalid"))); @@ -488,10 +625,37 @@ AlterForeignDataWrapper(AlterFdwStmt *stmt) fdwvalidator = DatumGetObjectId(datum); } + if (defhandler) + { + fdwhandler = lookup_fdw_handler_func(defhandler); + repl_val[Anum_pg_foreign_data_wrapper_fdwhandler - 1] = ObjectIdGetDatum(fdwhandler); + repl_repl[Anum_pg_foreign_data_wrapper_fdwhandler - 1] = true; + + /* + * Foreign-data wrapper with "NO HANDLER" cannot have foreign table. + * If any foreign table uses this foreign-data wrapper, make this + * statement fail. + */ + if (defhandler->arg == NULL && have_foreign_table(fdwId)) + elog(ERROR, "cannot unset handler because foreign table depend on it"); + } + else + { + /* + * Validator is not changed, but we need it for validating options. + */ + datum = SysCacheGetAttr(FOREIGNDATAWRAPPEROID, + tp, + Anum_pg_foreign_data_wrapper_fdwhandler, + &isnull); + Assert(!isnull); + fdwhandler = DatumGetObjectId(datum); + } + /* * Options specified, validate and update. */ - if (stmt->options) + if (stmt->gen_options) { /* Extract the current options */ datum = SysCacheGetAttr(FOREIGNDATAWRAPPEROID, @@ -504,7 +668,7 @@ AlterForeignDataWrapper(AlterFdwStmt *stmt) /* Transform the options */ datum = transformGenericOptions(ForeignDataWrapperRelationId, datum, - stmt->options, + stmt->gen_options, fdwvalidator); if (PointerIsValid(DatumGetPointer(datum))) @@ -1187,6 +1351,17 @@ CreateForeignTable(CreateForeignTableStmt *stmt, Oid relid) fdw = GetForeignDataWrapper(server->fdwid); + aclresult = pg_foreign_data_wrapper_aclcheck(fdw->fdwid, ownerId, ACL_USAGE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_FDW, fdw->fdwname); + + /* + * Check that the foreign-data wrapper have valid handler to access this + * foreign table. + */ + if (fdw->fdwhandler == InvalidOid) + elog(ERROR, "foreign-data wrapper handler is required to create foreign table"); + /* * Insert tuple into pg_foreign_table. */ diff --git a/src/backend/commands/lockcmds.c b/src/backend/commands/lockcmds.c index 35fc1b3880..5b4d5e26c0 100644 --- a/src/backend/commands/lockcmds.c +++ b/src/backend/commands/lockcmds.c @@ -142,11 +142,12 @@ LockTableRecurse(Oid reloid, RangeVar *rv, aclcheck_error(aclresult, ACL_KIND_CLASS, RelationGetRelationName(rel)); - /* Currently, we only allow plain tables to be locked */ - if (rel->rd_rel->relkind != RELKIND_RELATION) + /* Currently, we only allow plain tables and foreign tables to be locked */ + if (rel->rd_rel->relkind != RELKIND_RELATION && + rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not a table", + errmsg("\"%s\" is not a table or foreign table", RelationGetRelationName(rel)))); /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 9ff0850599..c76b3c699f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -48,7 +48,6 @@ #include "commands/trigger.h" #include "commands/typecmds.h" #include "executor/executor.h" -#include "foreign/foreign.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -271,8 +270,7 @@ static void ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, static void ATRewriteTables(List **wqueue, LOCKMODE lockmode); static void ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode); static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel); -static void ATSimplePermissions(Relation rel, bool allowView, bool allowType, - bool allowForeignTable); +static void ATSimplePermissions(Relation rel, bool allowView, bool allowType, bool allowForeignTable); static void ATSimplePermissionsRelationOrIndex(Relation rel); static void ATSimpleRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode); @@ -3570,41 +3568,42 @@ ATGetQueueEntry(List **wqueue, Relation rel) /* * ATSimplePermissions * - * - Ensure that it is a relation (or possibly a view or composite type or - * foreign table) + * - Ensure that it is a relation (or possibly a view) * - Ensure this user is the owner * - Ensure that it is not a system table */ static void -ATSimplePermissions(Relation rel, bool allowView, bool allowType, - bool allowForeignTable) +ATSimplePermissions(Relation rel, bool allowView, bool allowType, bool allowForeignTable) { + int relkind = rel->rd_rel->relkind; + if (rel->rd_rel->relkind != RELKIND_RELATION) { - int relkind = rel->rd_rel->relkind; StringInfoData allowed; - /* what kind of object is allowed ? */ initStringInfo(&allowed); - appendStringInfo(&allowed, "a table%s%s%s", + appendStringInfo(&allowed, "table%s%s%s", allowView ? " or view" : "", allowType ? " or composite type" : "", allowForeignTable ? " or foreign table" : ""); - + if ((relkind == RELKIND_VIEW && !allowView) || (relkind == RELKIND_COMPOSITE_TYPE && !allowType) || (relkind == RELKIND_FOREIGN_TABLE && !allowForeignTable)) + { ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("\"%s\" is not %s", + errmsg("\"%s\" is not a %s", RelationGetRelationName(rel), allowed.data))); - + } pfree(allowed.data); } /* Permissions checks */ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, + aclcheck_error(ACLCHECK_NOT_OWNER, + relkind == RELKIND_FOREIGN_TABLE ? + ACL_KIND_FOREIGN_TABLE : ACL_KIND_CLASS, RelationGetRelationName(rel)); if (!allowSystemTableMods && IsSystemRelation(rel)) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 2e829bcb45..51bd3e1ae7 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -895,7 +895,7 @@ vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound, onerel->rd_rel->relkind != RELKIND_TOASTVALUE) { ereport(WARNING, - (errmsg("skipping \"%s\" --- cannot vacuum indexes, views, or special system tables", + (errmsg("skipping \"%s\" --- cannot vacuum indexes, views, foreign tables or special system tables", RelationGetRelationName(onerel)))); relation_close(onerel, lmode); PopActiveSnapshot(); diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile index f260bcfd64..996009e363 100644 --- a/src/backend/executor/Makefile +++ b/src/backend/executor/Makefile @@ -23,6 +23,6 @@ OBJS = execAmi.o execCurrent.o execGrouping.o execJunk.o execMain.o \ nodeSeqscan.o nodeSetOp.o nodeSort.o nodeUnique.o \ nodeValuesscan.o nodeCtescan.o nodeWorktablescan.o \ nodeGroup.o nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o \ - nodeWindowAgg.o tstoreReceiver.o spi.o + nodeForeignscan.o nodeWindowAgg.o tstoreReceiver.o spi.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c index 20a50192e0..7f28c2b7b9 100644 --- a/src/backend/executor/execAmi.c +++ b/src/backend/executor/execAmi.c @@ -22,6 +22,7 @@ #include "executor/nodeBitmapOr.h" #include "executor/nodeCtescan.h" #include "executor/nodeFunctionscan.h" +#include "executor/nodeForeignscan.h" #include "executor/nodeGroup.h" #include "executor/nodeGroup.h" #include "executor/nodeHash.h" @@ -181,6 +182,10 @@ ExecReScan(PlanState *node) ExecReScanWorkTableScan((WorkTableScanState *) node); break; + case T_ForeignScanState: + ExecForeignReScan((ForeignScanState *) node); + break; + case T_NestLoopState: ExecReScanNestLoop((NestLoopState *) node); break; diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c index 64cb701e41..deedb765ef 100644 --- a/src/backend/executor/execProcnode.c +++ b/src/backend/executor/execProcnode.c @@ -86,6 +86,7 @@ #include "executor/nodeBitmapOr.h" #include "executor/nodeCtescan.h" #include "executor/nodeFunctionscan.h" +#include "executor/nodeForeignscan.h" #include "executor/nodeGroup.h" #include "executor/nodeHash.h" #include "executor/nodeHashjoin.h" @@ -226,6 +227,11 @@ ExecInitNode(Plan *node, EState *estate, int eflags) estate, eflags); break; + case T_ForeignScan: + result = (PlanState *) ExecInitForeignScan((ForeignScan *) node, + estate, eflags); + break; + /* * join nodes */ @@ -412,6 +418,10 @@ ExecProcNode(PlanState *node) result = ExecWorkTableScan((WorkTableScanState *) node); break; + case T_ForeignScanState: + result = ExecForeignScan((ForeignScanState *) node); + break; + /* * join nodes */ @@ -636,6 +646,10 @@ ExecEndNode(PlanState *node) ExecEndWorkTableScan((WorkTableScanState *) node); break; + case T_ForeignScanState: + ExecEndForeignScan((ForeignScanState *) node); + break; + /* * join nodes */ diff --git a/src/backend/executor/nodeForeignscan.c b/src/backend/executor/nodeForeignscan.c new file mode 100644 index 0000000000..7bb47884af --- /dev/null +++ b/src/backend/executor/nodeForeignscan.c @@ -0,0 +1,253 @@ +/*------------------------------------------------------------------------- + * + * nodeForeignscan.c + * Support routines for sequential scans of foreign tables. + * + * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * $PostgreSQL$ + * + *------------------------------------------------------------------------- + */ +/* + * INTERFACE ROUTINES + * ExecForeignScan sequentially scans a foreign table. + * ExecForeignNext retrieve next tuple in sequential order. + * ExecInitForeignScan creates and initializes a seqscan node. + * ExecEndForeignScan releases any storage allocated. + * ExecForeignReScan rescans the foreign table + * ExecForeignMarkPos marks scan position + * ExecForeignRestrPos restores scan position + */ +#include "postgres.h" + +#include "executor/executor.h" +#include "executor/nodeForeignscan.h" +#include "foreign/foreign.h" +#include "miscadmin.h" + +static TupleTableSlot *ForeignNext(ForeignScanState *node); +static bool ForeignRecheck(ForeignScanState *node, TupleTableSlot *slot); + +/* ---------------------------------------------------------------- + * Scan Support + * ---------------------------------------------------------------- + */ + +/* ---------------------------------------------------------------- + * ForeignNext + * + * This is a workhorse for ExecForeignScan + * ---------------------------------------------------------------- + */ +static TupleTableSlot * +ForeignNext(ForeignScanState *node) +{ + TupleTableSlot *slot = node->ss.ss_ScanTupleSlot; + + Assert(node->ss.ps.state->es_direction == ForwardScanDirection); + + /* tupleslot will be filled by Iterate. */ + node->routine->Iterate(node); + + /* Set tableoid if the tuple was valid. */ + if (HeapTupleIsValid(slot->tts_tuple)) + { + /* + * If the foreign-data wrapper returned a MinimalTuple, materialize the + * tuple to store system attributes. + */ + if (!TTS_HAS_PHYSICAL_TUPLE(slot)) + ExecMaterializeSlot(slot); + + /* overwrite only tableoid of the tuple */ + slot->tts_tuple->t_tableOid = + RelationGetRelid(node->ss.ss_currentRelation); + } + + return slot; +} + +/* + * ForeignRecheck -- access method routine to recheck a tuple in EvalPlanQual + */ +static bool +ForeignRecheck(ForeignScanState *node, TupleTableSlot *slot) +{ + /* ForeignScan never use keys in ForeignNext. */ + return true; +} + +/* ---------------------------------------------------------------- + * ExecForeignScan(node) + * + * Scans the relation sequentially and returns the next qualifying + * tuple. + * We call the ExecScan() routine and pass it the appropriate + * access method functions. + * ---------------------------------------------------------------- + */ +TupleTableSlot * +ExecForeignScan(ForeignScanState *node) +{ + return ExecScan((ScanState *) node, + (ExecScanAccessMtd) ForeignNext, + (ExecScanRecheckMtd) ForeignRecheck); +} + + +/* ---------------------------------------------------------------- + * ExecInitForeignScan + * ---------------------------------------------------------------- + */ +ForeignScanState * +ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags) +{ + ForeignScanState *scanstate; + Relation currentRelation; + Oid userid; + + /* + * foreign scan has no child node. + * but not any more. + */ + Assert(outerPlan(node) == NULL); + Assert(innerPlan(node) == NULL); + + /* + * create state structure + */ + scanstate = makeNode(ForeignScanState); + scanstate->ss.ps.plan = (Plan *) node; + scanstate->ss.ps.state = estate; + + /* + * Miscellaneous initialization + * + * create expression context for node + */ + ExecAssignExprContext(estate, &scanstate->ss.ps); + + /* + * initialize child expressions + */ + scanstate->ss.ps.targetlist = (List *) + ExecInitExpr((Expr *) node->scan.plan.targetlist, + (PlanState *) scanstate); + scanstate->ss.ps.qual = (List *) + ExecInitExpr((Expr *) node->scan.plan.qual, + (PlanState *) scanstate); + + /* + * tuple table initialization + */ + ExecInitResultTupleSlot(estate, &scanstate->ss.ps); + ExecInitScanTupleSlot(estate, &scanstate->ss); + + /* + * initialize scan relation. get the relation object id from the + * relid'th entry in the range table, open that relation and acquire + * appropriate lock on it. + */ + currentRelation = ExecOpenScanRelation(estate, node->scan.scanrelid); + scanstate->ss.ss_currentRelation = currentRelation; + ExecAssignScanType(&scanstate->ss, RelationGetDescr(currentRelation)); + scanstate->ss.ps.ps_TupFromTlist = false; + + /* + * Initialize result tuple type and projection info. + */ + ExecAssignResultTypeFromTL(&scanstate->ss.ps); + ExecAssignScanProjectionInfo(&scanstate->ss); + + /* Initialize forein-data wrapper specific data. */ + userid = GetOuterUserId(); + scanstate->table = GetForeignTable(RelationGetRelid(currentRelation)); + scanstate->server = GetForeignServer(scanstate->table->serverid); + scanstate->wrapper = GetForeignDataWrapper(scanstate->server->fdwid); + scanstate->user = GetUserMapping(userid, scanstate->server->serverid); + scanstate->routine = GetFdwRoutine(scanstate->wrapper->fdwhandler); + + /* connect to the foreign server and prepare to execute scan */ + scanstate->conn = ConnectToForeignServer(scanstate->routine, + scanstate->server, + scanstate->user, + scanstate->server->servername); + scanstate->routine->Open(scanstate); + + return scanstate; +} + +/* ---------------------------------------------------------------- + * ExecEndForeignScan + * + * frees any storage allocated through C routines. + * ---------------------------------------------------------------- + */ +void +ExecEndForeignScan(ForeignScanState *node) +{ + Relation relation; + + /* close the scan */ + node->routine->Close(node); + + /* get information from node */ + relation = node->ss.ss_currentRelation; + + /* Free the exprcontext */ + ExecFreeExprContext(&node->ss.ps); + + /* clean out the tuple table */ + ExecClearTuple(node->ss.ps.ps_ResultTupleSlot); + ExecClearTuple(node->ss.ss_ScanTupleSlot); + + /* close the relation. */ + ExecCloseScanRelation(relation); +} + +/* ---------------------------------------------------------------- + * Join Support + * ---------------------------------------------------------------- + */ + +/* ---------------------------------------------------------------- + * ExecForeignReScan + * + * Rescans the relation. + * ---------------------------------------------------------------- + */ +void +ExecForeignReScan(ForeignScanState *node) +{ + node->routine->ReOpen(node); + + ExecScanReScan((ScanState *) node); +} + +/* ---------------------------------------------------------------- + * ExecForeignMarkPos(node) + * + * Marks scan position. + * ---------------------------------------------------------------- + */ +void +ExecForeignMarkPos(ForeignScanState *node) +{ + elog(ERROR, "ForeignScan does not support mark/restore"); +} + +/* ---------------------------------------------------------------- + * ExecForeignRestrPos + * + * Restores scan position. + * ---------------------------------------------------------------- + */ +void +ExecForeignRestrPos(ForeignScanState *node) +{ + elog(ERROR, "ForeignScan does not support mark/restore"); +} diff --git a/src/backend/foreign/Makefile b/src/backend/foreign/Makefile index 85aa857d3a..8709e74edb 100644 --- a/src/backend/foreign/Makefile +++ b/src/backend/foreign/Makefile @@ -12,6 +12,6 @@ subdir = src/backend/foreign top_builddir = ../../.. include $(top_builddir)/src/Makefile.global -OBJS= foreign.o +OBJS= foreign.o fsconnection.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c index 118fd97a7f..ce9923026b 100644 --- a/src/backend/foreign/foreign.c +++ b/src/backend/foreign/foreign.c @@ -26,6 +26,7 @@ #include "utils/acl.h" #include "utils/array.h" #include "utils/builtins.h" +#include "utils/hsearch.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/syscache.h" @@ -60,6 +61,7 @@ GetForeignDataWrapper(Oid fdwid) fdw->owner = fdwform->fdwowner; fdw->fdwname = pstrdup(NameStr(fdwform->fdwname)); fdw->fdwvalidator = fdwform->fdwvalidator; + fdw->fdwhandler = fdwform->fdwhandler; /* Extract the options */ datum = SysCacheGetAttr(FOREIGNDATAWRAPPEROID, @@ -324,18 +326,19 @@ pg_options_to_table(PG_FUNCTION_ARGS) /* * Describes the valid options for postgresql FDW, server, and user mapping. */ -struct ConnectionOption +struct PgFdwOption { const char *optname; Oid optcontext; /* Oid of catalog in which option may appear */ }; /* - * Copied from fe-connect.c PQconninfoOptions. - * + * Valid options for postgresql_fdw. + * Connection options are copied from fe-connect.c PQconninfoOptions. * The list is small - don't bother with bsearch if it stays so. */ -static struct ConnectionOption libpq_conninfo_options[] = { +static struct PgFdwOption valid_options[] = { + /* Connection Options */ {"authtype", ForeignServerRelationId}, {"service", ForeignServerRelationId}, {"user", UserMappingRelationId}, @@ -350,6 +353,12 @@ static struct ConnectionOption libpq_conninfo_options[] = { {"requiressl", ForeignServerRelationId}, {"sslmode", ForeignServerRelationId}, {"gsslib", ForeignServerRelationId}, + + /* Other options */ + {"nspname", ForeignTableRelationId}, + {"relname", ForeignTableRelationId}, + + /* Centinel */ {NULL, InvalidOid} }; @@ -360,11 +369,11 @@ static struct ConnectionOption libpq_conninfo_options[] = { * don't care. */ static bool -is_conninfo_option(const char *option, Oid context) +is_valid_option(const char *option, Oid context) { - struct ConnectionOption *opt; + struct PgFdwOption *opt; - for (opt = libpq_conninfo_options; opt->optname; opt++) + for (opt = valid_options; opt->optname; opt++) if (context == opt->optcontext && strcmp(opt->optname, option) == 0) return true; return false; @@ -391,9 +400,9 @@ postgresql_fdw_validator(PG_FUNCTION_ARGS) { DefElem *def = lfirst(cell); - if (!is_conninfo_option(def->defname, catalog)) + if (!is_valid_option(def->defname, catalog)) { - struct ConnectionOption *opt; + struct PgFdwOption *opt; StringInfoData buf; /* @@ -401,7 +410,7 @@ postgresql_fdw_validator(PG_FUNCTION_ARGS) * with list of valid options for the object. */ initStringInfo(&buf); - for (opt = libpq_conninfo_options; opt->optname; opt++) + for (opt = valid_options; opt->optname; opt++) if (catalog == opt->optcontext) appendStringInfo(&buf, "%s%s", (buf.len > 0) ? ", " : "", opt->optname); @@ -456,6 +465,28 @@ GetForeignTable(Oid relid) return ft; } +FdwRoutine * +GetFdwRoutine(Oid fdwhandler) +{ + FmgrInfo flinfo; + FunctionCallInfoData fcinfo; + Datum result; + FdwRoutine *routine; + + fmgr_info(fdwhandler, &flinfo); + InitFunctionCallInfoData(fcinfo, &flinfo, 0, NULL, NULL); + result = FunctionCallInvoke(&fcinfo); + + if (fcinfo.isnull || + (routine = (FdwRoutine *) DatumGetPointer(result)) == NULL) + { + elog(ERROR, "function %u returned NULL", flinfo.fn_oid); + routine = NULL; /* keep compiler quiet */ + } + + return routine; +} + /* * Determine the relation is a foreign table. */ @@ -469,8 +500,8 @@ IsForeignTable(Oid relid) tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); if (!HeapTupleIsValid(tuple)) ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_TABLE), - errmsg("relation with OID %u does not exist", relid))); + (errcode(ERRCODE_UNDEFINED_TABLE), + errmsg("relation with OID %u does not exist", relid))); classForm = (Form_pg_class) GETSTRUCT(tuple); relkind = classForm->relkind; ReleaseSysCache(tuple); diff --git a/src/backend/foreign/fsconnection.c b/src/backend/foreign/fsconnection.c new file mode 100644 index 0000000000..1e351a7766 --- /dev/null +++ b/src/backend/foreign/fsconnection.c @@ -0,0 +1,329 @@ +/*------------------------------------------------------------------------- + * + * fsconnection.c + * foreign server connection manager. + * + * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group + * + * IDENTIFICATION + * $PostgreSQL$ + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "foreign/foreign.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "parser/scansup.h" +#include "storage/ipc.h" +#include "utils/builtins.h" +#include "utils/catcache.h" + + +extern Datum pg_foreign_connections(PG_FUNCTION_ARGS); + + +/* + * Connection cache entry managed with hash table. + */ +typedef struct FSConnCacheEntry +{ + /* hash key must be first */ + char name[NAMEDATALEN]; /* connection name; used as hash key */ + Oid serverid; /* oid of foreign server */ + Oid userid; /* oid of user (0 means PUBLIC) */ + FdwRoutine *routine; + FSConnection *conn; /* foreign server connection */ +} FSConnCacheEntry; + +static FSConnCacheEntry *make_connection(const char *conname, bool *found); +static void cleanup_fsconnection(int code, Datum arg); + +/* + * Hash table to cache connection to PostgreSQL servers, will be initialized + * before first attempt to connect PostgreSQL server by the backend. + */ +static HTAB *FSConnectionHash; + +/* + * Get established connection to the server with the user. + * + * Connections are cached until the backend shutdowns. + */ +FSConnection * +ConnectToForeignServer(FdwRoutine *routine, + ForeignServer *server, + UserMapping *user, + const char *conname) +{ + bool found; + FSConnCacheEntry *entry; + + AssertArg(routine != NULL); + AssertArg(server != NULL); + AssertArg(user != NULL); + AssertArg(conname != NULL); + AssertArg(strlen(conname) < NAMEDATALEN); + + entry = make_connection(conname, &found); + if (found) + { + if (entry->routine != routine || + entry->serverid != server->serverid) + { + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("connection \"%s\" is already used for another server", + conname))); + } + return entry->conn; + } + + /* + * If a new entry, connect to the foreign server with ConnectServer. + * The FSConnection will be left as NULL when the FDW does not support + * the function. + * + * OuterUserId is used to determine user mapping, because when a foreign + * table was accessed in a function which was defined with SECURITY DEFINER + * option, CurrentUserId is changed to the owner of the function and it + * could differ from the caller of the function. + * + * Note that even if the user mapping was for PUBLIC, user->userid is set + * to the OuterUserId in GetUserMapping(). + */ + entry->serverid = server->serverid; + entry->userid = user->userid; + entry->routine = routine; + entry->conn = NULL; + + if (routine->ConnectServer != NULL) + { + PG_TRY(); + { + entry->conn = entry->routine->ConnectServer(server, user); + } + PG_CATCH(); + { + /* remove uninitialized entry when the attempt to connect failed. */ + hash_search(FSConnectionHash, conname, HASH_REMOVE, &found); + PG_RE_THROW(); + } + PG_END_TRY(); + } + return entry->conn; +} + +void +RegisterFSConnection(FSConnection *conn, + FdwRoutine *routine, + const char *conname) +{ + bool found; + FSConnCacheEntry *entry; + + AssertArg(routine != NULL); + AssertArg(conname != NULL); + AssertArg(strlen(conname) < NAMEDATALEN); + + entry = make_connection(conname, &found); + if (found) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("connection \"%s\" is already used for another server", + conname))); + + entry->serverid = 0; + entry->userid = GetOuterUserId(); + entry->routine = routine; + entry->conn = conn; +} + +static FSConnCacheEntry * +make_connection(const char *conname, bool *found) +{ + /* initialize connection cache */ + if (FSConnectionHash == NULL) + { + HASHCTL ctl; + + ereport(DEBUG1, (errmsg("initializing foreign server connection cache"))); + + /* hash key is oid of pg_user_mapping */ + MemSet(&ctl, 0, sizeof(ctl)); + ctl.keysize = NAMEDATALEN; + ctl.entrysize = sizeof(FSConnCacheEntry); + /* allocate FSConnectionHash in the cache context */ + ctl.hcxt = CacheMemoryContext; + FSConnectionHash = hash_create("Foreign Connections", 32, + &ctl, HASH_ELEM | HASH_CONTEXT); + + /* register cleanup function */ + on_proc_exit(cleanup_fsconnection, 0); + + ereport(DEBUG1, (errmsg("foreign server connection cache created"))); + } + + /* + * Search from cache first. When an apporopriate connection was found in + * the connection cache, use it. + */ + return (FSConnCacheEntry *) hash_search(FSConnectionHash, + conname, HASH_ENTER, found); +} + +/* + * Find the connection in the cache by name. + * Caller must check that the connection is related to correct foreign-data + * wrapper. + */ +FSConnection * +GetFSConnectionByName(const char *conname, FdwRoutine **routine) +{ + FSConnCacheEntry *entry; + + AssertArg(conname != NULL); + AssertArg(strlen(conname) < NAMEDATALEN); + + if (FSConnectionHash == NULL) + return NULL; + + entry = (FSConnCacheEntry *) hash_search(FSConnectionHash, + conname, HASH_FIND, NULL); + + if (entry) + { + if (routine) + *routine = entry->routine; + return entry->conn; + } + else + return NULL; +} + +/* + * Remove the connection in the cache and disconnect from the foreign server. + */ +bool +DisconnectForeignServer(const char *conname) +{ + FSConnCacheEntry *entry; + + AssertArg(conname != NULL); + AssertArg(strlen(conname) < NAMEDATALEN); + + /* When the connection cache has not initialized yet, nothing to do. */ + if (FSConnectionHash == NULL) + return false; + + /* Remove from the connection cache. Do nothing if not found. */ + entry = (FSConnCacheEntry *) hash_search(FSConnectionHash, + conname, HASH_REMOVE, NULL); + if (entry == NULL) + return false; + + elog(DEBUG1, "foreign server connection \"%s\" discarded", conname); + + /* Disconnect from the foreign server with FreeFSConnection(). */ + if (entry->routine->FreeFSConnection && entry->conn != NULL) + entry->routine->FreeFSConnection(entry->conn); + + return true; +} + +/* + * Discard all foreign server connections and free resources. + */ +void +DisconnectAllForeignServers(void) +{ + HASH_SEQ_STATUS seq; + FSConnCacheEntry *entry; + +elog(DEBUG1, "%s(%u)", __FUNCTION__, __LINE__); + if (!FSConnectionHash) + return; + + hash_seq_init(&seq, FSConnectionHash); + while ((entry = hash_seq_search(&seq)) != NULL) + DisconnectForeignServer(entry->name); +} + +/* + * Retrieve foreign server connections. + */ +Datum +pg_foreign_connections(PG_FUNCTION_ARGS) +{ +#define PG_FOREIGN_SERVER_CONNECTIONS_COLS 3 + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + TupleDesc tupdesc; + Tuplestorestate *tupstore; + MemoryContext per_query_ctx; + MemoryContext oldcontext; + + /* check to see if caller supports us returning a tuplestore */ + if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("set-valued function called in context that cannot accept a set"))); + if (!(rsinfo->allowedModes & SFRM_Materialize)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("materialize mode required, but it is not " \ + "allowed in this context"))); + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + per_query_ctx = rsinfo->econtext->ecxt_per_query_memory; + oldcontext = MemoryContextSwitchTo(per_query_ctx); + + tupstore = tuplestore_begin_heap(true, false, work_mem); + rsinfo->returnMode = SFRM_Materialize; + rsinfo->setResult = tupstore; + rsinfo->setDesc = tupdesc; + + if (FSConnectionHash) + { + HASH_SEQ_STATUS hash_seq; + FSConnCacheEntry *entry; + + MemoryContextSwitchTo(oldcontext); + + hash_seq_init(&hash_seq, FSConnectionHash); + while ((entry = hash_seq_search(&hash_seq)) != NULL) + { + Datum values[PG_FOREIGN_SERVER_CONNECTIONS_COLS]; + bool nulls[PG_FOREIGN_SERVER_CONNECTIONS_COLS]; + int i = 0; + + memset(values, 0, sizeof(values)); + memset(nulls, 0, sizeof(nulls)); + + values[i++] = CStringGetTextDatum(entry->name); + values[i++] = ObjectIdGetDatum(entry->serverid); + values[i++] = ObjectIdGetDatum(entry->userid); + + Assert(i == PG_FOREIGN_SERVER_CONNECTIONS_COLS); + + tuplestore_putvalues(tupstore, tupdesc, values, nulls); + } + } + + /* clean up and return the tuplestore */ + tuplestore_donestoring(tupstore); + + return (Datum) 0; +} + +/* + * Disconnect all connections on the exit of backend process. + */ +static void +cleanup_fsconnection(int code, Datum arg) +{ + DisconnectAllForeignServers(); +} diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index adea1469f3..894ae36e84 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -519,6 +519,22 @@ _copyWorkTableScan(WorkTableScan *from) return newnode; } +/* + * _copyForeignScan + */ +static ForeignScan * +_copyForeignScan(ForeignScan *from) +{ + ForeignScan *newnode = makeNode(ForeignScan); + + /* + * copy node superclass fields + */ + CopyScanFields((Scan *) from, (Scan *) newnode); + + return newnode; +} + /* * CopyJoinFields * @@ -3128,8 +3144,8 @@ _copyCreateFdwStmt(CreateFdwStmt *from) CreateFdwStmt *newnode = makeNode(CreateFdwStmt); COPY_STRING_FIELD(fdwname); - COPY_NODE_FIELD(validator); - COPY_NODE_FIELD(options); + COPY_NODE_FIELD(fdw_options); + COPY_NODE_FIELD(gen_options); return newnode; } @@ -3140,9 +3156,8 @@ _copyAlterFdwStmt(AlterFdwStmt *from) AlterFdwStmt *newnode = makeNode(AlterFdwStmt); COPY_STRING_FIELD(fdwname); - COPY_NODE_FIELD(validator); - COPY_SCALAR_FIELD(change_validator); - COPY_NODE_FIELD(options); + COPY_NODE_FIELD(fdw_options); + COPY_NODE_FIELD(gen_options); return newnode; } @@ -3686,6 +3701,9 @@ copyObject(void *from) case T_WorkTableScan: retval = _copyWorkTableScan(from); break; + case T_ForeignScan: + retval = _copyForeignScan(from); + break; case T_Join: retval = _copyJoin(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 6b048bdf7e..fd9b15833b 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1601,8 +1601,8 @@ static bool _equalCreateFdwStmt(CreateFdwStmt *a, CreateFdwStmt *b) { COMPARE_STRING_FIELD(fdwname); - COMPARE_NODE_FIELD(validator); - COMPARE_NODE_FIELD(options); + COMPARE_NODE_FIELD(fdw_options); + COMPARE_NODE_FIELD(gen_options); return true; } @@ -1611,9 +1611,8 @@ static bool _equalAlterFdwStmt(AlterFdwStmt *a, AlterFdwStmt *b) { COMPARE_STRING_FIELD(fdwname); - COMPARE_NODE_FIELD(validator); - COMPARE_SCALAR_FIELD(change_validator); - COMPARE_NODE_FIELD(options); + COMPARE_NODE_FIELD(fdw_options); + COMPARE_NODE_FIELD(gen_options); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 55665ca20e..3cf7e9c281 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -504,6 +504,14 @@ _outWorkTableScan(StringInfo str, WorkTableScan *node) WRITE_INT_FIELD(wtParam); } +static void +_outForeignScan(StringInfo str, ForeignScan *node) +{ + WRITE_NODE_TYPE("FOREIGNSCAN"); + + _outScanInfo(str, (Scan *) node); +} + static void _outJoin(StringInfo str, Join *node) { @@ -2540,6 +2548,9 @@ _outNode(StringInfo str, void *obj) case T_WorkTableScan: _outWorkTableScan(str, obj); break; + case T_ForeignScan: + _outForeignScan(str, obj); + break; case T_Join: _outJoin(str, obj); break; diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 88e283a71d..cae54a3dc2 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -255,21 +255,22 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) * least one dimension of cost or sortedness. */ - /* - * TODO: Implement foreign table scanning and replace this check with - * path creation for foreign table. - */ if (IsForeignTable(rte->relid)) - elog(ERROR, "foreign table scan not implemented."); - - /* Consider sequential scan */ - add_path(rel, create_seqscan_path(root, rel)); + { + /* only foreign scan path is applyable to foreign table */ + add_path(rel, create_foreignscan_path(root, rel)); + } + else + { + /* Consider sequential scan */ + add_path(rel, create_seqscan_path(root, rel)); - /* Consider index scans */ - create_index_paths(root, rel); + /* Consider index scans */ + create_index_paths(root, rel); - /* Consider TID scans */ - create_tidscan_paths(root, rel); + /* Consider TID scans */ + create_tidscan_paths(root, rel); + } /* Now find the cheapest of the paths for this rel */ set_cheapest(rel); diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 53aa62fb81..9930abdac3 100644 --- a/src/backend/optimizer/path/costsize.c +++ b/src/backend/optimizer/path/costsize.c @@ -1021,6 +1021,23 @@ cost_ctescan(Path *path, PlannerInfo *root, RelOptInfo *baserel) path->total_cost = startup_cost + run_cost; } +/* + * cost_foreignscan + * Determines and returns the cost of scanning a foreign table sequentially. + */ +void +cost_foreignscan(Path *path, PlannerInfo *root, + RelOptInfo *baserel) +{ + /* Should only be applied to base relations */ + Assert(baserel->relid > 0); + Assert(baserel->rtekind == RTE_RELATION); + + /* XXX estimate cost to scan the foreign table */ + path->startup_cost = 10000.0; + path->total_cost = 10000.0; +} + /* * cost_recursive_union * Determines and returns the cost of performing a recursive union, @@ -2430,6 +2447,11 @@ cost_rescan(PlannerInfo *root, Path *path, *rescan_total_cost = run_cost; } break; + case T_ForeignScan: + /* XXX estimate cost to scan foreign table via FDW */ + *rescan_startup_cost = path->startup_cost; + *rescan_total_cost = path->total_cost; + break; default: *rescan_startup_cost = path->startup_cost; *rescan_total_cost = path->total_cost; diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index fa7b29f7d4..92666e9479 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -20,6 +20,7 @@ #include #include "access/skey.h" +#include "catalog/pg_class.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "optimizer/clauses.h" @@ -68,6 +69,8 @@ static CteScan *create_ctescan_plan(PlannerInfo *root, Path *best_path, List *tlist, List *scan_clauses); static WorkTableScan *create_worktablescan_plan(PlannerInfo *root, Path *best_path, List *tlist, List *scan_clauses); +static ForeignScan *create_foreignscan_plan(PlannerInfo *root, Path *best_path, + List *tlist, List *scan_clauses); static NestLoop *create_nestloop_plan(PlannerInfo *root, NestPath *best_path, Plan *outer_plan, Plan *inner_plan); static MergeJoin *create_mergejoin_plan(PlannerInfo *root, MergePath *best_path, @@ -105,6 +108,8 @@ static CteScan *make_ctescan(List *qptlist, List *qpqual, Index scanrelid, int ctePlanId, int cteParam); static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual, Index scanrelid, int wtParam); +static ForeignScan *make_foreignscan(List *qptlist, RangeTblEntry *rte, + List *qpqual, Index scanrelid); static BitmapAnd *make_bitmap_and(List *bitmapplans); static BitmapOr *make_bitmap_or(List *bitmapplans); static NestLoop *make_nestloop(List *tlist, @@ -190,6 +195,7 @@ create_plan_recurse(PlannerInfo *root, Path *best_path) case T_ValuesScan: case T_CteScan: case T_WorkTableScan: + case T_ForeignScan: plan = create_scan_plan(root, best_path); break; case T_HashJoin: @@ -326,6 +332,13 @@ create_scan_plan(PlannerInfo *root, Path *best_path) scan_clauses); break; + case T_ForeignScan: + plan = (Plan *) create_foreignscan_plan(root, + best_path, + tlist, + scan_clauses); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) best_path->pathtype); @@ -448,6 +461,7 @@ disuse_physical_tlist(Plan *plan, Path *path) case T_ValuesScan: case T_CteScan: case T_WorkTableScan: + case T_ForeignScan: plan->targetlist = build_relation_tlist(path->parent); break; default: @@ -1625,6 +1639,43 @@ create_worktablescan_plan(PlannerInfo *root, Path *best_path, return scan_plan; } +/* + * create_foreignscan_plan + * Returns a foreignscan plan for the base relation scanned by 'best_path' + * with restriction clauses 'scan_clauses' and targetlist 'tlist'. + */ +static ForeignScan * +create_foreignscan_plan(PlannerInfo *root, Path *best_path, + List *tlist, List *scan_clauses) +{ + ForeignScan *scan_plan; + Index scan_relid = best_path->parent->relid; + RangeTblEntry *rte; + + /* it should be a base rel... */ + Assert(scan_relid > 0); + Assert(best_path->parent->rtekind == RTE_RELATION); + rte = planner_rt_fetch(scan_relid, root); + Assert(rte->rtekind == RTE_RELATION); + + /* Sort clauses into best execution order */ + scan_clauses = order_qual_clauses(root, scan_clauses); + + /* Reduce RestrictInfo list to bare expressions; ignore pseudoconstants */ + scan_clauses = extract_actual_clauses(scan_clauses, false); + + scan_plan = make_foreignscan(tlist, + rte, + scan_clauses, + scan_relid); + + copy_path_costsize(&scan_plan->scan.plan, best_path); + /* XXX override estimated rows to emulate big-result */ + scan_plan->scan.plan.plan_rows = 1000; + + return scan_plan; +} + /***************************************************************************** * @@ -2783,6 +2834,25 @@ make_worktablescan(List *qptlist, return node; } +static ForeignScan * +make_foreignscan(List *qptlist, + RangeTblEntry *rte, + List *qpqual, + Index scanrelid) +{ + ForeignScan *node = makeNode(ForeignScan); + Plan *plan = &node->scan.plan; + + /* cost should be inserted by caller */ + plan->targetlist = qptlist; + plan->qual = qpqual; + plan->lefttree = NULL; + plan->righttree = NULL; + node->scan.scanrelid = scanrelid; + + return node; +} + Append * make_append(List *appendplans, List *tlist) { diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index d5e9212f6a..b515382311 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -396,6 +396,17 @@ set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset) fix_scan_list(glob, splan->scan.plan.qual, rtoffset); } break; + case T_ForeignScan: + { + ForeignScan *splan = (ForeignScan *) plan; + + splan->scan.scanrelid += rtoffset; + splan->scan.plan.targetlist = + fix_scan_list(glob, splan->scan.plan.targetlist, rtoffset); + splan->scan.plan.qual = + fix_scan_list(glob, splan->scan.plan.qual, rtoffset); + } + break; case T_NestLoop: case T_MergeJoin: case T_HashJoin: diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index ab62e2a234..cef5e31efe 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -2024,6 +2024,10 @@ finalize_plan(PlannerInfo *root, Plan *plan, Bitmapset *valid_params, context.paramids = bms_add_members(context.paramids, scan_params); break; + case T_ForeignScan: + context.paramids = bms_add_members(context.paramids, scan_params); + break; + case T_ModifyTable: { ModifyTable *mtplan = (ModifyTable *) plan; diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index f904258280..a630de6909 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1236,6 +1236,15 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) continue; } + /* + * SELECT FOR UPDATE/SHARE is not allowd to foreign tables because + * they are read-only. + */ + if (newrelation->rd_rel->relkind == RELKIND_FOREIGN_TABLE && + lockmode != AccessShareLock) + ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("SELECT FOR UPDATE/SHARE is not allowed with foreign tables"))); + /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID, diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index f8aa745fef..6f7d559cc4 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1310,6 +1310,25 @@ create_worktablescan_path(PlannerInfo *root, RelOptInfo *rel) return pathnode; } +/* + * create_foreignscan_path + * Creates a path corresponding to a scan of a foreign table, + * returning the pathnode. + */ +Path * +create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel) +{ + Path *pathnode = makeNode(Path); + + pathnode->pathtype = T_ForeignScan; + pathnode->parent = rel; + pathnode->pathkeys = NIL; /* result is always unordered */ + + cost_foreignscan(pathnode, root, rel); + + return pathnode; +} + /* * create_nestloop_path * Creates a pathnode corresponding to a nestloop join between two diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index ad71d3a4f9..9102f42872 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -440,6 +440,9 @@ estimate_rel_size(Relation rel, int32 *attr_widths, *pages = 1; *tuples = 1; break; + case RELKIND_FOREIGN_TABLE: + /* XXX: estimate with FDW or use pg_class.reltuples/relpages ? */ + break; default: /* else it has no disk storage; probably shouldn't get here? */ *pages = 0; @@ -667,7 +670,8 @@ relation_excluded_by_constraints(PlannerInfo *root, * * We also support building a "physical" tlist for subqueries, functions, * values lists, and CTEs, since the same optimization can occur in - * SubqueryScan, FunctionScan, ValuesScan, CteScan, and WorkTableScan nodes. + * SubqueryScan, FunctionScan, ValuesScan, CteScan, WorkTableScan and + * ForeignScan nodes. */ List * build_physical_tlist(PlannerInfo *root, RelOptInfo *rel) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 0a93ec70b0..e67f091d3f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -28,6 +28,7 @@ #include "catalog/pg_type.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "nodes/pg_list.h" #include "optimizer/var.h" #include "parser/analyze.h" #include "parser/parse_agg.h" @@ -40,6 +41,7 @@ #include "parser/parse_target.h" #include "parser/parsetree.h" #include "rewrite/rewriteManip.h" +#include "utils/lsyscache.h" #include "utils/rel.h" @@ -2261,6 +2263,15 @@ applyLockingClause(Query *qry, Index rtindex, bool forUpdate, bool noWait, bool pushedDown) { RowMarkClause *rc; + RangeTblEntry *rte; + + /* If rangetable is a foreign table, locking is not allowed */ + rte = list_nth(qry->rtable, rtindex - 1); + if (rte->rtekind == RTE_RELATION && + get_rel_relkind(rte->relid) == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("SELECT FOR UPDATE/SHARE is not allowed with foreign tables"))); /* If it's an explicit clause, make sure hasForUpdate gets set */ if (!pushedDown) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 78db97bdf5..a02c7c1c80 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -221,8 +221,8 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_ %type alter_column_default opclass_item opclass_drop alter_using %type add_drop opt_asc_desc opt_nulls_order -%type alter_table_cmd alter_foreign_table_cmd alter_type_cmd -%type alter_table_cmds alter_foreign_table_cmds alter_type_cmds +%type alter_table_cmd alter_type_cmd alter_foreign_table_cmd +%type alter_table_cmds alter_type_cmds alter_foreign_table_cmds %type opt_drop_behavior @@ -307,6 +307,9 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_ create_generic_options alter_generic_options relation_expr_list dostmt_opt_list +%type opt_fdw_options fdw_options +%type fdw_option + %type OptTempTableName %type into_clause create_as_target @@ -3219,16 +3222,33 @@ DropTableSpaceStmt: DROP TABLESPACE name * *****************************************************************************/ -CreateFdwStmt: CREATE FOREIGN DATA_P WRAPPER name opt_validator create_generic_options +CreateFdwStmt: CREATE FOREIGN DATA_P WRAPPER name opt_fdw_options create_generic_options { CreateFdwStmt *n = makeNode(CreateFdwStmt); n->fdwname = $5; - n->validator = $6; - n->options = $7; + n->fdw_options = $6; + n->gen_options = $7; $$ = (Node *) n; } ; +fdw_option: + VALIDATOR handler_name { $$ = makeDefElem("validator", (Node *)$2); } + | NO VALIDATOR { $$ = makeDefElem("validator", NULL); } + | HANDLER handler_name { $$ = makeDefElem("handler", (Node *)$2); } + | NO HANDLER { $$ = makeDefElem("handler", NULL); } + ; + +fdw_options: + fdw_option { $$ = list_make1($1); } + | fdw_options fdw_option { $$ = lappend($1, $2); } + ; + +opt_fdw_options: + fdw_options { $$ = $1; } + | /*EMPTY*/ { $$ = NIL; } + ; + /***************************************************************************** * * QUERY : @@ -3261,28 +3281,20 @@ DropFdwStmt: DROP FOREIGN DATA_P WRAPPER name opt_drop_behavior * ****************************************************************************/ -AlterFdwStmt: ALTER FOREIGN DATA_P WRAPPER name validator_clause alter_generic_options +AlterFdwStmt: ALTER FOREIGN DATA_P WRAPPER name opt_fdw_options alter_generic_options { AlterFdwStmt *n = makeNode(AlterFdwStmt); n->fdwname = $5; - n->validator = $6; - n->change_validator = true; - n->options = $7; + n->fdw_options = $6; + n->gen_options = $7; $$ = (Node *) n; } - | ALTER FOREIGN DATA_P WRAPPER name validator_clause + | ALTER FOREIGN DATA_P WRAPPER name fdw_options { AlterFdwStmt *n = makeNode(AlterFdwStmt); n->fdwname = $5; - n->validator = $6; - n->change_validator = true; - $$ = (Node *) n; - } - | ALTER FOREIGN DATA_P WRAPPER name alter_generic_options - { - AlterFdwStmt *n = makeNode(AlterFdwStmt); - n->fdwname = $5; - n->options = $6; + n->fdw_options = $6; + n->gen_options = NIL; $$ = (Node *) n; } ; @@ -4880,7 +4892,6 @@ security_label_type: | DOMAIN_P { $$ = OBJECT_TYPE; } | TYPE_P { $$ = OBJECT_TYPE; } | VIEW { $$ = OBJECT_VIEW; } - | FOREIGN TABLE { $$ = OBJECT_FOREIGN_TABLE; } ; security_label: Sconst { $$ = $1; } diff --git a/src/backend/utils/adt/pseudotypes.c b/src/backend/utils/adt/pseudotypes.c index dd12592473..a08af41413 100644 --- a/src/backend/utils/adt/pseudotypes.c +++ b/src/backend/utils/adt/pseudotypes.c @@ -453,3 +453,29 @@ pg_node_tree_send(PG_FUNCTION_ARGS) { return textsend(fcinfo); } + +/* + * fdw_handler_in - input routine for pseudo-type FDW_HANDLER. + */ +Datum +fdw_handler_in(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot accept a value of type fdw_handler"))); + + PG_RETURN_VOID(); /* keep compiler quiet */ +} + +/* + * fdw_handler_out - output routine for pseudo-type FDW_HANDLER. + */ +Datum +fdw_handler_out(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot display a value of type fdw_handler"))); + + PG_RETURN_VOID(); /* keep compiler quiet */ +} diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c index 14ed9147a1..87a424f476 100644 --- a/src/backend/utils/init/miscinit.c +++ b/src/backend/utils/init/miscinit.c @@ -30,6 +30,7 @@ #endif #include "catalog/pg_authid.h" +#include "foreign/foreign.h" #include "mb/pg_wchar.h" #include "miscadmin.h" #include "postmaster/autovacuum.h" @@ -261,6 +262,10 @@ SetOuterUserId(Oid userid) AssertArg(OidIsValid(userid)); OuterUserId = userid; + /* Discard all foreign server connections */ + if (CurrentUserId != userid) + DisconnectAllForeignServers(); + /* We force the effective user ID to match, too */ CurrentUserId = userid; } @@ -286,6 +291,10 @@ SetSessionUserId(Oid userid, bool is_superuser) SessionUserIsSuperuser = is_superuser; SetRoleIsActive = false; + /* Discard all foreign server connections */ + if (CurrentUserId != userid) + DisconnectAllForeignServers(); + /* We force the effective user IDs to match, too */ OuterUserId = userid; CurrentUserId = userid; @@ -379,6 +388,7 @@ SetUserIdAndContext(Oid userid, bool sec_def_context) (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("cannot set parameter \"%s\" within security-restricted operation", "role"))); + CurrentUserId = userid; if (sec_def_context) SecurityRestrictionContext |= SECURITY_LOCAL_USERID_CHANGE; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 58a31c408b..be43afd9a4 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5925,6 +5925,7 @@ getForeignDataWrappers(int *numForeignDataWrappers) int i_fdwname; int i_rolname; int i_fdwvalidator; + int i_fdwhandler; int i_fdwacl; int i_fdwoptions; @@ -5939,7 +5940,8 @@ getForeignDataWrappers(int *numForeignDataWrappers) selectSourceSchema("pg_catalog"); appendPQExpBuffer(query, "SELECT tableoid, oid, fdwname, " - "(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, fdwacl," + "(%s fdwowner) AS rolname, fdwvalidator::pg_catalog.regproc, " + "fdwhandler::pg_catalog.regproc, fdwacl," "array_to_string(ARRAY(" " SELECT option_name || ' ' || quote_literal(option_value) " " FROM pg_options_to_table(fdwoptions)), ', ') AS fdwoptions " @@ -5959,6 +5961,7 @@ getForeignDataWrappers(int *numForeignDataWrappers) i_fdwname = PQfnumber(res, "fdwname"); i_rolname = PQfnumber(res, "rolname"); i_fdwvalidator = PQfnumber(res, "fdwvalidator"); + i_fdwhandler = PQfnumber(res, "fdwhandler"); i_fdwacl = PQfnumber(res, "fdwacl"); i_fdwoptions = PQfnumber(res, "fdwoptions"); @@ -5972,6 +5975,7 @@ getForeignDataWrappers(int *numForeignDataWrappers) fdwinfo[i].dobj.namespace = NULL; fdwinfo[i].rolname = strdup(PQgetvalue(res, i, i_rolname)); fdwinfo[i].fdwvalidator = strdup(PQgetvalue(res, i, i_fdwvalidator)); + fdwinfo[i].fdwhandler = strdup(PQgetvalue(res, i, i_fdwhandler)); fdwinfo[i].fdwoptions = strdup(PQgetvalue(res, i, i_fdwoptions)); fdwinfo[i].fdwacl = strdup(PQgetvalue(res, i, i_fdwacl)); @@ -10185,6 +10189,10 @@ dumpForeignDataWrapper(Archive *fout, FdwInfo *fdwinfo) appendPQExpBuffer(q, " VALIDATOR %s", fdwinfo->fdwvalidator); + if (fdwinfo->fdwhandler && strcmp(fdwinfo->fdwhandler, "-") != 0) + appendPQExpBuffer(q, " HANDLER %s", + fdwinfo->fdwhandler); + if (fdwinfo->fdwoptions && strlen(fdwinfo->fdwoptions) > 0) appendPQExpBuffer(q, " OPTIONS (%s)", fdwinfo->fdwoptions); @@ -11010,11 +11018,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) PQclear(res); } else - { reltypename = "TABLE"; - srvname = NULL; - ftoptions = NULL; - } numParents = tbinfo->numParents; parents = tbinfo->parents; diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 78855357c8..5905d0f00c 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -420,6 +420,7 @@ typedef struct _fdwInfo DumpableObject dobj; char *rolname; char *fdwvalidator; + char *fdwhandler; char *fdwoptions; char *fdwacl; } FdwInfo; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 5501c17856..2c12cb8fc0 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3348,9 +3348,11 @@ listForeignDataWrappers(const char *pattern, bool verbose) printfPQExpBuffer(&buf, "SELECT fdwname AS \"%s\",\n" " pg_catalog.pg_get_userbyid(fdwowner) AS \"%s\",\n" + " fdwhandler::pg_catalog.regproc AS \"%s\",\n" " fdwvalidator::pg_catalog.regproc AS \"%s\"", gettext_noop("Name"), gettext_noop("Owner"), + gettext_noop("Handler"), gettext_noop("Validator")); if (verbose) diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 51010e1621..14f2369a0f 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -288,6 +288,21 @@ static const SchemaQuery Query_for_list_of_sequences = { NULL }; +static const SchemaQuery Query_for_list_of_foreign_tables = { + /* catname */ + "pg_catalog.pg_class c", + /* selcondition */ + "c.relkind IN ('f')", + /* viscondition */ + "pg_catalog.pg_table_is_visible(c.oid)", + /* namespace */ + "c.relnamespace", + /* result */ + "pg_catalog.quote_ident(c.relname)", + /* qualresult */ + NULL +}; + static const SchemaQuery Query_for_list_of_tables = { /* catname */ "pg_catalog.pg_class c", @@ -303,11 +318,11 @@ static const SchemaQuery Query_for_list_of_tables = { NULL }; -static const SchemaQuery Query_for_list_of_tisv = { +static const SchemaQuery Query_for_list_of_tisvf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ - "c.relkind IN ('r', 'i', 'S', 'v')", + "c.relkind IN ('r', 'i', 'S', 'v', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ @@ -318,11 +333,11 @@ static const SchemaQuery Query_for_list_of_tisv = { NULL }; -static const SchemaQuery Query_for_list_of_tsv = { +static const SchemaQuery Query_for_list_of_tsvf = { /* catname */ "pg_catalog.pg_class c", /* selcondition */ - "c.relkind IN ('r', 'S', 'v')", + "c.relkind IN ('r', 'S', 'v', 'f')", /* viscondition */ "pg_catalog.pg_table_is_visible(c.oid)", /* namespace */ @@ -541,6 +556,7 @@ static const pgsql_thing_t words_after_create[] = { {"DICTIONARY", Query_for_list_of_ts_dictionaries, NULL, true}, {"DOMAIN", NULL, &Query_for_list_of_domains}, {"FOREIGN DATA WRAPPER", NULL, NULL}, + {"FOREIGN TABLE", NULL, NULL}, {"FUNCTION", NULL, &Query_for_list_of_functions}, {"GROUP", Query_for_list_of_roles}, {"LANGUAGE", Query_for_list_of_languages}, @@ -638,7 +654,7 @@ psql_completion(char *text, int start, int end) static const char *const backslash_commands[] = { "\\a", "\\connect", "\\conninfo", "\\C", "\\cd", "\\copy", "\\copyright", - "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\deu", "\\dew", "\\df", + "\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\det", "\\deu", "\\dew", "\\df", "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dn", "\\do", "\\dp", "\\drds", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\e", "\\echo", "\\ef", "\\encoding", @@ -701,7 +717,7 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev3_wd, "TABLE") != 0) { static const char *const list_ALTER[] = - {"AGGREGATE", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "FOREIGN DATA WRAPPER", "FUNCTION", + {"AGGREGATE", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION", "GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "OPERATOR", "ROLE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE", "USER", "USER MAPPING FOR", "VIEW", NULL}; @@ -755,6 +771,16 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_ALTERDATABASE); } + /* ALTER FOREIGN */ + else if (pg_strcasecmp(prev2_wd, "ALTER") == 0 && + pg_strcasecmp(prev_wd, "FOREIGN") == 0) + { + static const char *const list_ALTER_FOREIGN[] = + {"DATA WRAPPER", "TABLE", NULL}; + + COMPLETE_WITH_LIST(list_ALTER_FOREIGN); + } + /* ALTER FOREIGN DATA WRAPPER */ else if (pg_strcasecmp(prev5_wd, "ALTER") == 0 && pg_strcasecmp(prev4_wd, "FOREIGN") == 0 && @@ -767,6 +793,18 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_ALTER_FDW); } + /* ALTER FOREIGN TABLE */ + else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 && + pg_strcasecmp(prev3_wd, "FOREIGN") == 0 && + pg_strcasecmp(prev2_wd, "TABLE") == 0) + { + static const char *const list_ALTER_FOREIGN_TABLE[] = + {"ADD", "ALTER", "DISABLE", "DROP", "ENABLE", "INHERIT", + "NO INHERIT", "RENAME", "RESET", "OWNER TO", NULL}; + + COMPLETE_WITH_LIST(list_ALTER_FOREIGN_TABLE); + } + /* ALTER INDEX */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && pg_strcasecmp(prev2_wd, "INDEX") == 0) @@ -1376,7 +1414,7 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "ON") == 0) { static const char *const list_COMMENT[] = - {"CAST", "CONVERSION", "DATABASE", "INDEX", "LANGUAGE", "RULE", "SCHEMA", + {"CAST", "CONVERSION", "DATABASE", "FOREIGN TABLE", "INDEX", "LANGUAGE", "RULE", "SCHEMA", "SEQUENCE", "TABLE", "TYPE", "VIEW", "COLUMN", "AGGREGATE", "FUNCTION", "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE", NULL}; @@ -1469,6 +1507,16 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "TEMPLATE") == 0) COMPLETE_WITH_QUERY(Query_for_list_of_template_databases); + /* CREATE FOREIGN */ + else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && + pg_strcasecmp(prev_wd, "FOREIGN") == 0) + { + static const char *const list_CREATE_FOREIGN[] = + {"DATA WRAPPER", "TABLE", NULL}; + + COMPLETE_WITH_LIST(list_CREATE_FOREIGN); + } + /* CREATE FOREIGN DATA WRAPPER */ else if (pg_strcasecmp(prev5_wd, "CREATE") == 0 && pg_strcasecmp(prev4_wd, "FOREIGN") == 0 && @@ -1476,6 +1524,18 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev2_wd, "WRAPPER") == 0) COMPLETE_WITH_CONST("VALIDATOR"); + /* CREATE FOREIGN TABLE name (...) */ + else if (pg_strcasecmp(prev5_wd, "CREATE") == 0 && + pg_strcasecmp(prev4_wd, "FOREIGN") == 0 && + pg_strcasecmp(prev3_wd, "TABLE") == 0 && + prev_wd[strlen(prev_wd) - 1] == ')') + { + static const char *const list_CREATE_FOREIGN_TABLE[] = + {"INHERTIS", "SERVER", NULL}; + + COMPLETE_WITH_LIST(list_CREATE_FOREIGN_TABLE); + } + /* CREATE INDEX */ /* First off we complete CREATE UNIQUE with "INDEX" */ else if (pg_strcasecmp(prev2_wd, "CREATE") == 0 && @@ -1839,6 +1899,14 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_DROPCR); } } + else if (pg_strcasecmp(prev2_wd, "DROP") == 0 && + pg_strcasecmp(prev_wd, "FOREIGN") == 0) + { + static const char *const drop_CREATE_FOREIGN[] = + {"DATA WRAPPER", "TABLE", NULL}; + + COMPLETE_WITH_LIST(drop_CREATE_FOREIGN); + } else if (pg_strcasecmp(prev4_wd, "DROP") == 0 && (pg_strcasecmp(prev3_wd, "AGGREGATE") == 0 || pg_strcasecmp(prev3_wd, "FUNCTION") == 0) && @@ -1943,6 +2011,12 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "WRAPPER") == 0) COMPLETE_WITH_QUERY(Query_for_list_of_fdws); +/* FOREIGN TABLE */ + else if (pg_strcasecmp(prev3_wd, "CREATE") != 0 && + pg_strcasecmp(prev2_wd, "FOREIGN") == 0 && + pg_strcasecmp(prev_wd, "TABLE") == 0) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables, NULL); + /* GRANT && REVOKE*/ /* Complete GRANT/REVOKE with a list of privileges */ else if (pg_strcasecmp(prev_wd, "GRANT") == 0 || @@ -1974,10 +2048,11 @@ psql_completion(char *text, int start, int end) else if ((pg_strcasecmp(prev3_wd, "GRANT") == 0 || pg_strcasecmp(prev3_wd, "REVOKE") == 0) && pg_strcasecmp(prev_wd, "ON") == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, " UNION SELECT 'DATABASE'" " UNION SELECT 'FOREIGN DATA WRAPPER'" " UNION SELECT 'FOREIGN SERVER'" + " UNION SELECT 'FOREIGN TABLE'" " UNION SELECT 'FUNCTION'" " UNION SELECT 'LANGUAGE'" " UNION SELECT 'LARGE OBJECT'" @@ -1989,7 +2064,7 @@ psql_completion(char *text, int start, int end) pg_strcasecmp(prev_wd, "FOREIGN") == 0) { static const char *const list_privilege_foreign[] = - {"DATA WRAPPER", "SERVER", NULL}; + {"DATA WRAPPER", "SERVER", "TABLE", NULL}; COMPLETE_WITH_LIST(list_privilege_foreign); } @@ -2510,7 +2585,7 @@ psql_completion(char *text, int start, int end) else if (pg_strcasecmp(prev_wd, "FROM") == 0 && pg_strcasecmp(prev3_wd, "COPY") != 0 && pg_strcasecmp(prev3_wd, "\\copy") != 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); /* Backslash commands */ @@ -2548,7 +2623,7 @@ psql_completion(char *text, int start, int end) else if (strncmp(prev_wd, "\\dn", strlen("\\dn")) == 0) COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (strncmp(prev_wd, "\\dp", strlen("\\dp")) == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvf, NULL); else if (strncmp(prev_wd, "\\ds", strlen("\\ds")) == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_sequences, NULL); else if (strncmp(prev_wd, "\\dt", strlen("\\dt")) == 0) @@ -2562,7 +2637,7 @@ psql_completion(char *text, int start, int end) /* must be at end of \d list */ else if (strncmp(prev_wd, "\\d", strlen("\\d")) == 0) - COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisv, NULL); + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tisvf, NULL); else if (strcmp(prev_wd, "\\ef") == 0) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL); diff --git a/src/include/catalog/pg_foreign_data_wrapper.h b/src/include/catalog/pg_foreign_data_wrapper.h index 6dd01e60f6..b1fc2adf39 100644 --- a/src/include/catalog/pg_foreign_data_wrapper.h +++ b/src/include/catalog/pg_foreign_data_wrapper.h @@ -33,6 +33,7 @@ CATALOG(pg_foreign_data_wrapper,2328) NameData fdwname; /* foreign-data wrapper name */ Oid fdwowner; /* FDW owner */ Oid fdwvalidator; /* optional validation function */ + Oid fdwhandler; /* foreign-data routines function */ /* VARIABLE LENGTH FIELDS start here. */ @@ -52,11 +53,12 @@ typedef FormData_pg_foreign_data_wrapper *Form_pg_foreign_data_wrapper; * ---------------- */ -#define Natts_pg_foreign_data_wrapper 5 +#define Natts_pg_foreign_data_wrapper 6 #define Anum_pg_foreign_data_wrapper_fdwname 1 #define Anum_pg_foreign_data_wrapper_fdwowner 2 #define Anum_pg_foreign_data_wrapper_fdwvalidator 3 -#define Anum_pg_foreign_data_wrapper_fdwacl 4 -#define Anum_pg_foreign_data_wrapper_fdwoptions 5 +#define Anum_pg_foreign_data_wrapper_fdwhandler 4 +#define Anum_pg_foreign_data_wrapper_fdwacl 5 +#define Anum_pg_foreign_data_wrapper_fdwoptions 6 #endif /* PG_FOREIGN_DATA_WRAPPER_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 61c6b27d1d..28dc8a4a29 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3858,6 +3858,10 @@ DATA(insert OID = 2777 ( anynonarray_in PGNSP PGUID 12 1 0 0 f f f t f i 1 0 27 DESCR("I/O"); DATA(insert OID = 2778 ( anynonarray_out PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2275 "2776" _null_ _null_ _null_ _null_ anynonarray_out _null_ _null_ _null_ )); DESCR("I/O"); +DATA(insert OID = 3116 ( fdw_handler_in PGNSP PGUID 12 1 0 0 f f f f f i 1 0 3115 "2275" _null_ _null_ _null_ _null_ fdw_handler_in _null_ _null_ _null_ )); +DESCR("I/O"); +DATA(insert OID = 3117 ( fdw_handler_out PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2275 "3115" _null_ _null_ _null_ _null_ fdw_handler_out _null_ _null_ _null_ )); +DESCR("I/O"); /* cryptographic */ DATA(insert OID = 2311 ( md5 PGNSP PGUID 12 1 0 0 f f f t f i 1 0 25 "25" _null_ _null_ _null_ _null_ md5_text _null_ _null_ _null_ )); @@ -4195,6 +4199,8 @@ DATA(insert OID = 3035 ( pg_listening_channels PGNSP PGUID 12 1 10 0 f f f t t DESCR("get the channels that the current backend listens to"); DATA(insert OID = 3036 ( pg_notify PGNSP PGUID 12 1 0 0 f f f f f v 2 0 2278 "25 25" _null_ _null_ _null_ _null_ pg_notify _null_ _null_ _null_ )); DESCR("send a notification event"); +DATA(insert OID = 3539 ( pg_foreign_connections PGNSP PGUID 12 1 1000 0 f f f t t s 0 0 2249 "" "{25,26,26}" "{o,o,o}" "{connection_name,serverid,userid}" _null_ pg_foreign_connections _null_ _null_ _null_ )); +DESCR("get the foreign server connections for this session"); /* non-persistent series generator */ DATA(insert OID = 1066 ( generate_series PGNSP PGUID 12 1 1000 0 f f f t t i 3 0 23 "23 23 23" _null_ _null_ _null_ _null_ generate_series_step_int4 _null_ _null_ _null_ )); diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index fc2c3066f0..28393b6794 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -624,6 +624,8 @@ DATA(insert OID = 2776 ( anynonarray PGNSP PGUID 4 t p P f t \054 0 0 0 anynona #define ANYNONARRAYOID 2776 DATA(insert OID = 3500 ( anyenum PGNSP PGUID 4 t p P f t \054 0 0 0 anyenum_in anyenum_out - - - - - i p f 0 -1 0 _null_ _null_ )); #define ANYENUMOID 3500 +DATA(insert OID = 3115 ( fdw_handler PGNSP PGUID 4 t p P f t \054 0 0 0 fdw_handler_in fdw_handler_out - - - - - i p f 0 -1 0 _null_ _null_ )); +#define FDW_HANDLEROID 3115 /* diff --git a/src/include/executor/nodeForeignscan.h b/src/include/executor/nodeForeignscan.h new file mode 100644 index 0000000000..57da138a3c --- /dev/null +++ b/src/include/executor/nodeForeignscan.h @@ -0,0 +1,26 @@ +/*------------------------------------------------------------------------- + * + * nodeForeignscan.h + * + * + * + * Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * $PostgreSQL$ + * + *------------------------------------------------------------------------- + */ +#ifndef NODEFOREIGNSCAN_H +#define NODEFOREIGNSCAN_H + +#include "nodes/execnodes.h" + +extern ForeignScanState *ExecInitForeignScan(ForeignScan *node, EState *estate, int eflags); +extern TupleTableSlot *ExecForeignScan(ForeignScanState *node); +extern void ExecEndForeignScan(ForeignScanState *node); +extern void ExecForeignMarkPos(ForeignScanState *node); +extern void ExecForeignRestrPos(ForeignScanState *node); +extern void ExecForeignReScan(ForeignScanState *node); + +#endif /* NODEFOREIGNSCAN_H */ diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h index e8bdb679ed..b9cd134f6c 100644 --- a/src/include/foreign/foreign.h +++ b/src/include/foreign/foreign.h @@ -31,6 +31,7 @@ typedef enum ServerOpt = 1, /* options applicable to SERVER */ UserMappingOpt = 2, /* options for USER MAPPING */ FdwOpt = 4, /* options for FOREIGN DATA WRAPPER */ + ForeignTableOpt = 8, /* options for FOREIGN TABLE */ } GenericOptionFlags; typedef struct ForeignDataWrapper @@ -39,6 +40,7 @@ typedef struct ForeignDataWrapper Oid owner; /* FDW owner user Oid */ char *fdwname; /* Name of the FDW */ Oid fdwvalidator; + Oid fdwhandler; List *options; /* fdwoptions as DefElem list */ } ForeignDataWrapper; @@ -67,6 +69,10 @@ typedef struct ForeignTable List *options; /* ftoptions as DefElem list */ } ForeignTable; +typedef struct FdwRoutine FdwRoutine; +typedef struct FSConnection FSConnection; +typedef struct FdwReply FdwReply; + extern ForeignServer *GetForeignServer(Oid serverid); extern ForeignServer *GetForeignServerByName(const char *name, bool missing_ok); extern Oid GetForeignServerOidByName(const char *name, bool missing_ok); @@ -76,9 +82,19 @@ extern ForeignDataWrapper *GetForeignDataWrapperByName(const char *name, bool missing_ok); extern Oid GetForeignDataWrapperOidByName(const char *name, bool missing_ok); extern ForeignTable *GetForeignTable(Oid relid); +extern FdwRoutine *GetFdwRoutine(Oid fdwhandler); extern bool IsForeignTable(Oid relid); /* ALTER FOREIGN TABLE ... OPTIONS (...) handlers */ extern void ATExecGenericOptions(Relation rel, List *options); +/* foreign server connections */ +extern FSConnection *ConnectToForeignServer(FdwRoutine *routine, + ForeignServer *server, UserMapping *user, const char *conname); +extern void RegisterFSConnection(FSConnection *conn, FdwRoutine *routine, + const char *conname); +extern FSConnection *GetFSConnectionByName(const char *conname, FdwRoutine **routine); +extern bool DisconnectForeignServer(const char *conname); +extern void DisconnectAllForeignServers(void); + #endif /* FOREIGN_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 8a1f2ee047..5b899c0501 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -17,6 +17,7 @@ #include "access/genam.h" #include "access/heapam.h" #include "access/skey.h" +#include "foreign/foreign.h" #include "nodes/params.h" #include "nodes/plannodes.h" #include "nodes/tidbitmap.h" @@ -1356,6 +1357,66 @@ typedef struct WorkTableScanState RecursiveUnionState *rustate; } WorkTableScanState; +/* ---------------- + * ForeignScanState information + * + * ForeignScan nodes are used to scan the foreign table managed by + * a foreign server. + * ---------------- + */ +typedef struct ForeignScanState +{ + ScanState ss; /* its first field is NodeTag */ + + FdwRoutine *routine; /* set of FDW routines */ + ForeignDataWrapper *wrapper;/* foreign data wrapper */ + ForeignServer *server; /* foreign server */ + FSConnection *conn; /* connection to the foreign server */ + UserMapping *user; /* user mapping */ + ForeignTable *table; /* foreign table */ + FdwReply *reply; /* private data for each data wrapper */ +} ForeignScanState; + +/* + * Common interface routines of FDW baed on SQL/MED standard. + * Each FDW must implement these routines. + */ +struct FdwRoutine +{ + /* + * Connect to the foreign server identified by server and user. + * Each FDW also manages connection cache because the FDW only knows the + * acutal size of the concrete connection object. + */ + FSConnection* (*ConnectServer)(ForeignServer *server, UserMapping *user); + + /* + * Disconnect and free FSConnection. + */ + void (*FreeFSConnection)(FSConnection *conn); + + /* + * Deparse query request and open a cursor for the foreign scan. + */ + void (*Open)(ForeignScanState *scanstate); + + /* + * Fetch the next record and fill tupleslot with it. + */ + void (*Iterate)(ForeignScanState *scanstate); + + /* + * End the foreign scan and close cursor. + */ + void (*Close)(ForeignScanState *scanstate); + + /* + * Re-initialize the foreign scan, used when the INNER executor node is + * executed again. + */ + void (*ReOpen)(ForeignScanState *scanstate); +}; + /* ---------------------------------------------------------------- * Join State Information * ---------------------------------------------------------------- diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index fdcdabed8a..b3ef014bd7 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -59,6 +59,7 @@ typedef enum NodeTag T_ValuesScan, T_CteScan, T_WorkTableScan, + T_ForeignScan, T_Join, T_NestLoop, T_MergeJoin, @@ -101,6 +102,7 @@ typedef enum NodeTag T_ValuesScanState, T_CteScanState, T_WorkTableScanState, + T_ForeignScanState, T_JoinState, T_NestLoopState, T_MergeJoinState, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a331eb36db..748a6a272a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1522,17 +1522,16 @@ typedef struct CreateFdwStmt { NodeTag type; char *fdwname; /* foreign-data wrapper name */ - List *validator; /* optional validator function (qual. name) */ - List *options; /* generic options to FDW */ + List *fdw_options; /* VALIDATOR/HANDLER conbination */ + List *gen_options; /* generic options to FDW */ } CreateFdwStmt; typedef struct AlterFdwStmt { NodeTag type; char *fdwname; /* foreign-data wrapper name */ - List *validator; /* optional validator function (qual. name) */ - bool change_validator; - List *options; /* generic options to FDW */ + List *fdw_options; /* VALIDATOR/HANDLER conbination */ + List *gen_options; /* generic options to FDW */ } AlterFdwStmt; typedef struct DropFdwStmt diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index f2f99f4a2b..d30fba46ea 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -400,6 +400,15 @@ typedef struct WorkTableScan int wtParam; /* ID of Param representing work table */ } WorkTableScan; +/* ---------------- + * ForeignScan node + * ---------------- + */ +typedef struct ForeignScan +{ + Scan scan; +} ForeignScan; + /* * ========== diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h index 63641b9cc8..7993eeeefb 100644 --- a/src/include/optimizer/cost.h +++ b/src/include/optimizer/cost.h @@ -81,6 +81,7 @@ extern void cost_functionscan(Path *path, PlannerInfo *root, extern void cost_valuesscan(Path *path, PlannerInfo *root, RelOptInfo *baserel); extern void cost_ctescan(Path *path, PlannerInfo *root, RelOptInfo *baserel); +extern void cost_foreignscan(Path *path, PlannerInfo *root, RelOptInfo *baserel); extern void cost_recursive_union(Plan *runion, Plan *nrterm, Plan *rterm); extern void cost_sort(Path *path, PlannerInfo *root, List *pathkeys, Cost input_cost, double tuples, int width, diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 5e0ebe046b..a4df59f666 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -56,6 +56,7 @@ extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel); extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel); extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel); extern Path *create_worktablescan_path(PlannerInfo *root, RelOptInfo *rel); +extern Path *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel); extern NestPath *create_nestloop_path(PlannerInfo *root, RelOptInfo *joinrel, diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index f4b2a962c8..cb36d10980 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -514,6 +514,8 @@ extern Datum pg_node_tree_in(PG_FUNCTION_ARGS); extern Datum pg_node_tree_out(PG_FUNCTION_ARGS); extern Datum pg_node_tree_recv(PG_FUNCTION_ARGS); extern Datum pg_node_tree_send(PG_FUNCTION_ARGS); +extern Datum fdw_handler_in(PG_FUNCTION_ARGS); +extern Datum fdw_handler_out(PG_FUNCTION_ARGS); /* regexp.c */ extern Datum nameregexeq(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 00f6e058ee..8837c691f3 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -39,12 +39,12 @@ CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR ERROR: function bar(text[], oid) does not exist CREATE FOREIGN DATA WRAPPER foo; \dew - List of foreign-data wrappers - Name | Owner | Validator -------------+-------------------+-------------------------- - dummy | foreign_data_user | - - foo | foreign_data_user | - - postgresql | foreign_data_user | postgresql_fdw_validator + List of foreign-data wrappers + Name | Owner | Handler | Validator +------------+-------------------+---------+-------------------------- + dummy | foreign_data_user | - | - + foo | foreign_data_user | - | - + postgresql | foreign_data_user | - | postgresql_fdw_validator (3 rows) CREATE FOREIGN DATA WRAPPER foo; -- duplicate @@ -52,12 +52,12 @@ ERROR: foreign-data wrapper "foo" already exists DROP FOREIGN DATA WRAPPER foo; CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1'); \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+------------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {testing=1} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+------------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {testing=1} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) DROP FOREIGN DATA WRAPPER foo; @@ -65,12 +65,12 @@ CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR ERROR: option "testing" provided more than once CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2'); \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+----------------------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {testing=1,another=2} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+----------------------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {testing=1,another=2} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) DROP FOREIGN DATA WRAPPER foo; @@ -81,12 +81,12 @@ HINT: Must be superuser to create a foreign-data wrapper. RESET ROLE; CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator; \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | postgresql_fdw_validator | | - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | postgresql_fdw_validator | | + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) -- ALTER FOREIGN DATA WRAPPER @@ -98,12 +98,12 @@ ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR ERROR: function bar(text[], oid) does not exist ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR; \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2'); @@ -113,34 +113,34 @@ ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR ERROR: option "c" not found ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x); \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+----------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {a=1,b=2} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+----------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {a=1,b=2} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4'); \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+----------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {b=3,c=4} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+----------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {b=3,c=4} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2'); ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR ERROR: option "b" provided more than once \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {b=3,c=4,a=2} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {b=3,c=4,a=2} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) SET ROLE regress_test_role; @@ -150,12 +150,12 @@ HINT: Must be superuser to alter a foreign-data wrapper. SET ROLE regress_test_role_super; ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+------------------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | {b=3,c=4,a=2,d=5} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+------------------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | {b=3,c=4,a=2,d=5} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR @@ -169,12 +169,12 @@ ERROR: permission denied to alter foreign-data wrapper "foo" HINT: Must be superuser to alter a foreign-data wrapper. RESET ROLE; \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------------+--------------------------+-------------------+------------------- - dummy | foreign_data_user | - | | - foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------------+---------+--------------------------+-------------------+------------------- + dummy | foreign_data_user | - | - | | + foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) -- DROP FOREIGN DATA WRAPPER @@ -183,12 +183,12 @@ ERROR: foreign-data wrapper "nonexistent" does not exist DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent; NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------------+--------------------------+-------------------+------------------- - dummy | foreign_data_user | - | | - foo | regress_test_role_super | - | | {b=3,c=4,a=2,d=5} - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------------+---------+--------------------------+-------------------+------------------- + dummy | foreign_data_user | - | - | | + foo | regress_test_role_super | - | - | | {b=3,c=4,a=2,d=5} + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) DROP ROLE regress_test_role_super; -- ERROR @@ -203,23 +203,23 @@ ALTER ROLE regress_test_role_super SUPERUSER; DROP FOREIGN DATA WRAPPER foo; DROP ROLE regress_test_role_super; \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------- - dummy | foreign_data_user | - | | - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------- + dummy | foreign_data_user | - | - | | + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (2 rows) CREATE FOREIGN DATA WRAPPER foo; CREATE SERVER s1 FOREIGN DATA WRAPPER foo; CREATE USER MAPPING FOR current_user SERVER s1; \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------- - dummy | foreign_data_user | - | | - foo | foreign_data_user | - | | - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------- + dummy | foreign_data_user | - | - | | + foo | foreign_data_user | - | - | | + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (3 rows) \des+ @@ -251,11 +251,11 @@ NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to server s1 drop cascades to user mapping for foreign_data_user \dew+ - List of foreign-data wrappers - Name | Owner | Validator | Access privileges | Options -------------+-------------------+--------------------------+-------------------+--------- - dummy | foreign_data_user | - | | - postgresql | foreign_data_user | postgresql_fdw_validator | | + List of foreign-data wrappers + Name | Owner | Handler | Validator | Access privileges | Options +------------+-------------------+---------+--------------------------+-------------------+--------- + dummy | foreign_data_user | - | - | | + postgresql | foreign_data_user | - | postgresql_fdw_validator | | (2 rows) \des+ diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index c7a796a2a5..314f4cbf2a 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1280,6 +1280,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem -----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); + pg_foreign_connections | SELECT c.connection_name AS conname, s.srvname, u.rolname AS usename, d.fdwname FROM (((pg_foreign_connections() c(connection_name, serverid, userid) LEFT JOIN pg_authid u ON ((c.userid = u.oid))) LEFT JOIN pg_foreign_server s ON ((c.serverid = s.oid))) LEFT JOIN pg_foreign_data_wrapper d ON ((s.srvfdw = d.oid))); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); pg_locks | SELECT l.locktype, l.database, l.relation, l.page, l.tuple, l.virtualxid, l.transactionid, l.classid, l.objid, l.objsubid, l.virtualtransaction, l.pid, l.mode, l.granted FROM pg_lock_status() l(locktype, database, relation, page, tuple, virtualxid, transactionid, classid, objid, objsubid, virtualtransaction, pid, mode, granted); @@ -1334,7 +1335,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color)))); street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath); toyemp | SELECT emp.name, emp.age, emp.location, (12 * emp.salary) AS annualsal FROM emp; -(56 rows) +(57 rows) SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; -- 2.39.5