From 3b2a9fd91ca4ab70e3256a554de729d2dbea4f09 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Tue, 1 Jul 2014 15:26:25 +0900 Subject: [PATCH 2/2] Add support of IMPORT FOREIGN SCHEMA in postgres_fdw Documentation is added as well. --- contrib/postgres_fdw/expected/postgres_fdw.out | 136 ++++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 213 +++++++++++++++++++++++++ contrib/postgres_fdw/sql/postgres_fdw.sql | 49 ++++++ doc/src/sgml/postgres-fdw.sgml | 31 ++++ 4 files changed, 429 insertions(+) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 2e49ee3..2b5ec72 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2834,3 +2834,139 @@ NOTICE: NEW: (13,"test triggered !") (0,27) (1 row) +-- Tests for IMPORT FOREIGN SCHEMA +CREATE schema import_destination; +CREATE schema import_source; +CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); +CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text); +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_destination; +\det+ import_destination + List of foreign tables + Schema | Table | Server | FDW Options | Description +--------------------+-------+----------+------------------------------------------------+------------- + import_destination | t1 | loopback | (schema_name 'import_source', table_name 't1') | + import_destination | t2 | loopback | (schema_name 'import_source', table_name 't2') | +(2 rows) + +\d import_destination.t1 + Foreign table "import_destination.t1" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | not null | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't1') + +\d import_destination.t2 + Foreign table "import_destination.t2" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | | + c3 | text | | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't2') + +DROP SCHEMA import_destination cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to foreign table import_destination.t1 +drop cascades to foreign table import_destination.t2 +-- Options +CREATE schema import_destination; +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_destination +OPTIONS (ignore_not_null 'true'); +\det+ import_destination + List of foreign tables + Schema | Table | Server | FDW Options | Description +--------------------+-------+----------+------------------------------------------------+------------- + import_destination | t1 | loopback | (schema_name 'import_source', table_name 't1') | + import_destination | t2 | loopback | (schema_name 'import_source', table_name 't2') | +(2 rows) + +\d import_destination.t1 + Foreign table "import_destination.t1" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't1') + +\d import_destination.t2 + Foreign table "import_destination.t2" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | | + c3 | text | | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't2') + +DROP SCHEMA import_destination cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to foreign table import_destination.t1 +drop cascades to foreign table import_destination.t2 +-- Checks for LIMIT TO and EXCEPT +CREATE schema import_destination; +IMPORT FOREIGN SCHEMA import_source LIMIT TO (inexistent) FROM SERVER loopback INTO import_destination; -- ERROR +ERROR: List of CREATE FOREIGN TABLE queries generated does not match the number of items in LIMIT TO +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1) FROM SERVER loopback INTO import_destination; +\det+ import_destination; + List of foreign tables + Schema | Table | Server | FDW Options | Description +--------------------+-------+----------+------------------------------------------------+------------- + import_destination | t1 | loopback | (schema_name 'import_source', table_name 't1') | +(1 row) + +IMPORT FOREIGN SCHEMA import_source EXCEPT (t1) FROM SERVER loopback INTO import_destination; +\det+ import_destination; + List of foreign tables + Schema | Table | Server | FDW Options | Description +--------------------+-------+----------+------------------------------------------------+------------- + import_destination | t1 | loopback | (schema_name 'import_source', table_name 't1') | + import_destination | t2 | loopback | (schema_name 'import_source', table_name 't2') | +(2 rows) + +\d import_destination.t1 + Foreign table "import_destination.t1" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | not null | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't1') + +\d import_destination.t2 + Foreign table "import_destination.t2" + Column | Type | Modifiers | FDW Options +--------+-------------------+-----------+------------- + c1 | integer | | + c2 | character varying | | + c3 | text | | +Server: loopback +FDW Options: (schema_name 'import_source', table_name 't2') + +DROP SCHEMA import_destination cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to foreign table import_destination.t1 +drop cascades to foreign table import_destination.t2 +-- Missing type in foreign server +-- Create a new database for this special case +CREATE DATABASE postgres_fdw_import_test; +CREATE SCHEMA import_destination; +\c postgres_fdw_import_test +CREATE TYPE typ1 AS (m1 int, m2 varchar); +CREATE SCHEMA import_source; +CREATE TABLE import_source.t1 (c1 int, c2 typ1); +\c contrib_regression +CREATE SERVER postgres_fdw_import_server +FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres_fdw_import_test'); +CREATE USER MAPPING FOR CURRENT_USER SERVER postgres_fdw_import_server; +-- Error +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1) +FROM SERVER postgres_fdw_import_server INTO import_destination; +ERROR: IMPORT of table "t1" failed because of missing type "typ1" defined on remote but not locally: +-- Clean up +DROP SERVER postgres_fdw_import_server CASCADE; +NOTICE: drop cascades to user mapping for ioltas +DROP SCHEMA import_destination CASCADE; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 7dd43a9..c19aa37 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -32,6 +32,7 @@ #include "optimizer/restrictinfo.h" #include "optimizer/var.h" #include "parser/parsetree.h" +#include "parser/parse_type.h" #include "utils/builtins.h" #include "utils/guc.h" #include "utils/lsyscache.h" @@ -286,6 +287,11 @@ static void postgresExplainForeignModify(ModifyTableState *mtstate, static bool postgresAnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages); +static List *postgresImportForeignSchema(ForeignServer *server, + const char *remote_schema, + ImportForeignSchemaType import_type, + List *table_names, + List *options); /* * Helper functions @@ -363,6 +369,9 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) /* Support functions for ANALYZE */ routine->AnalyzeForeignTable = postgresAnalyzeForeignTable; + /* Support functions for IMPORT FOREIGN SCHEMA */ + routine->ImportForeignSchema = postgresImportForeignSchema; + PG_RETURN_POINTER(routine); } @@ -2347,6 +2356,210 @@ postgresAnalyzeForeignTable(Relation relation, return true; } + +/* + * Map a remote schema to a local one. + */ +static List * +postgresImportForeignSchema(ForeignServer *server, + const char *remote_schema, + ImportForeignSchemaType import_type, + List *table_names, + List *options) +{ + List *tables = NULL; + ListCell *lc; + Oid userid = GetUserId(); + UserMapping *mapping = GetUserMapping(userid, server->serverid); + PGconn *conn = GetConnection(server, mapping, false); + PGresult *res; + int numrows, + i; + StringInfoData buf; + bool ignore_not_null = false;; + + /* Parse options */ + foreach(lc, options) + { + DefElem *def = (DefElem *) lfirst(lc); + if (strcmp(def->defname, "ignore_not_null") == 0) + { + ignore_not_null = defGetBoolean(def); + break; + } + } + + /* Check that the schema really exists */ + initStringInfo(&buf); + appendStringInfo(&buf, + "SELECT 1 FROM pg_namespace WHERE nspname = '%s'", + remote_schema); + res = PQexec(conn, buf.data); + + /* Process with remote failed */ + if (PQresultStatus(res) != PGRES_TUPLES_OK) + pgfdw_report_error(ERROR, res, conn, true, buf.data); + + /* Schema really does not exist */ + if (PQntuples(res) != 1) + ereport(ERROR, + (errcode(ERRCODE_FDW_SCHEMA_NOT_FOUND), + errmsg("Schema %s not present on foreign server", remote_schema))); + + /* Clean up */ + PQclear(res); + resetStringInfo(&buf); + + /* + * Fetch all table data from this schema. The base query fetches + * back all the relation data with the table name, attributes and + * their associated types, on top of which are applied restrictions + * decided by EXCEPT or LIMIT TO. + */ + appendStringInfo(&buf, + "SELECT relname, " + " attname, " + " typname, " + " atttypmod::int4 AS atttypmod, " + " attnotnull::bool as attnotnull " + "FROM pg_class " + " LEFT JOIN pg_namespace ON " + " pg_class.relnamespace = pg_namespace.oid " + " LEFT JOIN pg_attribute ON " + " pg_class.oid = pg_attribute.attrelid " + " AND pg_attribute.attnum >= 0 " + " AND NOT pg_attribute.attisdropped " + " LEFT JOIN pg_type ON " + " pg_type.oid = pg_attribute.atttypid " + "WHERE pg_class.relkind IN ('r', 'v', 'f', 'm') " + " AND pg_namespace.nspname = '%s' ", + remote_schema); + + /* Apply restrictions for LIMIT TO and EXCEPT */ + if (import_type == FDW_IMPORT_SCHEMA_LIMIT_TO || + import_type == FDW_IMPORT_SCHEMA_EXCEPT) + { + bool first_item = true; + appendStringInfo(&buf, "AND pg_class.relname "); + + /* Small extra for EXCEPT */ + if (import_type == FDW_IMPORT_SCHEMA_EXCEPT) + appendStringInfo(&buf, "NOT "); + appendStringInfo(&buf, "IN ("); + + /* Append list of table names in IN clause */ + foreach(lc, table_names) + { + RangeVar *rv = lfirst(lc); + appendStringInfo(&buf, ""); + if (first_item) + first_item = false; + else + appendStringInfo(&buf, ", "); + appendStringInfo(&buf, "'%s'", rv->relname); + } + appendStringInfo(&buf, ") "); + } + + /* Append ORDER BY at the end of query to ensure output ordering */ + appendStringInfo(&buf, "ORDER BY pg_class.relname, pg_attribute.attnum"); + + /* Now it is time to rocks... */ + res = PQexec(conn, buf.data); + if (PQresultStatus(res) != PGRES_TUPLES_OK) + pgfdw_report_error(ERROR, res, conn, true, buf.data); + + /* Process results */ + numrows = PQntuples(res); + for (i = 0; i < numrows; i++) + { + CreateForeignTableStmt *stmt = makeNode(CreateForeignTableStmt); + char *tablename; + + /* Get table name and begin object generation */ + tablename = strdup(PQgetvalue(res, i, 0)); + + /* Setup the base relation information */ + stmt->base.relation = makeRangeVar(NULL, tablename, 0); + stmt->servername = server->servername; + + /* + * It is the role of server to set the schema where tables + * are imported, set it to NULL here. + */ + stmt->base.relation->schemaname = NULL; + + /* + * Loop on results as long as we can fetch attribute data for + * this relation. + */ + while (i < numrows && strcmp(PQgetvalue(res, i, 0), + tablename) == 0) + { + ColumnDef *new_column = makeNode(ColumnDef); + char *typname = PQgetvalue(res, i, 2); + int32 typmod = atoi(PQgetvalue(res, i, 3)); + Oid typid; + + /* Column name */ + new_column->colname = strdup(PQgetvalue(res, i, 1)); + /* Assign NULL/NOT NULL if necessary */ + if (!ignore_not_null) + new_column->is_not_null = PQgetvalue(res, i, 4)[0] == 't'; + + /* Check type OID and assign it */ + typid = LookupTypeNameOid(NULL, makeTypeName(typname), true); + if (!OidIsValid(typid)) + ereport(ERROR, + (errcode(ERRCODE_FDW_SCHEMA_NOT_FOUND), + errmsg("IMPORT of table \"%s\" failed because of missing type " + "\"%s\" defined on remote but not locally: ", + tablename, typname))); + new_column->typeName = makeTypeNameFromOid(typid, typmod); + + /* Append attribute to relation list */ + stmt->base.tableElts = lappend(stmt->base.tableElts, new_column); + + /* Get ready to look at the next row */ + i++; + } + + /* Get one row back, this relation processing is done */ + i--; + + /* + * Add schema_name and table_name options table_name is added to + * survive a foreign table rename. + */ + stmt->options = lappend(stmt->options, + makeDefElem("schema_name", (Node *) makeString((char *) remote_schema))); + stmt->options = lappend(stmt->options, + makeDefElem("table_name", (Node *) makeString(tablename))); + tables = lappend(tables, stmt); + } + + /* Clean up */ + PQclear(res); + + /* + * Check that the number of objects generated has the same number of + * items as what is specified in LIMIT TO, if of course it has been + * specified by caller. + * + * XXX: Do we bother checking on foreign server what are the tables + * missing but specified by the user? + */ + if (import_type == FDW_IMPORT_SCHEMA_LIMIT_TO && + list_length(table_names) != list_length(tables)) + ereport(ERROR, + (errcode(ERRCODE_FDW_TABLE_NOT_FOUND), + errmsg("List of CREATE FOREIGN TABLE queries generated does not " + "match the number of items in LIMIT TO"))); + + ReleaseConnection(conn); + return tables; +} + /* * Acquire a random sample of rows from foreign table managed by postgres_fdw. * diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 6187839..779de9b 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -609,3 +609,52 @@ UPDATE rem1 SET f2 = 'testo'; -- Test returning a system attribute INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; + +-- Tests for IMPORT FOREIGN SCHEMA +CREATE schema import_destination; +CREATE schema import_source; +CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL); +CREATE TABLE import_source.t2 (c1 int, c2 varchar NULL, c3 text); +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_destination; +\det+ import_destination +\d import_destination.t1 +\d import_destination.t2 +DROP SCHEMA import_destination cascade; +-- Options +CREATE schema import_destination; +IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_destination +OPTIONS (ignore_not_null 'true'); +\det+ import_destination +\d import_destination.t1 +\d import_destination.t2 +DROP SCHEMA import_destination cascade; + +-- Checks for LIMIT TO and EXCEPT +CREATE schema import_destination; +IMPORT FOREIGN SCHEMA import_source LIMIT TO (inexistent) FROM SERVER loopback INTO import_destination; -- ERROR +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1) FROM SERVER loopback INTO import_destination; +\det+ import_destination; +IMPORT FOREIGN SCHEMA import_source EXCEPT (t1) FROM SERVER loopback INTO import_destination; +\det+ import_destination; +\d import_destination.t1 +\d import_destination.t2 +DROP SCHEMA import_destination cascade; + +-- Missing type in foreign server +-- Create a new database for this special case +CREATE DATABASE postgres_fdw_import_test; +CREATE SCHEMA import_destination; +\c postgres_fdw_import_test +CREATE TYPE typ1 AS (m1 int, m2 varchar); +CREATE SCHEMA import_source; +CREATE TABLE import_source.t1 (c1 int, c2 typ1); +\c contrib_regression +CREATE SERVER postgres_fdw_import_server +FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgres_fdw_import_test'); +CREATE USER MAPPING FOR CURRENT_USER SERVER postgres_fdw_import_server; +-- Error +IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1) +FROM SERVER postgres_fdw_import_server INTO import_destination; +-- Clean up +DROP SERVER postgres_fdw_import_server CASCADE; +DROP SCHEMA import_destination CASCADE; diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index e6f6e20..e72be86 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -83,6 +83,13 @@ of columns to the remote table is by name, not position. + + Importing foreign schemas from a foreign server is supported using + in postgres_fdw. + Trying to import table definitions using data types undefined on server + results in an error. Default column expressions are not imported. + + FDW Options of postgres_fdw @@ -291,6 +298,30 @@ + + + Importing Options + + + postgres_fdw is able to import foreign table definitions + from a foreign server. Import can be customized as well with a couple + of options. + + + + + + ignore_not_null + + + This option controls if NULL and NOT NULL + contraints are included in the definitions of the tables imported + from a foreign server. The default is false. + + + + + -- 2.0.0