Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XqHAO-00011K-4l for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Nov 2014 07:56:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XqHAN-0005Lc-06 for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Nov 2014 07:56:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XqHAK-0005LK-Cf for pgsql-hackers@postgresql.org; Mon, 17 Nov 2014 07:56:05 +0000 Received: from tama500.ecl.ntt.co.jp ([129.60.39.148]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XqHAD-0006qw-PB for pgsql-hackers@postgresql.org; Mon, 17 Nov 2014 07:56:02 +0000 Received: from mfs6.rdh.ecl.ntt.co.jp (mfs6.rdh.ecl.ntt.co.jp [129.60.39.149]) by tama500.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id sAH7tp76020363; Mon, 17 Nov 2014 16:55:51 +0900 Received: from mfs6.rdh.ecl.ntt.co.jp (localhost.localdomain [127.0.0.1]) by mfs6.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id 88F5BE0087; Mon, 17 Nov 2014 16:55:51 +0900 (JST) Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248]) by mfs6.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id 7BE4CE0084; Mon, 17 Nov 2014 16:55:51 +0900 (JST) Received: from [129.60.241.75] ([129.60.241.75]) by imail3.m.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id sAH7toVN026002; Mon, 17 Nov 2014 16:55:51 +0900 Message-ID: <5469A9FA.60804@lab.ntt.co.jp> Date: Mon, 17 Nov 2014 16:55:38 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: Ashutosh Bapat CC: Kyotaro HORIGUCHI , Heikki Linnakangas , Noah Misch , Shigeru Hanada , pgsql-hackers Subject: Re: inherit support for foreign tables References: <543D0261.6020803@lab.ntt.co.jp> <54461BEB.9000707@lab.ntt.co.jp> <5449EE98.1040704@lab.ntt.co.jp> <20141107.145713.169337282.horiguchi.kyotaro@lab.ntt.co.jp> <545CB4A2.1030303@lab.ntt.co.jp> In-Reply-To: Content-Type: multipart/mixed; boundary="------------020104030708030504000706" X-TM-AS-MML: disable X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------020104030708030504000706 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit (2014/11/12 20:04), Ashutosh Bapat wrote: > I reviewed fdw-chk-3 patch. Here are my comments Thanks for the review! > Tests > ------- > 1. The tests added in file_fdw module look good. We should add tests for > CREATE TABLE with CHECK CONSTRAINT also. Agreed. I added the tests, and also changed the proposed tests a bit. > 2. For postgres_fdw we need tests to check the behaviour in case the > constraints mismatch between the remote table and its local foreign > table declaration in case of INSERT, UPDATE and SELECT. Done. > 3. In the testcases for postgres_fdw it seems that you have forgotten to > add statement after SET constraint_exclusion to 'partition' I added the statement. > 4. In test foreign_data there are changes to fix the diffs caused by > these changes like below > ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR > -ERROR: "ft1" is not a table > +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 > +NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping > ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; > -ERROR: "ft1" is not a table > +ERROR: constraint "ft1_c1_check" of relation "ft1" does not exist > Earlier when constraints were not supported for FOREIGN TABLE, these > tests made sure the same functionality. So, even though the > corresponding constraints were not created on the table (in fact it > didn't allow the creation as well). Now that the constraints are > allowed, I think the tests for "no_const" (without IF EXISTS) and > "ft1_c1_check" are duplicating the same testcase. May be we should > review this set of statement in the light of new functionality. Agreed. I removed the "DROP CONSTRAINT ft1_c1_check" test, and added a new test for ALTER CONSTRAINT. > Code and implementation > ---------------------------------- > The usage of NO INHERIT and NOT VALID with CONSTRAINT on foreign table > is blocked, but corresponding documentation entry doesn't mention so. > Since foreign tables can not be inherited NO INHERIT option isn't > applicable to foreign tables and the constraints on the foreign tables > are declarative, hence NOT VALID option is also not applicable. So, I > agree with what the code is doing, but that should be reflected in > documentation with this explanation. > Rest of the code modifies the condition checks for CHECK CONSTRAINTs on > foreign tables, and it looks good to me. Done. Other changes: * Modified one error message that I added in AddRelationNewConstraints, to match the other message there. * Revised other docs a little bit. Attached is an updated version of the patch. Thanks, Best regards, Etsuro Fujita --------------020104030708030504000706 Content-Type: text/x-diff; name="fdw-chk-4.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-chk-4.patch" *** a/contrib/file_fdw/input/file_fdw.source --- b/contrib/file_fdw/input/file_fdw.source *************** *** 62,68 **** 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, b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); --- 62,68 ---- CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR CREATE FOREIGN TABLE agg_text ( ! a int2 CHECK (a >= 0), b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); *************** *** 72,82 **** CREATE FOREIGN TABLE agg_csv ( --- 72,84 ---- 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,139 **** DELETE FROM agg_csv WHERE a = 100; --- 136,153 ---- -- 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; + SET constraint_exclusion = 'partition'; + -- 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 *************** *** 78,84 **** 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, b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); --- 78,84 ---- CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR ERROR: filename is required for file_fdw foreign tables CREATE FOREIGN TABLE agg_text ( ! a int2 CHECK (a >= 0), b float4 ) SERVER file_server OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N'); *************** *** 88,98 **** CREATE FOREIGN TABLE agg_csv ( --- 88,100 ---- 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,224 **** SELECT * FROM agg_csv FOR UPDATE; --- 221,254 ---- 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) + + SET constraint_exclusion = 'partition'; -- 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,2578 ---- (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) + + SET constraint_exclusion = 'partition'; + -- Can throw errors on remote side during update + 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) + + SET constraint_exclusion = 'partition'; + -- Can't throw errors on remote side during update + 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) -- =================================================================== 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,422 ---- 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; + SET constraint_exclusion = 'partition'; + -- Can throw errors on remote side during update + 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; + SET constraint_exclusion = 'partition'; + -- Can't throw errors on remote side during update + 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) -- =================================================================== 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 foreign 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. + + + + Note that constraints on foreign tables cannot be marked + NOT VALID since those constraints are simply declarative. + + + + + + DROP CONSTRAINT [ IF EXISTS ] + + + 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. + + + + 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. --- 414,423 ---- Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or ! 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. *** 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,176 ---- + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which each row must satisfy. + 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. + + + + Note that check constraints on foreign tables cannot be marked + NO INHERIT since those tables are not allowd to be + inherited. + + + + + DEFAULT default_expr *************** *** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 220,235 ---- + + 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 *************** *** 2222,2227 **** AddRelationNewConstraints(Relation rel, --- 2222,2241 ---- 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("CHECK constraints on foreign tables cannot be marked NOT VALID"))); + 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("unique or primary key 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("unique, primary key, 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: 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; ! 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,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 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 SET WITH OIDS; -- ERROR ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); --------------020104030708030504000706 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------020104030708030504000706--