*** a/contrib/file_fdw/input/file_fdw.source --- b/contrib/file_fdw/input/file_fdw.source *************** *** 134,139 **** DELETE FROM agg_csv WHERE a = 100; --- 134,149 ---- -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; + -- constraint exclusion tests + ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0); + \t on + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + SET constraint_exclusion = 'partition'; + \t off + ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; + -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; *** a/contrib/file_fdw/output/file_fdw.source --- b/contrib/file_fdw/output/file_fdw.source *************** *** 219,224 **** SELECT * FROM agg_csv FOR UPDATE; --- 219,242 ---- 42 | 324.78 (3 rows) + -- constraint exclusion tests + ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0); + \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 + + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + Result + Output: a, b + One-Time Filter: false + + SET constraint_exclusion = 'partition'; + \t off + ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 2488,2493 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; --- 2488,2515 ---- (13 rows) -- =================================================================== + -- test constraint exclusion stuff + -- =================================================================== + ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + QUERY PLAN + ------------------------------------------------------------------------------------------ + Foreign Scan on public.ft1 + Output: c1, c2, c3, c4, c5, c6, c7, c8 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c2 < 0)) + (3 rows) + + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + QUERY PLAN + ------------------------------------------ + Result + Output: c1, c2, c3, c4, c5, c6, c7, c8 + One-Time Filter: false + (3 rows) + + SET constraint_exclusion = 'partition'; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 387,392 **** select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; --- 387,401 ---- select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; -- =================================================================== + -- test constraint exclusion stuff + -- =================================================================== + ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + SET constraint_exclusion = 'partition'; + + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); *** a/doc/src/sgml/ref/alter_foreign_table.sgml --- b/doc/src/sgml/ref/alter_foreign_table.sgml *************** *** 42,47 **** 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 *************** *** 152,157 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + + ADD table_constraint + + + This form adds a new constraint to a table using the same syntax as + . + Unlike the case when adding a constraint to a regular table, nothing happens + to the underlying storage: this action simply declares that + some new constraint holds for all rows in the foreign table. + + + + + + DROP CONSTRAINT [ IF EXISTS ] + + + This form drops the specified constraint on a table. + If IF EXISTS is specified and the constraint + does not exist, no error is thrown. In this case a notice is issued instead. + + + + DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER *************** *** 284,289 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + + table_constraint + + + New table constraint for the table. + + + + + + constraint_name + + + Name of an existing constraint to drop. + + + + CASCADE *************** *** 365,374 **** 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. --- 409,418 ---- 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 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. *** a/doc/src/sgml/ref/create_foreign_table.sgml --- b/doc/src/sgml/ref/create_foreign_table.sgml *************** *** 19,25 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] [, ... ] ] ) SERVER server_name --- 19,26 ---- CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] ! | table_constraint } [, ... ] ] ) SERVER server_name *************** *** 30,36 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 31,43 ---- [ CONSTRAINT constraint_name ] { NOT NULL | NULL | + CHECK ( expression ) | DEFAULT default_expr } + + and table_constraint is: + + [ CONSTRAINT constraint_name ] + { CHECK ( expression ) } *************** *** 138,143 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 145,171 ---- + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which each row must satisfy. + Expressions evaluating to TRUE or UNKNOWN succeed. + 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,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 215,230 ---- + + Notes + + + Constraints on foreign tables are not enforced on insert or update. + Those definitions simply declare the constraints hold for all rows + in the foreign tables. It is the user's responsibility to ensure + that those definitions match the remote side. + + Examples *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 2221,2226 **** AddRelationNewConstraints(Relation rel, --- 2221,2240 ---- if (cdef->contype != CONSTR_CHECK) continue; + /* Don't allow NO INHERIT for foreign tables */ + if (cdef->is_no_inherit && + rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CHECK constraints on foreign tables cannot be marked NO INHERIT"))); + + /* 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("NOT VALID is not supported for CHECK constraints on foreign tables"))); + if (cdef->raw_expr != NULL) { Assert(cdef->cooked_expr == NULL); *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 477,486 **** 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, --- 477,482 ---- *************** *** 3142,3148 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_INDEX; break; case AT_AddConstraint: /* ADD CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) --- 3138,3144 ---- pass = AT_PASS_ADD_INDEX; break; case AT_AddConstraint: /* ADD CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) *************** *** 3156,3162 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_CONSTR; break; case AT_DropConstraint: /* DROP CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) --- 3152,3158 ---- pass = AT_PASS_ADD_CONSTR; break; case AT_DropConstraint: /* DROP CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** *** 515,526 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_CHECK: - if (cxt->isforeign) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), - parser_errposition(cxt->pstate, - constraint->location))); cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); break; --- 515,520 ---- *************** *** 529,535 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) --- 523,529 ---- if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("primary key or unique constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) *************** *** 546,552 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); --- 540,546 ---- 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))); *************** *** 605,614 **** 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))); --- 599,612 ---- static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { ! if (cxt->isforeign && ! (constraint->contype == CONSTR_PRIMARY || ! constraint->contype == CONSTR_UNIQUE || ! constraint->contype == CONSTR_EXCLUSION || ! constraint->contype == CONSTR_FOREIGN)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("primary key, unique, exclusion, or foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *************** *** 670,676 **** LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; --- 670,682 ---- ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT, ! c3 date ! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT ! CREATE FOREIGN TABLE ft1 ( ! c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; *************** *** 682,687 **** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; --- 688,695 ---- 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) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') *************** *** 740,745 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; --- 748,755 ---- c8 | text | | (p2 'V2') | extended | | c9 | integer | | | plain | | c10 | integer | | (p1 'v1') | plain | | + Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') *************** *** 748,763 **** 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 DROP CONSTRAINT no_const; -- ERROR ! ERROR: "ft1" is not a table 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 ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; --- 758,775 ---- 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) NO INHERIT; -- ERROR ! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR ! ERROR: NOT VALID is not supported for CHECK constraints on foreign tables ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); 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; ! NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; ! ERROR: constraint "ft1_c1_check" of relation "ft1" does not exist ! ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; *************** *** 785,790 **** ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; --- 797,804 ---- c7 | integer | | (p1 'v1', p2 'v2') c8 | text | | (p2 'V2') c10 | integer | | (p1 'v1') + Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) Server: s0 FDW Options: (quote '~', "be quoted" 'value', escape '@') *** a/src/test/regress/sql/foreign_data.sql --- b/src/test/regress/sql/foreign_data.sql *************** *** 270,276 **** 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, ! c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; --- 270,281 ---- CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT, ! c3 date ! ) 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 ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; *************** *** 314,323 **** 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 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 '@'); --- 319,331 ---- 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) NO INHERIT; -- 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 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 DROP CONSTRAINT ft1_c9_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 '@');