Hi,
I noticed that there is no postgres_fdw option to control whether check
constraints on remote tables are included in the definitions of foreign
tables imported from a remote PG server when performing IMPORT FOREIGN
SCHEMA, while we now allow check constraints on foreign tables.
Attached is a patch for that. I'll add this to the next CF.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 3415,3420 **** CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text colla
--- 3415,3421 ----
CREATE TYPE typ1 AS (m1 int, m2 varchar);
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ ALTER TABLE import_source."x 4" ADD CONSTRAINT c1positive CHECK (c1 >= 0);
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
CREATE SCHEMA import_dest1;
***************
*** 3462,3467 **** FDW Options: (schema_name 'import_source', table_name 't3')
--- 3463,3470 ----
c1 | double precision | | (column_name 'c1')
C 2 | text | | (column_name 'C 2')
c3 | character varying(42) | | (column_name 'c3')
+ Check constraints:
+ "c1positive" CHECK (c1 >= 0::double precision)
Server: loopback
FDW Options: (schema_name 'import_source', table_name 'x 4')
***************
*** 3518,3523 **** FDW Options: (schema_name 'import_source', table_name 't3')
--- 3521,3528 ----
c1 | double precision | | (column_name 'c1')
C 2 | text | | (column_name 'C 2')
c3 | character varying(42) | | (column_name 'c3')
+ Check constraints:
+ "c1positive" CHECK (c1 >= 0::double precision)
Server: loopback
FDW Options: (schema_name 'import_source', table_name 'x 4')
***************
*** 3529,3535 **** FDW Options: (schema_name 'import_source', table_name 'x 5')
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
! OPTIONS (import_collate 'false', import_not_null 'false');
\det+ import_dest3
List of foreign tables
Schema | Table | Server | FDW Options | Description
--- 3534,3540 ----
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
! OPTIONS (import_collate 'false', import_not_null 'false', import_check 'false');
\det+ import_dest3
List of foreign tables
Schema | Table | Server | FDW Options | Description
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 2584,2589 **** postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
--- 2584,2590 ----
bool import_collate = true;
bool import_default = false;
bool import_not_null = true;
+ bool import_check = true;
ForeignServer *server;
UserMapping *mapping;
PGconn *conn;
***************
*** 2604,2609 **** postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
--- 2605,2612 ----
import_default = defGetBoolean(def);
else if (strcmp(def->defname, "import_not_null") == 0)
import_not_null = defGetBoolean(def);
+ else if (strcmp(def->defname, "import_check") == 0)
+ import_check = defGetBoolean(def);
else
ereport(ERROR,
(errcode(ERRCODE_FDW_INVALID_OPTION_NAME),
***************
*** 2824,2829 **** postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
--- 2827,2929 ----
/* Clean up */
PQclear(res);
res = NULL;
+
+ /*
+ * Fetch all table (and column) check constraint data from this schema,
+ * possibly restricted by EXCEPT or LIMIT TO.
+ */
+ if (import_check)
+ {
+ resetStringInfo(&buf);
+
+ appendStringInfoString(&buf,
+ "SELECT relname, "
+ " conname, "
+ " pg_get_constraintdef(r.oid) "
+ "FROM pg_class c "
+ " JOIN pg_namespace n ON "
+ " relnamespace = n.oid "
+ " JOIN pg_constraint r ON "
+ " conrelid = c.oid AND contype = 'c' ");
+
+ appendStringInfoString(&buf,
+ "WHERE c.relkind IN ('r', 'f') "
+ " AND n.nspname = ");
+ deparseStringLiteral(&buf, stmt->remote_schema);
+
+ /* Apply restrictions for LIMIT TO and EXCEPT */
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_LIMIT_TO ||
+ stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ {
+ bool first_item = true;
+
+ appendStringInfoString(&buf, " AND c.relname ");
+ if (stmt->list_type == FDW_IMPORT_SCHEMA_EXCEPT)
+ appendStringInfoString(&buf, "NOT ");
+ appendStringInfoString(&buf, "IN (");
+
+ /* Append list of table names within IN clause */
+ foreach(lc, stmt->table_list)
+ {
+ RangeVar *rv = (RangeVar *) lfirst(lc);
+
+ if (first_item)
+ first_item = false;
+ else
+ appendStringInfoString(&buf, ", ");
+ deparseStringLiteral(&buf, rv->relname);
+ }
+ appendStringInfoString(&buf, ")");
+ }
+
+ /* Append ORDER BY at the end of query to ensure output ordering */
+ appendStringInfo(&buf, " ORDER BY c.relname, r.conname");
+
+ /* Fetch the data */
+ res = PQexec(conn, buf.data);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(ERROR, res, conn, false, buf.data);
+
+ /* Process results */
+ numrows = PQntuples(res);
+ /* note: incrementation of i happens in inner loop's while() test */
+ for (i = 0; i < numrows;)
+ {
+ char *tablename = PQgetvalue(res, i, 0);
+ bool first_item = true;
+
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER FOREIGN TABLE %s ",
+ quote_identifier(tablename));
+
+ /* Scan all rows for this table */
+ do
+ {
+ char *conname = PQgetvalue(res, i, 1);
+ char *condef = PQgetvalue(res, i, 2);
+
+ if (first_item)
+ first_item = false;
+ else
+ appendStringInfoString(&buf, ", ");
+
+ /* Print constraint name and definition */
+ appendStringInfo(&buf, "ADD CONSTRAINT %s %s",
+ quote_identifier(conname),
+ condef);
+ }
+ while (++i < numrows &&
+ strcmp(PQgetvalue(res, i, 0), tablename) == 0);
+
+ appendStringInfoString(&buf, ";");
+
+ commands = lappend(commands, pstrdup(buf.data));
+ }
+
+ /* Clean up */
+ PQclear(res);
+ res = NULL;
+ }
}
PG_CATCH();
{
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 785,790 **** CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text colla
--- 785,791 ----
CREATE TYPE typ1 AS (m1 int, m2 varchar);
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
+ ALTER TABLE import_source."x 4" ADD CONSTRAINT c1positive CHECK (c1 >= 0);
CREATE TABLE import_source."x 5" (c1 float8);
ALTER TABLE import_source."x 5" DROP COLUMN c1;
***************
*** 801,807 **** IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
! OPTIONS (import_collate 'false', import_not_null 'false');
\det+ import_dest3
\d import_dest3.*
--- 802,808 ----
\d import_dest2.*
CREATE SCHEMA import_dest3;
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
! OPTIONS (import_collate 'false', import_not_null 'false', import_check 'false');
\det+ import_dest3
\d import_dest3.*
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 709,715 **** ImportForeignSchema (ImportForeignSchemaStmt *stmt, Oid serverOid);
called when executing <xref linkend="sql-importforeignschema">, and is
passed the parse tree for that statement, as well as the OID of the
foreign server to use. It should return a list of C strings, each of
! which must contain a <xref linkend="sql-createforeigntable"> command.
These strings will be parsed and executed by the core server.
</para>
--- 709,716 ----
called when executing <xref linkend="sql-importforeignschema">, and is
passed the parse tree for that statement, as well as the OID of the
foreign server to use. It should return a list of C strings, each of
! which must contain a <xref linkend="sql-createforeigntable"> or
! <xref linkend="sql-alterforeigntable"> command.
These strings will be parsed and executed by the core server.
</para>
***************
*** 736,742 **** ImportForeignSchema (ImportForeignSchemaStmt *stmt, Oid serverOid);
The FDW may ignore the <structfield>local_schema</> field of
the <structname>ImportForeignSchemaStmt</>, because the core server
will automatically insert that name into the parsed <command>CREATE
! FOREIGN TABLE</> commands.
</para>
<para>
--- 737,743 ----
The FDW may ignore the <structfield>local_schema</> field of
the <structname>ImportForeignSchemaStmt</>, because the core server
will automatically insert that name into the parsed <command>CREATE
! FOREIGN TABLE</> and <command>ALTER FOREIGN TABLE</> commands.
</para>
<para>
*** a/doc/src/sgml/postgres-fdw.sgml
--- b/doc/src/sgml/postgres-fdw.sgml
***************
*** 349,360 ****
</para>
</listitem>
</varlistentry>
</variablelist>
<para>
! Note that constraints other than <literal>NOT NULL</> will never be
! imported from the remote tables, since <productname>PostgreSQL</>
! does not support any other type of constraint on a foreign table.
Checking other types of constraints is always left to the remote server.
</para>
</sect3>
--- 349,371 ----
</para>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>import_check</literal></term>
+ <listitem>
+ <para>
+ This option controls whether column and table <literal>CHECK</>
+ constraints are included in the definitions of foreign tables imported
+ from a foreign server. The default is <literal>true</>.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
<para>
! Note that constraints other than <literal>NOT NULL</> and
! <literal>CHECK</> will never be imported from the remote tables, since
! <productname>PostgreSQL</> does not support any other type of constraint
! on a foreign table.
Checking other types of constraints is always left to the remote server.
</para>
</sect3>
*** a/src/backend/commands/foreigncmds.c
--- b/src/backend/commands/foreigncmds.c
***************
*** 1571,1599 **** ImportForeignSchema(ImportForeignSchemaStmt *stmt)
*/
foreach(lc2, raw_parsetree_list)
{
! CreateForeignTableStmt *cstmt = lfirst(lc2);
/*
! * Because we only allow CreateForeignTableStmt, we can skip parse
! * analysis, rewrite, and planning steps here.
*/
! if (!IsA(cstmt, CreateForeignTableStmt))
elog(ERROR,
"foreign-data wrapper \"%s\" returned incorrect statement type %d",
! fdw->fdwname, (int) nodeTag(cstmt));
/* Ignore commands for tables excluded by filter options */
! if (!IsImportableForeignTable(cstmt->base.relation->relname, stmt))
continue;
/* Enable reporting of current table's name on error */
! callback_arg.tablename = cstmt->base.relation->relname;
/* Ensure creation schema is the one given in IMPORT statement */
! cstmt->base.relation->schemaname = pstrdup(stmt->local_schema);
/* Execute statement */
! ProcessUtility((Node *) cstmt,
cmd,
PROCESS_UTILITY_SUBCOMMAND, NULL,
None_Receiver, NULL);
--- 1571,1608 ----
*/
foreach(lc2, raw_parsetree_list)
{
! Node *parsetree = lfirst(lc2);
! RangeVar *rv;
/*
! * Because we only allow CreateForeignTableStmt and AlterTableStmt,
! * we can skip parse analysis, rewrite, and planning steps here.
*/
! if (!IsA(parsetree, CreateForeignTableStmt) &&
! !(IsA(parsetree, AlterTableStmt) &&
! ((AlterTableStmt *) parsetree)->relkind == OBJECT_FOREIGN_TABLE))
elog(ERROR,
"foreign-data wrapper \"%s\" returned incorrect statement type %d",
! fdw->fdwname, (int) nodeTag(parsetree));
!
! /* Get RangeVar */
! if (!IsA(parsetree, CreateForeignTableStmt))
! rv = ((AlterTableStmt *) parsetree)->relation;
! else
! rv = ((CreateForeignTableStmt *) parsetree)->base.relation;
/* Ignore commands for tables excluded by filter options */
! if (!IsImportableForeignTable(rv->relname, stmt))
continue;
/* Enable reporting of current table's name on error */
! callback_arg.tablename = rv->relname;
/* Ensure creation schema is the one given in IMPORT statement */
! rv->schemaname = pstrdup(stmt->local_schema);
/* Execute statement */
! ProcessUtility(parsetree,
cmd,
PROCESS_UTILITY_SUBCOMMAND, NULL,
None_Receiver, NULL);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
