From fc2ac1fb41c2defb8caf825781af75db158fb7a9 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 17 Dec 2014 17:00:53 -0500 Subject: [PATCH] Allow CHECK constraints to be placed on foreign tables. As with NOT NULL constraints, we consider that such constraints are merely reports of constraints that are being enforced by the remote server (or other underlying storage mechanism). Their only real use is to allow planner optimizations, for example in constraint-exclusion checks. Thus, the code changes here amount to little more than removal of the error that was formerly thrown for applying CHECK to a foreign table. (In passing, do a bit of cleanup of the ALTER FOREIGN TABLE reference page, which had accumulated some weird decisions about ordering etc.) Shigeru Hanada and Etsuro Fujita, reviewed by Kyotaro Horiguchi and Ashutosh Bapat. --- contrib/file_fdw/input/file_fdw.source | 16 ++- contrib/file_fdw/output/file_fdw.source | 32 ++++- .../postgres_fdw/expected/postgres_fdw.out | 85 +++++++++++++ contrib/postgres_fdw/sql/postgres_fdw.sql | 30 +++++ doc/src/sgml/ref/alter_foreign_table.sgml | 113 +++++++++++++----- doc/src/sgml/ref/create_foreign_table.sgml | 65 +++++++++- src/backend/catalog/heap.c | 9 +- src/backend/commands/tablecmds.c | 8 +- src/backend/parser/parse_utilcmd.c | 51 ++++++-- src/test/regress/expected/foreign_data.out | 57 +++++++-- src/test/regress/sql/foreign_data.sql | 28 ++++- 11 files changed, 432 insertions(+), 62 deletions(-) diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source index b608372825e..e975bc552e3 100644 --- a/contrib/file_fdw/input/file_fdw.source +++ b/contrib/file_fdw/input/file_fdw.source @@ -62,7 +62,7 @@ CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null ' CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR CREATE FOREIGN TABLE agg_text ( - a int2, + a int2 CHECK (a >= 0), b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); @@ -72,11 +72,13 @@ CREATE FOREIGN TABLE agg_csv ( b float4 ) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null ''); +ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0); CREATE FOREIGN TABLE agg_bad ( a int2, b float4 ) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null ''); +ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0); -- per-column options tests CREATE FOREIGN TABLE text_csv ( @@ -134,6 +136,18 @@ DELETE FROM agg_csv WHERE a = 100; -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; +-- constraint exclusion tests +\t on +EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; +\t off +SELECT * FROM agg_csv WHERE a < 0; +SET constraint_exclusion = 'on'; +\t on +EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; +\t off +SELECT * FROM agg_csv WHERE a < 0; +RESET constraint_exclusion; + -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source index bc183b88744..6808d5b2f8f 100644 --- a/contrib/file_fdw/output/file_fdw.source +++ b/contrib/file_fdw/output/file_fdw.source @@ -78,7 +78,7 @@ ERROR: COPY null representation cannot use newline or carriage return CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR ERROR: filename is required for file_fdw foreign tables CREATE FOREIGN TABLE agg_text ( - a int2, + a int2 CHECK (a >= 0), b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); @@ -88,11 +88,13 @@ CREATE FOREIGN TABLE agg_csv ( b float4 ) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null ''); +ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0); CREATE FOREIGN TABLE agg_bad ( a int2, b float4 ) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null ''); +ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0); -- per-column options tests CREATE FOREIGN TABLE text_csv ( word1 text OPTIONS (force_not_null 'true'), @@ -219,6 +221,34 @@ SELECT * FROM agg_csv FOR UPDATE; 42 | 324.78 (3 rows) +-- constraint exclusion tests +\t on +EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + Foreign Scan on public.agg_csv + Output: a, b + Filter: (agg_csv.a < 0) + Foreign File: @abs_srcdir@/data/agg.csv + +\t off +SELECT * FROM agg_csv WHERE a < 0; + a | b +---+--- +(0 rows) + +SET constraint_exclusion = 'on'; +\t on +EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + Result + Output: a, b + One-Time Filter: false + +\t off +SELECT * FROM agg_csv WHERE a < 0; + a | b +---+--- +(0 rows) + +RESET constraint_exclusion; -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index a335e56a9df..583cce738aa 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2588,6 +2588,91 @@ select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; 407 | 100 (13 rows) +-- =================================================================== +-- test check constraints +-- =================================================================== +-- Consistent check constraints provide consistent results +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0; + QUERY PLAN +------------------------------------------------------------------- + Aggregate + Output: count(*) + -> Foreign Scan on public.ft1 + Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 < 0)) +(4 rows) + +SELECT count(*) FROM ft1 WHERE c2 < 0; + count +------- + 0 +(1 row) + +SET constraint_exclusion = 'on'; +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0; + QUERY PLAN +-------------------------------- + Aggregate + Output: count(*) + -> Result + One-Time Filter: false +(4 rows) + +SELECT count(*) FROM ft1 WHERE c2 < 0; + count +------- + 0 +(1 row) + +RESET constraint_exclusion; +-- check constraint is enforced on the remote side, not locally +INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive +ERROR: new row for relation "T 1" violates check constraint "c2positive" +DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). +CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) +UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive +ERROR: new row for relation "T 1" violates check constraint "c2positive" +DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo). +CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1 +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; +-- But inconsistent check constraints provide inconsistent results +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0; + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + Output: count(*) + -> Foreign Scan on public.ft1 + Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 >= 0)) +(4 rows) + +SELECT count(*) FROM ft1 WHERE c2 >= 0; + count +------- + 821 +(1 row) + +SET constraint_exclusion = 'on'; +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0; + QUERY PLAN +-------------------------------- + Aggregate + Output: count(*) + -> Result + One-Time Filter: false +(4 rows) + +SELECT count(*) FROM ft1 WHERE c2 >= 0; + count +------- + 0 +(1 row) + +RESET constraint_exclusion; +-- local check constraint is not actually enforced +INSERT INTO ft1(c1, c2) VALUES(1111, 2); +UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index fcfb10d29ea..83e8fa7b045 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -405,6 +405,36 @@ commit; select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; +-- =================================================================== +-- test check constraints +-- =================================================================== + +-- Consistent check constraints provide consistent results +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0; +SELECT count(*) FROM ft1 WHERE c2 < 0; +SET constraint_exclusion = 'on'; +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0; +SELECT count(*) FROM ft1 WHERE c2 < 0; +RESET constraint_exclusion; +-- check constraint is enforced on the remote side, not locally +INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive +UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive; + +-- But inconsistent check constraints provide inconsistent results +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0); +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0; +SELECT count(*) FROM ft1 WHERE c2 >= 0; +SET constraint_exclusion = 'on'; +EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0; +SELECT count(*) FROM ft1 WHERE c2 >= 0; +RESET constraint_exclusion; +-- local check constraint is not actually enforced +INSERT INTO ft1(c1, c2) VALUES(1111, 2); +UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1; +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index 9d9c439315f..ff48ab88829 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -42,6 +42,8 @@ ALTER FOREIGN TABLE [ IF EXISTS ] namecolumn_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) + ADD table_constraint + DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name @@ -87,16 +89,6 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name - - IF EXISTS - - - Do not throw an error if the foreign table does not exist. A notice is - issued in this case. - - - - SET DATA TYPE @@ -153,41 +145,54 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name - DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER + ADD table_constraint - These forms configure the firing of trigger(s) belonging to the foreign - table. See the similar form of for more - details. + This form adds a new constraint to a foreign table, using the same + syntax as . + Currently only CHECK constraints are supported. + + + + Unlike the case when adding a constraint to a regular table, nothing is + done to verify the constraint is correct; rather, this action simply + declares that some new condition holds for all rows in the foreign + table. (See the discussion in .) + Note that constraints on foreign tables cannot be marked + NOT VALID since such constraints are simply declarative. - OWNER + DROP CONSTRAINT [ IF EXISTS ] - This form changes the owner of the foreign table to the - specified user. + This form drops the specified constraint on a foreign table. + If IF EXISTS is specified and the constraint + does not exist, no error is thrown. + In this case a notice is issued instead. - RENAME + DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER - The RENAME forms change the name of a foreign table - or the name of an individual column in a foreign table. + These forms configure the firing of trigger(s) belonging to the foreign + table. See the similar form of for more + details. - SET SCHEMA + OWNER - This form moves the foreign table into another schema. + This form changes the owner of the foreign table to the + specified user. @@ -207,6 +212,25 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + RENAME + + + The RENAME forms change the name of a foreign table + or the name of an individual column in a foreign table. + + + + + + SET SCHEMA + + + This form moves the foreign table into another schema. + + + + @@ -218,6 +242,12 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + If the command is written as ALTER FOREIGN TABLE IF EXISTS ... + and the foreign table does not exist, no error is thrown. A notice is + issued in this case. + + You must own the table to use ALTER FOREIGN TABLE. To change the schema of a foreign table, you must also have @@ -284,12 +314,30 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name + + table_constraint + + + New table constraint for the foreign table. + + + + + + constraint_name + + + Name of an existing constraint to drop. + + + + CASCADE Automatically drop objects that depend on the dropped column - (for example, views referencing the column). + or constraint (for example, views referencing the column). @@ -298,7 +346,7 @@ ALTER FOREIGN TABLE [ IF EXISTS ] nameRESTRICT - Refuse to drop the column if there are any dependent + Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior. @@ -365,10 +413,10 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or - DROP COLUMN, a NOT NULL constraint is - added, or a column type is changed with SET DATA TYPE. It is - the user's responsibility to ensure that the table definition matches the - remote side. + DROP COLUMN, a NOT NULL + or CHECK constraint is added, or a column type is changed + with SET DATA TYPE. It is the user's responsibility to ensure + that the table definition matches the remote side. @@ -413,4 +461,13 @@ ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2, ' extension of SQL, which disallows zero-column foreign tables. + + + See Also + + + + + + diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index 46a20eff14f..a24aa6e6d1d 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -19,7 +19,8 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ - column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] + { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] + | table_constraint } [, ... ] ] ) SERVER server_name @@ -30,7 +31,13 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name [ CONSTRAINT constraint_name ] { NOT NULL | NULL | + CHECK ( expression ) | DEFAULT default_expr } + +and table_constraint is: + +[ CONSTRAINT constraint_name ] +CHECK ( expression ) @@ -137,6 +144,28 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name + + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which each row in the foreign table is expected + to satisfy; that is, the expression should produce TRUE or UNKNOWN, + never FALSE, for all rows in the foreign table. + A check constraint specified as a column constraint should + reference that column's value only, while an expression + appearing in a table constraint can reference multiple columns. + + + + Currently, CHECK expressions cannot contain + subqueries nor refer to variables other than columns of the + current row. The system column tableoid + may be referenced, but not any other system column. + + + + DEFAULT default_expr @@ -187,6 +216,40 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name + + Notes + + + Constraints on foreign tables (such as CHECK + or NOT NULL clauses) are not enforced by the + core PostgreSQL system, and most foreign data wrappers + do not attempt to enforce them either; that is, the constraint is + simply assumed to hold true. There would be little point in such + enforcement since it would only apply to rows inserted or updated via + the foreign table, and not to rows modified by other means, such as + directly on the remote server. Instead, a constraint attached to a + foreign table should represent a constraint that is being enforced by + the remote server. + + + + Some special-purpose foreign data wrappers might be the only access + mechanism for the data they access, and in that case it might be + appropriate for the foreign data wrapper itself to perform constraint + enforcement. But you should not assume that a wrapper does that + unless its documentation says so. + + + + Although PostgreSQL does not attempt to enforce + constraints on foreign tables, it does assume that they are correct + for purposes of query optimization. If there are rows visible in the + foreign table that do not satisfy a declared constraint, queries on + the table might produce incorrect answers. It is the user's + responsibility to ensure that the constraint definition matches + reality. + + Examples diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index e523ee923a4..e360482e1de 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -1091,7 +1091,7 @@ heap_create_with_catalog(const char *relname, */ if (!OidIsValid(relid)) { - /* Use binary-upgrade override for pg_class.oid/relfilenode? */ + /* Use binary-upgrade override for pg_class.oid/relfilenode? */ if (IsBinaryUpgrade && (relkind == RELKIND_RELATION || relkind == RELKIND_SEQUENCE || relkind == RELKIND_VIEW || relkind == RELKIND_MATVIEW || @@ -2244,6 +2244,13 @@ AddRelationNewConstraints(Relation rel, expr = stringToNode(cdef->cooked_expr); } + /* Don't allow NOT VALID for foreign tables */ + if (cdef->skip_validation && + rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CHECK constraints on foreign tables cannot be marked NOT VALID"))); + /* * Check name uniqueness, or generate a name if none was given. */ diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1e737a01c9f..81c5ab27c96 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -479,10 +479,6 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg("ON COMMIT can only be used on temporary tables"))); - if (stmt->constraints != NIL && relkind == RELKIND_FOREIGN_TABLE) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("constraints are not supported on foreign tables"))); /* * Look up the namespace in which we are supposed to create the relation, @@ -3154,7 +3150,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_INDEX; break; case AT_AddConstraint: /* ADD CONSTRAINT */ - ATSimplePermissions(rel, ATT_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) @@ -3168,7 +3164,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_CONSTR; break; case AT_DropConstraint: /* DROP CONSTRAINT */ - ATSimplePermissions(rel, ATT_TABLE); + ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 7c1939f9c45..b9fbb5b6efd 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -515,21 +515,23 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_CHECK: + cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); + break; + + case CONSTR_PRIMARY: if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), + errmsg("primary key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); - cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); - break; + /* FALL THRU */ - case CONSTR_PRIMARY: case CONSTR_UNIQUE: if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), + errmsg("unique constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) @@ -546,7 +548,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), + errmsg("foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); @@ -605,18 +607,35 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { - if (cxt->isforeign) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), - parser_errposition(cxt->pstate, - constraint->location))); - switch (constraint->contype) { case CONSTR_PRIMARY: + if (cxt->isforeign) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("primary key constraints are not supported on foreign tables"), + parser_errposition(cxt->pstate, + constraint->location))); + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); + break; + case CONSTR_UNIQUE: + if (cxt->isforeign) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("unique constraints are not supported on foreign tables"), + parser_errposition(cxt->pstate, + constraint->location))); + cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); + break; + case CONSTR_EXCLUSION: + if (cxt->isforeign) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("exclusion constraints are not supported on foreign tables"), + parser_errposition(cxt->pstate, + constraint->location))); cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); break; @@ -625,6 +644,12 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) break; case CONSTR_FOREIGN: + if (cxt->isforeign) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("foreign key constraints are not supported on foreign tables"), + parser_errposition(cxt->pstate, + constraint->location))); cxt->fkconstraints = lappend(cxt->fkconstraints, constraint); break; diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index e4dedb0c3ba..9bca4402f6e 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -669,9 +669,37 @@ ERROR: syntax error at or near "WITH OIDS" LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( - c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, + c2 text OPTIONS (param2 'val2', param3 'val3'), + c3 date +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +ERROR: primary key constraints are not supported on foreign tables +LINE 2: c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, + ^ +CREATE TABLE ref_table (id integer PRIMARY KEY); +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +ERROR: foreign key constraints are not supported on foreign tables +LINE 2: c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table ... + ^ +DROP TABLE ref_table; +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c2 text OPTIONS (param2 'val2', param3 'val3'), + c3 date, + UNIQUE (c3) +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +ERROR: unique constraints are not supported on foreign tables +LINE 5: UNIQUE (c3) + ^ +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), + c3 date, + CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; @@ -682,6 +710,9 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; c1 | integer | not null | ("param 1" 'val1') | plain | | ft1.c1 c2 | text | | (param2 'val2', param3 'val3') | extended | | c3 | date | | | plain | | +Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) + "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') @@ -740,6 +771,9 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; c8 | text | | (p2 'V2') | extended | | c9 | integer | | | plain | | c10 | integer | | (p1 'v1') | plain | | +Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) + "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') @@ -748,16 +782,20 @@ CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type DROP TABLE use_ft1_column_type; -ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR -ERROR: constraints are not supported on foreign tables -LINE 1: ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c... +ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR +ERROR: primary key constraints are not supported on foreign tables +LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); ^ -ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR +ERROR: CHECK constraints on foreign tables cannot be marked NOT VALID +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); +ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR ERROR: "ft1" is not a table +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR +ERROR: constraint "no_const" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; -ERROR: "ft1" is not a table -ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; -ERROR: "ft1" is not a table +NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; @@ -785,6 +823,9 @@ ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; c7 | integer | | (p1 'v1', p2 'v2') c8 | text | | (p2 'V2') c10 | integer | | (p1 'v1') +Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) + "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Server: s0 FDW Options: (quote '~', "be quoted" 'value', escape '@') diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index de9dbc8f386..b4b999d444e 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -269,9 +269,28 @@ CREATE FOREIGN TABLE ft1 (); -- ERROR CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR CREATE FOREIGN TABLE ft1 ( - c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY, c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +CREATE TABLE ref_table (id integer PRIMARY KEY); +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id), + c2 text OPTIONS (param2 'val2', param3 'val3'), + c3 date +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +DROP TABLE ref_table; +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c2 text OPTIONS (param2 'val2', param3 'val3'), + c3 date, + UNIQUE (c3) +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR +CREATE FOREIGN TABLE ft1 ( + c1 integer OPTIONS ("param 1" 'val1') NOT NULL, + c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), + c3 date, + CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date) ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; @@ -314,10 +333,13 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR DROP TABLE use_ft1_column_type; -ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR +ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR +ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); +ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR +ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; -ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); -- 2.39.5