Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XyXoX-00069o-7K for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Dec 2014 03:19:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XyXoW-0007yZ-LS for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Dec 2014 03:19:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XyXoT-0007yE-Pd for pgsql-hackers@postgresql.org; Wed, 10 Dec 2014 03:19:42 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XyXoE-0007lH-UY for pgsql-hackers@postgresql.org; Wed, 10 Dec 2014 03:19:40 +0000 Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id sBA3JHuh014268; Wed, 10 Dec 2014 12:19:17 +0900 Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 69AA55F594; Wed, 10 Dec 2014 12:19:17 +0900 (JST) Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 5871E5F593; Wed, 10 Dec 2014 12:19:17 +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 sBA3JGpU023995; Wed, 10 Dec 2014 12:19:16 +0900 Message-ID: <5487BB9D.7070605@lab.ntt.co.jp> Date: Wed, 10 Dec 2014 12:18:53 +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> <54645499.7050709@lab.ntt.co.jp> <5476FB4B.9060705@lab.ntt.co.jp> In-Reply-To: Content-Type: multipart/mixed; boundary="------------000608020900030007080606" X-TM-AS-MML: disable X-Pg-Spam-Score: -2.6 (--) 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. --------------000608020900030007080606 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi Ashutosh, Thanks for the review! (2014/11/28 18:14), Ashutosh Bapat wrote: > On Thu, Nov 27, 2014 at 3:52 PM, Etsuro Fujita > > wrote: > (2014/11/17 17:55), Ashutosh Bapat wrote: > Here are my review comments for patch fdw-inh-3.patch. > Tests > ------- > 1. It seems like you have copied from testcase inherit.sql to > postgres_fdw testcase. That's a good thing, but it makes the > test quite > long. May be we should have two tests in postgres_fdw contrib > module, > one for simple cases, and other for inheritance. What do you say? > IMO, the test is not so time-consuming, so it doesn't seem worth > splitting it into two. > I am not worried about the timing but I am worried about the length of > the file and hence ease of debugging in case we find any issues there. > We will leave that for the commiter to decide. OK > Documentation > -------------------- > 1. The change in ddl.sgml > - We will refer to the child tables as partitions, though > they > - are in every way normal PostgreSQL tables. > + We will refer to the child tables as partitions, though > we assume > + that they are normal PostgreSQL tables. > > adds phrase "we assume that", which confuses the intention > behind the > sentence. The original text is intended to highlight the equivalence > between "partition" and "normal table", where as the addition > esp. the > word "assume" weakens that equivalence. Instead now we have to > highlight > the equivalence between "partition" and "normal or foreign > table". The > wording similar to "though they are either normal or foreign tables" > should be used there. > You are right, but I feel that there is something out of place in > saying that there (5.10.2. Implementing Partitioning) because the > procedure there has been written based on normal tables only. Put > another way, if we say that, I think we'd need to add more docs, > describing the syntax and/or the corresponding examples for > foreign-table cases. But I'd like to leave that for another patch. > So, how about the wording "we assume *here* that", instead of "we > assume that", as I added the following notice in the previous > section (5.10.1. Overview)? > > @@ -2650,7 +2669,10 @@ VALUES ('Albany', NULL, NULL, 'NY'); > table of a single parent table. The parent table itself is > normally > empty; it exists just to represent the entire data set. You > should be > familiar with inheritance (see ) > before > - attempting to set up partitioning. > + attempting to set up partitioning. (The setup and management of > + partitioned tables illustrated in this section assume that each > + partition is a normal table. However, you can do that in a > similar way > + for cases where some or all partitions are foreign tables.) > This looks ok, though, I would like to see final version of the > document. But I think, we will leave that for committer to handle. OK > 2. The wording "some kind of optimization" gives vague picture. > May be > it can be worded as "Since the constraints are assumed to be > true, they > are used in constraint-based query optimization like constraint > exclusion for partitioned tables.". > + Those constraints are used in some kind of query > optimization such > + as constraint exclusion for partitioned tables (see > + ). > Will follow your revision. Done. > Code > ------- > 1. In the following change > +/* > * acquire_inherited_sample_rows -- acquire sample rows from > inheritance tree > * > * This has the same API as acquire_sample_rows, except that > rows are > * collected from all inheritance children as well as the > specified table. > - * We fail and return zero if there are no inheritance children. > + * We fail and return zero if there are no inheritance children or > there are > + * inheritance children that foreign tables. > > The addition should be "there are inheritance children that *are all > *foreign tables. Note the addition "are all". > Sorry, I incorrectly wrote the comment. What I tried to write is > "We fail and return zero if there are no inheritance children or if > we are not in VAC_MODE_SINGLE case and inheritance tree contains at > least one foreign table.". > You might want to use "English" description of VAC_MODE_SINGLE instead > of that macro in the comment, so that reader doesn't have to look up > VAC_MODE_SINGLE. But I think, we will leave this for the committer. I corrected the comments and translated the macro into the English description. > 2. The function has_foreign() be better named > has_foreign_child()? This > How about "has_foreign_table"? > has_foreign_child() would be better, since these are "children" in the > inheritance hierarchy and not mere "table"s. Done. But I renamed it to has_foreign_children() because it sounds more natural at least to me. > function loops through all the tableoids passed even after it > has found > a foreign table. Why can't we return true immediately after > finding the > first foreign table, unless the side effects of heap_open() on > all the > table are required. But I don't see that to be the case, since these > tables are locked already through a previous call to > heap_open(). In the > Good catch! Will fix. > same function instead of argument name parentrelId may be we > should use > name parent_oid, so that we use same notation for parent and > child table > OIDs. > Will fix. Done. > 3. Regarding enum VacuumMode - it's being used only in case of > acquire_inherited_sample_rows(__) and that too, to check only a > single > value of the three defined there. May be we should just infer > that value > inside acquire_inherited_sample_rows(__) or pass a boolean true > or false > from do_analyse_rel() based on the VacuumStmt. I do not see need > for a > separate three value enum of which only one value is used and > also to > pass it down from vacuum() by changing signatures of the minion > functions. > I introduced that for possible future use. See the discussion in [1]. > Will leave it for the commiter to decide. I noticed that the signatures need not to be modified, as you said. Thanks for pointing that out! So, I revised the patch not to change the signatures, though I left the enum, renaming it to AnalyzeMode. Let's have the committer's review. > 4. In postgresGetForeignPlan(), the code added by this patch is > required > to handle the case when the row mark is placed on a parent table and > hence is required to be applied on the child table. We need a > comment > explaining this. Otherwise, the three step process to get the > row mark > information isn't clear for a reader. > Will add the comment. Done. > 5. In expand_inherited_rtentry() why do you need a separate variable > hasForeign? Instead of using that variable, you can actually > set/reset > rte->hasForeign since existence of even a single foreign child would > mark that member as true. - After typing this, I got the answer > when I > looked at the function code. Every child's RTE is initially a > copy of > parent's RTE and hence hasForeign status would be inherited by every > child after the first foreign child. I think, this reasoning > should be > added as comment before assignment to rte->hasForeign at the end > of the > function. > As you mentioned, I think we could set rte->hasForeign directly > during the scan for the inheritance set, without the separate > variable, hasForeign. But ISTM that it'd improve code readability > to set rte->hasForeign using the separate variable at the end of the > function because rte->hasForeign has its meaning only when rte->inh > is true and because we know whether rte->inh is true, at the end of > the function. > Fine. Please use "hasForeignChild" instead of just "hasForeign" without > a clue as to what is "foreign" here. Done. But I renamed it to "hasForeignChildren". > 6. The tests in foreign_data.sql are pretty extensive. Thanks > for that. > I think, we should also check the effect of each of the following > command using \d on appropriate tables. > +CREATE FOREIGN TABLE ft2 () INHERITS (pt1) > + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" > 'value'); > +ALTER FOREIGN TABLE ft2 NO INHERIT pt1; > +DROP FOREIGN TABLE ft2; > +CREATE FOREIGN TABLE ft2 ( > + c1 integer NOT NULL, > + c2 text, > + c3 date > +) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" > 'value'); > +ALTER FOREIGN TABLE ft2 INHERIT pt1; > Will fix. Done. > Apart from the above, I noticed that the patch doesn't consider to > call ExplainForeignModify during EXPLAIN for an inherited > UPDATE/DELETE, as shown below (note that there are no UPDATE remote > queries displayed): Since there seems to be no objections, I updated the patch as proposed upthread. Here is an example. postgres=# explain (format text, verbose) update parent as p set a = a * 2 returning *; QUERY PLAN -------------------------------------------------------------------------------- Update on public.parent p (cost=0.00..202.33 rows=11 width=10) Output: p.a For public.ft1 p_1 Remote SQL: UPDATE public.mytable_1 SET a = $2 WHERE ctid = $1 RETURNING a For public.ft2 p_2 Remote SQL: UPDATE public.mytable_2 SET a = $2 WHERE ctid = $1 RETURNING a -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=10) Output: (p.a * 2), p.ctid -> Foreign Scan on public.ft1 p_1 (cost=100.00..101.16 rows=5 width=10) Output: (p_1.a * 2), p_1.ctid Remote SQL: SELECT a, ctid FROM public.mytable_1 FOR UPDATE -> Foreign Scan on public.ft2 p_2 (cost=100.00..101.16 rows=5 width=10) Output: (p_2.a * 2), p_2.ctid Remote SQL: SELECT a, ctid FROM public.mytable_2 FOR UPDATE (14 rows) Other changes: * revised regression tests for contrib/file_fdw to refer to tableoid. * revised docs a bit further. Attached are updated patches. Patch fdw-inh-5.patch has been created on top of patch fdw-chk-5.patch. Patch fdw-chk-5.patch is basically the same as the previous one fdw-chk-4.patch, but I slightly modified that one. The changes are the following. * added to foreign_data.sql more tests for your comments. * revised docs on ALTER FOREIGN TABLE a bit further. Thanks, Best regards, Etsuro Fujita --------------000608020900030007080606 Content-Type: text/x-patch; name="fdw-chk-5.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-chk-5.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 *************** *** 2556,2561 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; --- 2556,2646 ---- (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 *************** *** 401,406 **** select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; --- 401,436 ---- 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 *************** *** 153,158 **** 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 *************** *** 285,295 **** ALTER FOREIGN TABLE [ IF EXISTS ] name CASCADE Automatically drop objects that depend on the dropped column ! (for example, views referencing the column). --- 316,344 ---- + 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 ! or constraint (for example, views referencing the column). *************** *** 298,304 **** ALTER FOREIGN TABLE [ IF EXISTS ] nameRESTRICT ! Refuse to drop the column if there are any dependent objects. This is the default behavior. --- 347,353 ---- RESTRICT ! Refuse to drop the column or constraint if there are any dependent objects. This is the default behavior. *************** *** 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 in the foreign table 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,236 ---- + + Notes + + + Constraints on foreign tables are not enforced on insert or update. + The definition of a constraint on a foreign table simply declares + the constraint holds for all rows in the foreign table. It is + the user's responsibility to ensure that the constraint definition + matches 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 *************** *** 479,488 **** 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, --- 479,484 ---- *************** *** 3154,3160 **** 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) --- 3150,3156 ---- 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) *************** *** 3168,3174 **** 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) --- 3164,3170 ---- 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 *************** *** 669,677 **** ERROR: syntax error at or near "WITH OIDS" --- 669,711 ---- LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( + 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: unique or 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, primary key, exclusion, or foreign key 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 <> '') 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, + 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,687 **** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; --- 716,724 ---- 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,745 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; --- 777,785 ---- 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,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; --- 788,809 ---- 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 PRIMARY KEY (c7); -- ERROR ! ERROR: unique, primary key, exclusion, or foreign key constraints are not supported on foreign tables ! LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); ^ ! 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; --- 831,839 ---- 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 '@') *** a/src/test/regress/sql/foreign_data.sql --- b/src/test/regress/sql/foreign_data.sql *************** *** 269,277 **** CREATE FOREIGN TABLE ft1 (); -- ERROR --- 269,301 ---- 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') 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 <> '') 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, + 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,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 '@'); --- 338,351 ---- 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 PRIMARY KEY (c7); -- ERROR ! 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 '@'); --------------000608020900030007080606 Content-Type: text/x-patch; name="fdw-inh-5.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-inh-5.patch" *** a/contrib/file_fdw/input/file_fdw.source --- b/contrib/file_fdw/input/file_fdw.source *************** *** 148,153 **** EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; --- 148,167 ---- SELECT * FROM agg_csv WHERE a < 0; SET constraint_exclusion = 'partition'; + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_csv INHERIT agg; + SELECT tableoid::regclass, * FROM agg; + SELECT tableoid::regclass, * FROM agg_csv; + SELECT tableoid::regclass, * FROM ONLY agg; + -- updates aren't supported + UPDATE agg SET a = 1; + DELETE FROM agg WHERE a = 100; + -- but this should be ignored + SELECT tableoid::regclass, * FROM agg FOR UPDATE; + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + DROP TABLE agg; + -- 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 *************** *** 249,254 **** SELECT * FROM agg_csv WHERE a < 0; --- 249,294 ---- (0 rows) SET constraint_exclusion = 'partition'; + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_csv INHERIT agg; + SELECT tableoid::regclass, * FROM agg; + tableoid | a | b + ----------+-----+--------- + agg_csv | 100 | 99.097 + agg_csv | 0 | 0.09561 + agg_csv | 42 | 324.78 + (3 rows) + + SELECT tableoid::regclass, * FROM agg_csv; + tableoid | a | b + ----------+-----+--------- + agg_csv | 100 | 99.097 + agg_csv | 0 | 0.09561 + agg_csv | 42 | 324.78 + (3 rows) + + SELECT tableoid::regclass, * FROM ONLY agg; + tableoid | a | b + ----------+---+--- + (0 rows) + + -- updates aren't supported + UPDATE agg SET a = 1; + ERROR: cannot update foreign table "agg_csv" + DELETE FROM agg WHERE a = 100; + ERROR: cannot delete from foreign table "agg_csv" + -- but this should be ignored + SELECT tableoid::regclass, * FROM agg FOR UPDATE; + tableoid | a | b + ----------+-----+--------- + agg_csv | 100 | 99.097 + agg_csv | 0 | 0.09561 + agg_csv | 42 | 324.78 + (3 rows) + + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + DROP TABLE agg; -- 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 *************** *** 2994,2999 **** NOTICE: NEW: (13,"test triggered !") --- 2994,3511 ---- (1 row) -- =================================================================== + -- test inheritance features + -- =================================================================== + CREATE TABLE a (aa TEXT); + CREATE TABLE loct (aa TEXT, bb TEXT); + CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) + SERVER loopback OPTIONS (table_name 'loct'); + INSERT INTO a(aa) VALUES('aaa'); + INSERT INTO a(aa) VALUES('aaaa'); + INSERT INTO a(aa) VALUES('aaaaa'); + INSERT INTO a(aa) VALUES('aaaaaa'); + INSERT INTO a(aa) VALUES('aaaaaaa'); + INSERT INTO a(aa) VALUES('aaaaaaaa'); + INSERT INTO b(aa) VALUES('bbb'); + INSERT INTO b(aa) VALUES('bbbb'); + INSERT INTO b(aa) VALUES('bbbbb'); + INSERT INTO b(aa) VALUES('bbbbbb'); + INSERT INTO b(aa) VALUES('bbbbbbb'); + INSERT INTO b(aa) VALUES('bbbbbbbb'); + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+---------- + a | aaa + a | aaaa + a | aaaaa + a | aaaaaa + a | aaaaaaa + a | aaaaaaaa + b | bbb + b | bbbb + b | bbbbb + b | bbbbbb + b | bbbbbbb + b | bbbbbbbb + (12 rows) + + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + relname | aa | bb + ---------+----------+---- + b | bbb | + b | bbbb | + b | bbbbb | + b | bbbbbb | + b | bbbbbbb | + b | bbbbbbbb | + (6 rows) + + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+---------- + a | aaa + a | aaaa + a | aaaaa + a | aaaaaa + a | aaaaaaa + a | aaaaaaaa + (6 rows) + + UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+---------- + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + b | bbb + b | bbbb + b | bbbbb + b | bbbbbb + b | bbbbbbb + b | bbbbbbbb + (12 rows) + + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + relname | aa | bb + ---------+----------+---- + b | bbb | + b | bbbb | + b | bbbbb | + b | bbbbbb | + b | bbbbbbb | + b | bbbbbbbb | + (6 rows) + + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+-------- + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + (6 rows) + + UPDATE b SET aa='new'; + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+-------- + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + b | new + b | new + b | new + b | new + b | new + b | new + (12 rows) + + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + relname | aa | bb + ---------+-----+---- + b | new | + b | new | + b | new | + b | new | + b | new | + b | new | + (6 rows) + + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+-------- + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + a | zzzzzz + (6 rows) + + UPDATE a SET aa='new'; + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+----- + a | new + a | new + a | new + a | new + a | new + a | new + b | new + b | new + b | new + b | new + b | new + b | new + (12 rows) + + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + relname | aa | bb + ---------+-----+---- + b | new | + b | new | + b | new | + b | new | + b | new | + b | new | + (6 rows) + + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+----- + a | new + a | new + a | new + a | new + a | new + a | new + (6 rows) + + DELETE FROM a; + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+---- + (0 rows) + + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + relname | aa | bb + ---------+----+---- + (0 rows) + + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + relname | aa + ---------+---- + (0 rows) + + DROP TABLE a CASCADE; + NOTICE: drop cascades to foreign table b + DROP TABLE loct; + -- Check SELECT FOR UPDATE/SHARE with an inherited source table + create table loct1 (f1 int, f2 int, f3 int); + create table loct2 (f1 int, f2 int, f3 int); + create table foo (f1 int, f2 int); + create foreign table foo2 (f3 int) inherits (foo) + server loopback options (table_name 'loct1'); + create table bar (f1 int, f2 int); + create foreign table bar2 (f3 int) inherits (bar) + server loopback options (table_name 'loct2'); + insert into foo values(1,1); + insert into foo values(3,3); + insert into foo2 values(2,2,2); + insert into foo2 values(3,3,3); + insert into bar values(1,1); + insert into bar values(2,2); + insert into bar values(3,3); + insert into bar values(4,4); + insert into bar2 values(1,1,1); + insert into bar2 values(2,2,2); + insert into bar2 values(3,3,3); + insert into bar2 values(4,4,4); + explain (verbose, costs off) + select * from bar where f1 in (select f1 from foo) for update; + QUERY PLAN + ---------------------------------------------------------------------------------------------- + LockRows + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* + -> Hash Join + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* + Hash Cond: (bar.f1 = foo.f1) + -> Append + -> Seq Scan on public.bar + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.* + -> Foreign Scan on public.bar2 + Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + -> Hash + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> HashAggregate + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + Group Key: foo.f1 + -> Append + -> Seq Scan on public.foo + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> Foreign Scan on public.foo2 + Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 + (22 rows) + + select * from bar where f1 in (select f1 from foo) for update; + f1 | f2 + ----+---- + 1 | 1 + 2 | 2 + 3 | 3 + 1 | 1 + 2 | 2 + 3 | 3 + (6 rows) + + explain (verbose, costs off) + select * from bar where f1 in (select f1 from foo) for share; + QUERY PLAN + ---------------------------------------------------------------------------------------------- + LockRows + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* + -> Hash Join + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.* + Hash Cond: (bar.f1 = foo.f1) + -> Append + -> Seq Scan on public.bar + Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.* + -> Foreign Scan on public.bar2 + Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.* + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE + -> Hash + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> HashAggregate + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + Group Key: foo.f1 + -> Append + -> Seq Scan on public.foo + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> Foreign Scan on public.foo2 + Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 + (22 rows) + + select * from bar where f1 in (select f1 from foo) for share; + f1 | f2 + ----+---- + 1 | 1 + 2 | 2 + 3 | 3 + 1 | 1 + 2 | 2 + 3 | 3 + (6 rows) + + -- Check UPDATE with inherited target and an inherited source table + explain (verbose, costs off) + update bar set f2 = f2 + 100 where f1 in (select f1 from foo); + QUERY PLAN + --------------------------------------------------------------------------------------------- + Update on public.bar + For public.bar2 + Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 + -> Hash Join + Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.* + Hash Cond: (bar.f1 = foo.f1) + -> Seq Scan on public.bar + Output: bar.f1, bar.f2, bar.ctid + -> Hash + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> HashAggregate + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + Group Key: foo.f1 + -> Append + -> Seq Scan on public.foo + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> Foreign Scan on public.foo2 + Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 + -> Hash Join + Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.* + Hash Cond: (bar2.f1 = foo.f1) + -> Foreign Scan on public.bar2 + Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + -> Hash + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> HashAggregate + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + Group Key: foo.f1 + -> Append + -> Seq Scan on public.foo + Output: foo.ctid, foo.tableoid, foo.*, foo.f1 + -> Foreign Scan on public.foo2 + Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1 + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1 + (36 rows) + + update bar set f2 = f2 + 100 where f1 in (select f1 from foo); + select tableoid::regclass::text as relname, bar.* from bar order by 1,2; + relname | f1 | f2 + ---------+----+----- + bar | 1 | 101 + bar | 2 | 102 + bar | 3 | 103 + bar | 4 | 4 + bar2 | 1 | 101 + bar2 | 2 | 102 + bar2 | 3 | 103 + bar2 | 4 | 4 + (8 rows) + + -- Check UPDATE with inherited target and an appendrel subquery + explain (verbose, costs off) + update bar set f2 = f2 + 100 + from + ( select f1 from foo union all select f1+3 from foo ) ss + where bar.f1 = ss.f1; + QUERY PLAN + -------------------------------------------------------------------------------------- + Update on public.bar + For public.bar2 + Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 + -> Hash Join + Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1)) + Hash Cond: (foo.f1 = bar.f1) + -> Append + -> Seq Scan on public.foo + Output: ROW(foo.f1), foo.f1 + -> Foreign Scan on public.foo2 + Output: ROW(foo2.f1), foo2.f1 + Remote SQL: SELECT f1 FROM public.loct1 + -> Seq Scan on public.foo foo_1 + Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3) + -> Foreign Scan on public.foo2 foo2_1 + Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3) + Remote SQL: SELECT f1 FROM public.loct1 + -> Hash + Output: bar.f1, bar.f2, bar.ctid + -> Seq Scan on public.bar + Output: bar.f1, bar.f2, bar.ctid + -> Merge Join + Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1)) + Merge Cond: (bar2.f1 = foo.f1) + -> Sort + Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid + Sort Key: bar2.f1 + -> Foreign Scan on public.bar2 + Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid + Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE + -> Sort + Output: (ROW(foo.f1)), foo.f1 + Sort Key: foo.f1 + -> Append + -> Seq Scan on public.foo + Output: ROW(foo.f1), foo.f1 + -> Foreign Scan on public.foo2 + Output: ROW(foo2.f1), foo2.f1 + Remote SQL: SELECT f1 FROM public.loct1 + -> Seq Scan on public.foo foo_1 + Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3) + -> Foreign Scan on public.foo2 foo2_1 + Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3) + Remote SQL: SELECT f1 FROM public.loct1 + (44 rows) + + update bar set f2 = f2 + 100 + from + ( select f1 from foo union all select f1+3 from foo ) ss + where bar.f1 = ss.f1; + select tableoid::regclass::text as relname, bar.* from bar order by 1,2; + relname | f1 | f2 + ---------+----+----- + bar | 1 | 201 + bar | 2 | 202 + bar | 3 | 203 + bar | 4 | 104 + bar2 | 1 | 201 + bar2 | 2 | 202 + bar2 | 3 | 203 + bar2 | 4 | 104 + (8 rows) + + drop table foo cascade; + NOTICE: drop cascades to foreign table foo2 + drop table bar cascade; + NOTICE: drop cascades to foreign table bar2 + drop table loct1; + drop table loct2; + -- Test that WHERE CURRENT OF is not supported + create table ltbl (a int, b text); + create table ptbl (a int, b text); + create table locc () inherits (ptbl); + insert into locc values(1, 'foo'); + create foreign table remc () inherits (ptbl) + server loopback options (table_name 'ltbl'); + insert into remc values(2, 'bar'); + select * from ptbl; + a | b + ---+----- + 1 | foo + 2 | bar + (2 rows) + + begin; + declare c cursor for select 1 from ptbl where b = 'foo'; + fetch from c; + ?column? + ---------- + 1 + (1 row) + + update ptbl set b = null where current of c; + ERROR: WHERE CURRENT OF is not supported for this table type + rollback; + select * from ptbl; + a | b + ---+----- + 1 | foo + 2 | bar + (2 rows) + + begin; + declare c cursor for select 1 from ptbl where b = 'bar'; + fetch from c; + ?column? + ---------- + 1 + (1 row) + + update ptbl set b = null where current of c; + ERROR: WHERE CURRENT OF is not supported for this table type + rollback; + select * from ptbl; + a | b + ---+----- + 1 | foo + 2 | bar + (2 rows) + + -- Test TRUNCATE + truncate ptbl; + select * from ptbl; + a | b + ---+----- + 2 | bar + (1 row) + + select * from only ptbl; + a | b + ---+--- + (0 rows) + + select * from locc; + a | b + ---+--- + (0 rows) + + select * from remc; + a | b + ---+----- + 2 | bar + (1 row) + + truncate remc; -- ERROR + ERROR: "remc" is not a table + drop table ptbl cascade; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to table locc + drop cascades to foreign table remc + drop table ltbl; + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== CREATE SCHEMA import_source; *** a/contrib/postgres_fdw/postgres_fdw.c --- b/contrib/postgres_fdw/postgres_fdw.c *************** *** 824,834 **** postgresGetForeignPlan(PlannerInfo *root, { RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid); if (rc) { /* ! * Relation is specified as a FOR UPDATE/SHARE target, so handle ! * that. * * For now, just ignore any [NO] KEY specification, since (a) it's * not clear what that means for a remote table that we don't have --- 824,847 ---- { RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid); + /* + * It's possible that relation is contained in an inheritance set and + * that parent relation is selected FOR UPDATE/SHARE. If so, get the + * RowMarkClause for parent relation. + */ + if (rc == NULL) + { + PlanRowMark *prm = get_plan_rowmark(root->rowMarks, baserel->relid); + + if (prm && prm->rti != prm->prti) + rc = get_parse_rowmark(root->parse, prm->prti); + } + if (rc) { /* ! * Relation or parent relation is specified as a FOR UPDATE/SHARE ! * target, so handle that. * * For now, just ignore any [NO] KEY specification, since (a) it's * not clear what that means for a remote table that we don't have *************** *** 1680,1690 **** postgresExplainForeignModify(ModifyTableState *mtstate, --- 1693,1717 ---- int subplan_index, ExplainState *es) { + ModifyTable *plan = (ModifyTable *) mtstate->ps.plan; + if (es->verbose) { char *sql = strVal(list_nth(fdw_private, FdwModifyPrivateUpdateSql)); + /* + * If we are in an inherited UPDATE/DELETE case, show the SQL statement + * in a group of the target relation information. + */ + if (subplan_index > 0) + { + ExplainOpenForeignModifyGroup(plan, subplan_index, es); + ExplainPropertyText("Remote SQL", sql, es); + ExplainCloseForeignModifyGroup(plan, subplan_index, es); + return; + } + ExplainPropertyText("Remote SQL", sql, es); } } *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 661,666 **** UPDATE rem1 SET f2 = 'testo'; --- 661,817 ---- INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid; -- =================================================================== + -- test inheritance features + -- =================================================================== + + CREATE TABLE a (aa TEXT); + CREATE TABLE loct (aa TEXT, bb TEXT); + CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a) + SERVER loopback OPTIONS (table_name 'loct'); + + INSERT INTO a(aa) VALUES('aaa'); + INSERT INTO a(aa) VALUES('aaaa'); + INSERT INTO a(aa) VALUES('aaaaa'); + INSERT INTO a(aa) VALUES('aaaaaa'); + INSERT INTO a(aa) VALUES('aaaaaaa'); + INSERT INTO a(aa) VALUES('aaaaaaaa'); + + INSERT INTO b(aa) VALUES('bbb'); + INSERT INTO b(aa) VALUES('bbbb'); + INSERT INTO b(aa) VALUES('bbbbb'); + INSERT INTO b(aa) VALUES('bbbbbb'); + INSERT INTO b(aa) VALUES('bbbbbbb'); + INSERT INTO b(aa) VALUES('bbbbbbbb'); + + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + + UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; + + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + + UPDATE b SET aa='new'; + + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + + UPDATE a SET aa='new'; + + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + + DELETE FROM a; + + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; + + DROP TABLE a CASCADE; + DROP TABLE loct; + + -- Check SELECT FOR UPDATE/SHARE with an inherited source table + create table loct1 (f1 int, f2 int, f3 int); + create table loct2 (f1 int, f2 int, f3 int); + + create table foo (f1 int, f2 int); + create foreign table foo2 (f3 int) inherits (foo) + server loopback options (table_name 'loct1'); + create table bar (f1 int, f2 int); + create foreign table bar2 (f3 int) inherits (bar) + server loopback options (table_name 'loct2'); + + insert into foo values(1,1); + insert into foo values(3,3); + insert into foo2 values(2,2,2); + insert into foo2 values(3,3,3); + insert into bar values(1,1); + insert into bar values(2,2); + insert into bar values(3,3); + insert into bar values(4,4); + insert into bar2 values(1,1,1); + insert into bar2 values(2,2,2); + insert into bar2 values(3,3,3); + insert into bar2 values(4,4,4); + + explain (verbose, costs off) + select * from bar where f1 in (select f1 from foo) for update; + select * from bar where f1 in (select f1 from foo) for update; + + explain (verbose, costs off) + select * from bar where f1 in (select f1 from foo) for share; + select * from bar where f1 in (select f1 from foo) for share; + + -- Check UPDATE with inherited target and an inherited source table + explain (verbose, costs off) + update bar set f2 = f2 + 100 where f1 in (select f1 from foo); + update bar set f2 = f2 + 100 where f1 in (select f1 from foo); + + select tableoid::regclass::text as relname, bar.* from bar order by 1,2; + + -- Check UPDATE with inherited target and an appendrel subquery + explain (verbose, costs off) + update bar set f2 = f2 + 100 + from + ( select f1 from foo union all select f1+3 from foo ) ss + where bar.f1 = ss.f1; + update bar set f2 = f2 + 100 + from + ( select f1 from foo union all select f1+3 from foo ) ss + where bar.f1 = ss.f1; + + select tableoid::regclass::text as relname, bar.* from bar order by 1,2; + + drop table foo cascade; + drop table bar cascade; + drop table loct1; + drop table loct2; + + -- Test that WHERE CURRENT OF is not supported + create table ltbl (a int, b text); + + create table ptbl (a int, b text); + create table locc () inherits (ptbl); + insert into locc values(1, 'foo'); + + create foreign table remc () inherits (ptbl) + server loopback options (table_name 'ltbl'); + insert into remc values(2, 'bar'); + + select * from ptbl; + + begin; + declare c cursor for select 1 from ptbl where b = 'foo'; + fetch from c; + update ptbl set b = null where current of c; + rollback; + + select * from ptbl; + + begin; + declare c cursor for select 1 from ptbl where b = 'bar'; + fetch from c; + update ptbl set b = null where current of c; + rollback; + + select * from ptbl; + + -- Test TRUNCATE + truncate ptbl; + select * from ptbl; + select * from only ptbl; + select * from locc; + select * from remc; + truncate remc; -- ERROR + + drop table ptbl cascade; + drop table ltbl; + + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** *** 2503,2508 **** VALUES ('Albany', NULL, NULL, 'NY'); --- 2503,2526 ---- further privileges to be granted. + + Note that a foreign table can also inherit from more than one parent + table (see for an overview of + foreign tables). + Like normal tables, table inheritance is typically established when + the foreign table is created, using the INHERITS clause + of the statement. + Alternatively, a foreign table which is already defined + in a compatible way can have a new parent relationship added, using + the INHERIT variant of + . + Similarly an inheritance link can be removed from a child using the + NO INHERIT variant of ALTER FOREIGN TABLE. + CREATE FOREIGN TABLE and ALTER FOREIGN TABLE + follow the same rules for duplicate column merging and rejection as + CREATE TABLE and ALTER TABLE, respectively. + + Caveats *************** *** 2650,2656 **** VALUES ('Albany', NULL, NULL, 'NY'); table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see ) before ! attempting to set up partitioning. --- 2668,2677 ---- table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see ) before ! attempting to set up partitioning. (The setup and management of ! partitioned tables illustrated in this section assume that each ! partition is a normal table. However, you can do that in a similar way ! for cases where some or all partitions are foreign tables.) *************** *** 2713,2720 **** VALUES ('Albany', NULL, NULL, 'NY'); ! We will refer to the child tables as partitions, though they ! are in every way normal PostgreSQL tables. --- 2734,2741 ---- ! We will refer to the child tables as partitions, though we assume ! here that they are normal PostgreSQL tables. *** a/doc/src/sgml/ref/alter_foreign_table.sgml --- b/doc/src/sgml/ref/alter_foreign_table.sgml *************** *** 48,53 **** ALTER FOREIGN TABLE [ IF EXISTS ] nametrigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name + INHERIT parent_table + NO INHERIT parent_table OWNER TO new_owner OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) *************** *** 195,200 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + INHERIT parent_table + + + This form adds the target foreign table as a new child of the specified + parent table. The parent table must be an ordinary table. + + + + + + NO INHERIT parent_table + + + This form removes the target foreign table from the list of children of + the specified parent table. + + + + + OWNER *************** *** 385,390 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + parent_table + + + A parent table to associate or de-associate with this foreign table. + The parent table must be an ordinary table. + + + + + new_owner *** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** *** 1010,1015 **** ALTER TABLE ALL IN TABLESPACE name --- 1010,1028 ---- + A recursive SET STORAGE operation will make + the storage mode unchanged for any column of descendant tables + that are foreign. + + + + A recursive SET WITH OIDS operation will be + rejected if any of descendant tables is foreign, since it is + not permitted to add an oid system column to + foreign tables. + + + The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. *************** *** 1018,1023 **** ALTER TABLE ALL IN TABLESPACE name --- 1031,1044 ---- + When adding a CHECK constraint with the NOT VALID + option recursively, the inherited constraint on a descendant + table that is foreign will be marked valid without checking consistency + with the foreign server. It is the user's resposibility to ensure that + the constraint definition matches the remote side. + + + Changing any part of a system catalog table is not permitted. *** a/doc/src/sgml/ref/analyze.sgml --- b/doc/src/sgml/ref/analyze.sgml *************** *** 200,205 **** ANALYZE [ VERBOSE ] [ table_name [ --- 200,212 ---- + The inheritance statistics for a parent table that contains one or more + children that are foreign tables are collected only when explicitly + selected. If any of the foreign table's wrapper does not support + ANALYZE, the command prints a warning and does nothing. + + + If the table being analyzed is completely empty, ANALYZE will not record new statistics for that table. Any existing statistics will be retained. *** a/doc/src/sgml/ref/create_foreign_table.sgml --- b/doc/src/sgml/ref/create_foreign_table.sgml *************** *** 23,28 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 23,29 ---- | table_constraint } [, ... ] ] ) + [ INHERITS ( parent_table [, ... ] ) ] SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ] *************** *** 121,126 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 122,146 ---- + INHERITS ( parent_table [, ... ] ) + + + The optional INHERITS clause specifies a list of + tables from which the new foreign table automatically inherits + all columns. See the similar form of + for more details. + + + + Note that unlike CREATE TABLE, column + STORAGE settings are not copied from parent tables, + resulting in the copied columns in the new foreign table having + type-specific default settings. + + + + + NOT NULL *************** *** 192,197 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 212,229 ---- + parent_table + + + The name of an existing table from which the new foreign table + automatically inherits all columns. The specified parent table + must be an ordinary table. See for + more information. + + + + + server_name *************** *** 228,234 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name The definition of a constraint on a foreign table simply declares the constraint holds for all rows in the foreign table. It is the user's responsibility to ensure that the constraint definition ! matches the remote side. --- 260,275 ---- The definition of a constraint on a foreign table simply declares the constraint holds for all rows in the foreign table. It is the user's responsibility to ensure that the constraint definition ! matches the remote side. Since the constraints are assumed to be ! true, they are used in constraint-based query optimization like ! constraint exclusion for partitioned tables (see ! ). ! ! ! ! Since it is not permitted to add an oid system column to ! foreign tables, the command will be rejected if any of parent tables ! has an oid system column. *** a/doc/src/sgml/ref/truncate.sgml --- b/doc/src/sgml/ref/truncate.sgml *************** *** 179,184 **** TRUNCATE [ TABLE ] [ ONLY ] name [ --- 179,189 ---- This is similar to the usual behavior of currval() after a failed transaction. + + + A recursive TRUNCATE ignores any descendant tables that are + foreign, and applies the operation to descendant tables that are normal. + *** a/src/backend/commands/analyze.c --- b/src/backend/commands/analyze.c *************** *** 56,61 **** --- 56,69 ---- #include "utils/tqual.h" + /* Possible modes for ANALYZE */ + typedef enum + { + ANL_MODE_ALL, /* Analyze all relations */ + ANL_MODE_SINGLE, /* Analyze a specific relation */ + ANL_MODE_AUTOVACUUM /* Autovacuum worker */ + } AnalyzeMode; + /* Data structure for Algorithm S from Knuth 3.4.2 */ typedef struct { *************** *** 81,86 **** typedef struct AnlIndexData --- 89,95 ---- int default_statistics_target = 100; /* A few variables that don't seem worth passing around as parameters */ + static AnalyzeMode anl_mode; static MemoryContext anl_context = NULL; static BufferAccessStrategy vac_strategy; *************** *** 102,107 **** static int acquire_sample_rows(Relation onerel, int elevel, --- 111,117 ---- HeapTuple *rows, int targrows, double *totalrows, double *totaldeadrows); static int compare_rows(const void *a, const void *b); + static bool has_foreign_children(Oid parentOID, List *tableOIDs); static int acquire_inherited_sample_rows(Relation onerel, int elevel, HeapTuple *rows, int targrows, double *totalrows, double *totaldeadrows); *************** *** 130,135 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, --- 140,154 ---- elevel = DEBUG2; /* Set up static variables */ + if (IsAutoVacuumWorkerProcess()) + anl_mode = ANL_MODE_AUTOVACUUM; + else + { + if (!vacstmt->relation) + anl_mode = ANL_MODE_ALL; + else + anl_mode = ANL_MODE_SINGLE; + } vac_strategy = bstrategy; /* *************** *** 297,305 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, * do_analyze_rel() -- analyze one relation, recursively or not * * Note that "acquirefunc" is only relevant for the non-inherited case. ! * If we supported foreign tables in inheritance trees, ! * acquire_inherited_sample_rows would need to determine the appropriate ! * acquirefunc for each child table. */ static void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, --- 316,323 ---- * do_analyze_rel() -- analyze one relation, recursively or not * * Note that "acquirefunc" is only relevant for the non-inherited case. ! * For the inherited case, acquire_inherited_sample_rows determines the ! * appropriate acquirefunc for each child table. */ static void do_analyze_rel(Relation onerel, VacuumStmt *vacstmt, *************** *** 1444,1454 **** compare_rows(const void *a, const void *b) /* * acquire_inherited_sample_rows -- acquire sample rows from inheritance tree * * This has the same API as acquire_sample_rows, except that rows are * collected from all inheritance children as well as the specified table. ! * We fail and return zero if there are no inheritance children. */ static int acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1462,1512 ---- /* + * Detect wether the inheritance tree contains any foreign tables + */ + static bool + has_foreign_children(Oid parentOID, List *tableOIDs) + { + bool found; + ListCell *lc; + + /* There are no children */ + if (list_length(tableOIDs) < 2) + return false; + + found = false; + foreach(lc, tableOIDs) + { + Oid childOID = lfirst_oid(lc); + Relation childrel; + + /* Parent should not be foreign */ + if (childOID == parentOID) + continue; + + /* We already got the needed lock */ + childrel = heap_open(childOID, NoLock); + if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + /* Found it */ + found = true; + } + heap_close(childrel, NoLock); + + if (found) + return true; + } + return false; + } + + /* * acquire_inherited_sample_rows -- acquire sample rows from inheritance tree * * This has the same API as acquire_sample_rows, except that rows are * collected from all inheritance children as well as the specified table. ! * We fail and return zero if there are no inheritance children, or if ! * inheritance tree contains any foreign tables and we are doing an ! * ANALYZE-with-no-parameter or in an autovacuum process. */ static int acquire_inherited_sample_rows(Relation onerel, int elevel, *************** *** 1457,1462 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1515,1521 ---- { List *tableOIDs; Relation *rels; + AcquireSampleRowsFunc *acquirefunc; double *relblocks; double totalblocks; int numrows, *************** *** 1491,1500 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1550,1580 ---- } /* + * If we are doing an ANALYZE-with-no-parameter or in an autovacuum process + * and inheritance tree contains any foreign tables, then fail. + */ + if (anl_mode != ANL_MODE_SINGLE) + { + if (has_foreign_children(RelationGetRelid(onerel), tableOIDs)) + { + ereport(elevel, + (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains foreign tables", + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel)), + errhint("Try ANALYZE \"%s.%s\" for the inheritance statistics.", + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel)))); + return 0; + } + } + + /* * Count the blocks in all the relations. The result could overflow * BlockNumber, so we use double arithmetic. */ rels = (Relation *) palloc(list_length(tableOIDs) * sizeof(Relation)); + acquirefunc = (AcquireSampleRowsFunc *) palloc(list_length(tableOIDs) + * sizeof(AcquireSampleRowsFunc)); relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double)); totalblocks = 0; nrels = 0; *************** *** 1516,1522 **** acquire_inherited_sample_rows(Relation onerel, int elevel, } rels[nrels] = childrel; ! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel); totalblocks += relblocks[nrels]; nrels++; } --- 1596,1631 ---- } rels[nrels] = childrel; ! ! if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) ! { ! FdwRoutine *fdwroutine; ! BlockNumber relpages = 0; ! bool ok = false; ! ! /* Check whether the FDW supports analysis */ ! fdwroutine = GetFdwRoutineForRelation(childrel, false); ! if (fdwroutine->AnalyzeForeignTable != NULL) ! ok = fdwroutine->AnalyzeForeignTable(childrel, ! &acquirefunc[nrels], ! &relpages); ! if (!ok) ! { ! /* Give up if the FDW doesn't support analysis */ ! ereport(WARNING, ! (errmsg("skipping analyze of inheritance tree \"%s\" --- cannot analyze foreign table \"%s\"", ! RelationGetRelationName(onerel), ! RelationGetRelationName(childrel)))); ! return 0; ! } ! relblocks[nrels] = (double) relpages; ! } ! else ! { ! acquirefunc[nrels] = acquire_sample_rows; ! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel); ! } ! totalblocks += relblocks[nrels]; nrels++; } *************** *** 1534,1539 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1643,1649 ---- { Relation childrel = rels[i]; double childblocks = relblocks[i]; + AcquireSampleRowsFunc childacquirefunc = acquirefunc[i]; if (childblocks > 0) { *************** *** 1549,1560 **** acquire_inherited_sample_rows(Relation onerel, int elevel, tdrows; /* Fetch a random sample of the child's rows */ ! childrows = acquire_sample_rows(childrel, ! elevel, ! rows + numrows, ! childtargrows, ! &trows, ! &tdrows); /* We may need to convert from child's rowtype to parent's */ if (childrows > 0 && --- 1659,1670 ---- tdrows; /* Fetch a random sample of the child's rows */ ! childrows = childacquirefunc(childrel, ! elevel, ! rows + numrows, ! childtargrows, ! &trows, ! &tdrows); /* We may need to convert from child's rowtype to parent's */ if (childrows > 0 && *** a/src/backend/commands/explain.c --- b/src/backend/commands/explain.c *************** *** 2230,2259 **** ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) } /* * Show extra information for a ModifyTable node */ static void show_modifytable_info(ModifyTableState *mtstate, ExplainState *es) { ! FdwRoutine *fdwroutine = mtstate->resultRelInfo->ri_FdwRoutine; ! /* ! * If the first target relation is a foreign table, call its FDW to ! * display whatever additional fields it wants to. For now, we ignore the ! * possibility of other targets being foreign tables, although the API for ! * ExplainForeignModify is designed to allow them to be processed. ! */ ! if (fdwroutine != NULL && ! fdwroutine->ExplainForeignModify != NULL) { ! ModifyTable *node = (ModifyTable *) mtstate->ps.plan; ! List *fdw_private = (List *) linitial(node->fdwPrivLists); ! fdwroutine->ExplainForeignModify(mtstate, ! mtstate->resultRelInfo, ! fdw_private, ! 0, ! es); } } --- 2230,2327 ---- } /* + * Open a group for foreign modification + */ + void + ExplainOpenForeignModifyGroup(ModifyTable *plan, int subplan_index, + ExplainState *es) + { + char *objectname; + char *namespace; + char *refname; + Index rti; + RangeTblEntry *rte; + + Assert(subplan_index > 0); + + /* Open a group */ + ExplainOpenGroup("Foreign Modification", "Foreign Modification", true, es); + + /* Show the target relation information */ + Assert(plan->resultRelations != NIL); + rti = list_nth_int(plan->resultRelations, subplan_index); + rte = rt_fetch(rti, es->rtable); + /* Assert it's on a real relation */ + Assert(rte->rtekind == RTE_RELATION); + objectname = get_rel_name(rte->relid); + namespace = get_namespace_name(get_rel_namespace(rte->relid)); + refname = (char *) list_nth(es->rtable_names, rti - 1); + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + appendStringInfoSpaces(es->str, es->indent * 2); + appendStringInfoString(es->str, "For"); + appendStringInfo(es->str, " %s.%s", quote_identifier(namespace), + quote_identifier(objectname)); + if (strcmp(refname, objectname) != 0) + appendStringInfo(es->str, " %s", quote_identifier(refname)); + appendStringInfoChar(es->str, '\n'); + es->indent++; + } + else + { + ExplainPropertyText("Relation Name", objectname, es); + ExplainPropertyText("Schema", namespace, es); + ExplainPropertyText("Alias", refname, es); + } + } + + /* + * Close a group for foreign modification + */ + void + ExplainCloseForeignModifyGroup(ModifyTable *plan, int subplan_index, + ExplainState *es) + { + Assert(subplan_index > 0); + + /* Undo the indentation we added in text format */ + if (es->format == EXPLAIN_FORMAT_TEXT) + es->indent--; + + /* Close a group */ + ExplainCloseGroup("Foreign Modification", "Foreign Modification", true, es); + } + + /* * Show extra information for a ModifyTable node */ static void show_modifytable_info(ModifyTableState *mtstate, ExplainState *es) { ! ModifyTable *plan = (ModifyTable *) mtstate->ps.plan; ! int j; ! for (j = 0; j < mtstate->mt_nplans; j++) { ! ResultRelInfo *resultRelInfo = mtstate->resultRelInfo + j; ! FdwRoutine *fdwroutine = resultRelInfo->ri_FdwRoutine; ! /* ! * If the target relation is a foreign table, call its FDW to display ! * whatever additional fields it wants to. ! */ ! if (fdwroutine != NULL && ! fdwroutine->ExplainForeignModify != NULL) ! { ! List *fdw_private = (List *) list_nth(plan->fdwPrivLists, j); ! ! fdwroutine->ExplainForeignModify(mtstate, ! resultRelInfo, ! fdw_private, ! j, ! es); ! } } } *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 318,324 **** static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel); static void ATSimplePermissions(Relation rel, int allowed_targets); static void ATWrongRelkindError(Relation rel, int allowed_targets); static void ATSimpleRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode); static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd, LOCKMODE lockmode); static List *find_typed_table_dependencies(Oid typeOid, const char *typeName, --- 318,325 ---- static void ATSimplePermissions(Relation rel, int allowed_targets); static void ATWrongRelkindError(Relation rel, int allowed_targets); static void ATSimpleRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd, bool recurse, ! bool include_foreign, LOCKMODE lockmode); static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd, LOCKMODE lockmode); static List *find_typed_table_dependencies(Oid typeOid, const char *typeName, *************** *** 568,573 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) --- 569,598 ---- stmt->relation->relpersistence, &inheritOids, &old_constraints, &parentOidCount); + if (relkind == RELKIND_FOREIGN_TABLE) + { + /* + * Don't allow a foreign table to inherit from parents that have OID + * system columns. + */ + if (parentOidCount > 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot inherit from relation with OIDs"))); + + /* + * Reset the storage parameter for inherited attributes that have + * non-default values. + */ + foreach(listptr, schema) + { + ColumnDef *colDef = lfirst(listptr); + + if (colDef->storage != 0) + colDef->storage = 0; + } + } + /* * Create a tuple descriptor from the relation schema. Note that this * deals with column names, types, and NOT NULL constraints, but not *************** *** 1025,1030 **** ExecuteTruncate(TruncateStmt *stmt) --- 1050,1061 ---- /* find_all_inheritors already got lock */ rel = heap_open(childrelid, NoLock); + /* ignore if the rel is a foreign table */ + if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + heap_close(rel, NoLock); + continue; + } truncate_check_rel(rel); rels = lappend(rels, rel); relids = lappend_oid(relids, childrelid); *************** *** 3102,3125 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, * rules. */ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_DROP; break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_ADD_CONSTR; break; case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* Performs own permission checks */ ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode); pass = AT_PASS_MISC; --- 3133,3160 ---- * rules. */ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); ! /* Recurse to child tables that are foreign, too */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode); /* No command-specific prep needed */ pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ! /* Recurse to child tables that are foreign, too */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode); /* No command-specific prep needed */ pass = AT_PASS_DROP; break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ! /* Recurse to child tables that are foreign, too */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode); /* No command-specific prep needed */ pass = AT_PASS_ADD_CONSTR; break; case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */ ! /* Recurse to child tables that are foreign, too */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode); /* Performs own permission checks */ ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode); pass = AT_PASS_MISC; *************** *** 3132,3138 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); /* No command-specific prep needed */ pass = AT_PASS_MISC; break; --- 3167,3174 ---- break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); ! /* Don't recurse to child tables that are foreign */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, false, lockmode); /* No command-specific prep needed */ pass = AT_PASS_MISC; break; *************** *** 3254,3264 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_MISC; break; case AT_AddInherit: /* INHERIT */ ! ATSimplePermissions(rel, ATT_TABLE); /* This command never recurses */ ATPrepAddInherit(rel); pass = AT_PASS_MISC; break; case AT_AlterConstraint: /* ALTER CONSTRAINT */ ATSimplePermissions(rel, ATT_TABLE); pass = AT_PASS_MISC; --- 3290,3306 ---- pass = AT_PASS_MISC; break; case AT_AddInherit: /* INHERIT */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* This command never recurses */ ATPrepAddInherit(rel); pass = AT_PASS_MISC; break; + case AT_DropInherit: /* NO INHERIT */ + ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); + /* This command never recurses */ + /* No command-specific prep needed */ + pass = AT_PASS_MISC; + break; case AT_AlterConstraint: /* ALTER CONSTRAINT */ ATSimplePermissions(rel, ATT_TABLE); pass = AT_PASS_MISC; *************** *** 3292,3298 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, case AT_EnableAlwaysRule: case AT_EnableReplicaRule: case AT_DisableRule: - case AT_DropInherit: /* NO INHERIT */ case AT_AddOf: /* OF */ case AT_DropOf: /* NOT OF */ case AT_EnableRowSecurity: --- 3334,3339 ---- *************** *** 4292,4298 **** ATWrongRelkindError(Relation rel, int allowed_targets) */ static void ATSimpleRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode) { /* * Propagate to children if desired. Non-table relations never have --- 4333,4340 ---- */ static void ATSimpleRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd, bool recurse, ! bool include_foreign, LOCKMODE lockmode) { /* * Propagate to children if desired. Non-table relations never have *************** *** 4320,4327 **** ATSimpleRecursion(List **wqueue, Relation rel, continue; /* find_all_inheritors already got lock */ childrel = relation_open(childrelid, NoLock); ! CheckTableNotInUse(childrel, "ALTER TABLE"); ! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode); relation_close(childrel, NoLock); } } --- 4362,4373 ---- continue; /* find_all_inheritors already got lock */ childrel = relation_open(childrelid, NoLock); ! if (childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE ! || include_foreign) ! { ! CheckTableNotInUse(childrel, "ALTER TABLE"); ! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode); ! } relation_close(childrel, NoLock); } } *************** *** 4611,4617 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE); attrdesc = heap_open(AttributeRelationId, RowExclusiveLock); --- 4657,4663 ---- /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); attrdesc = heap_open(AttributeRelationId, RowExclusiveLock); *************** *** 4907,4912 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, --- 4953,4963 ---- /* find_inheritance_children already got lock */ childrel = heap_open(childrelid, NoLock); + if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE && isOid) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot add OID column to foreign table \"%s\"", + RelationGetRelationName(childrel)))); CheckTableNotInUse(childrel, "ALTER TABLE"); /* Find or create work queue entry for this table */ *************** *** 5507,5513 **** ATExecDropColumn(List **wqueue, Relation rel, const char *colName, /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE); /* * get the number of the attribute --- 5558,5564 ---- /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* * get the number of the attribute *************** *** 5899,5905 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE); /* * Call AddRelationNewConstraints to do the work, making sure it works on --- 5950,5956 ---- /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* * Call AddRelationNewConstraints to do the work, making sure it works on *************** *** 5910,5918 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, * omitted from the returned list, which is what we want: we do not need * to do any validation work. That can only happen at child tables, * though, since we disallow merging at the top level. */ newcons = AddRelationNewConstraints(rel, NIL, ! list_make1(copyObject(constr)), recursing, /* allow_merge */ !recursing, /* is_local */ is_readd); /* is_internal */ --- 5961,5977 ---- * omitted from the returned list, which is what we want: we do not need * to do any validation work. That can only happen at child tables, * though, since we disallow merging at the top level. + * + * When propagating a NOT VALID option to children that are foreign tables, + * we quietly ignore the option. Note that this is safe because foreign + * tables don't have any children. */ + constr = (Constraint *) copyObject(constr); + if (tab->relkind == RELKIND_FOREIGN_TABLE && + constr->skip_validation && recursing) + constr->skip_validation = false; newcons = AddRelationNewConstraints(rel, NIL, ! list_make1(constr), recursing, /* allow_merge */ !recursing, /* is_local */ is_readd); /* is_internal */ *************** *** 7399,7405 **** ATExecDropConstraint(Relation rel, const char *constrName, /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE); conrel = heap_open(ConstraintRelationId, RowExclusiveLock); --- 7458,7464 ---- /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); conrel = heap_open(ConstraintRelationId, RowExclusiveLock); *************** *** 7734,7740 **** ATPrepAlterColumnType(List **wqueue, * alter would put them out of step. */ if (recurse) ! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); else if (!recursing && find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL) ereport(ERROR, --- 7793,7802 ---- * alter would put them out of step. */ if (recurse) ! { ! /* Recurse to child tables that are foreign, too */ ! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode); ! } else if (!recursing && find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL) ereport(ERROR, *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *************** *** 2307,2312 **** EvalPlanQualFetchRowMarks(EPQState *epqstate) --- 2307,2332 ---- Assert(erm->markType == ROW_MARK_COPY); + /* if child rel, must check whether it produced this row */ + if (erm->rti != erm->prti) + { + Oid tableoid; + + datum = ExecGetJunkAttribute(epqstate->origslot, + aerm->toidAttNo, + &isNull); + /* non-locked rels could be on the inside of outer joins */ + if (isNull) + continue; + tableoid = DatumGetObjectId(datum); + + if (tableoid != RelationGetRelid(erm->relation)) + { + /* this child is inactive right now */ + continue; + } + } + /* fetch the whole-row Var for the relation */ datum = ExecGetJunkAttribute(epqstate->origslot, aerm->wholeAttNo, *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 2024,2029 **** _copyRangeTblEntry(const RangeTblEntry *from) --- 2024,2030 ---- COPY_NODE_FIELD(eref); COPY_SCALAR_FIELD(lateral); COPY_SCALAR_FIELD(inh); + COPY_SCALAR_FIELD(hasForeignChildren); COPY_SCALAR_FIELD(inFromCl); COPY_SCALAR_FIELD(requiredPerms); COPY_SCALAR_FIELD(checkAsUser); *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 2342,2347 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) --- 2342,2348 ---- COMPARE_NODE_FIELD(eref); COMPARE_SCALAR_FIELD(lateral); COMPARE_SCALAR_FIELD(inh); + COMPARE_SCALAR_FIELD(hasForeignChildren); COMPARE_SCALAR_FIELD(inFromCl); COMPARE_SCALAR_FIELD(requiredPerms); COMPARE_SCALAR_FIELD(checkAsUser); *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 2452,2457 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) --- 2452,2458 ---- WRITE_BOOL_FIELD(lateral); WRITE_BOOL_FIELD(inh); + WRITE_BOOL_FIELD(hasForeignChildren); WRITE_BOOL_FIELD(inFromCl); WRITE_UINT_FIELD(requiredPerms); WRITE_OID_FIELD(checkAsUser); *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 1249,1254 **** _readRangeTblEntry(void) --- 1249,1255 ---- READ_BOOL_FIELD(lateral); READ_BOOL_FIELD(inh); + READ_BOOL_FIELD(hasForeignChildren); READ_BOOL_FIELD(inFromCl); READ_UINT_FIELD(requiredPerms); READ_OID_FIELD(checkAsUser); *** a/src/backend/optimizer/prep/preptlist.c --- b/src/backend/optimizer/prep/preptlist.c *************** *** 111,116 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 111,118 ---- /* if parent of inheritance tree, need the tableoid too */ if (rc->isParent) { + RangeTblEntry *rte = rt_fetch(rc->rti, parse->rtable); + var = makeVar(rc->rti, TableOidAttributeNumber, OIDOID, *************** *** 123,128 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 125,146 ---- pstrdup(resname), true); tlist = lappend(tlist, tle); + + /* if containing foreign tables, fetch the whole row too */ + if (rte->hasForeignChildren) + { + /* Not a table, so we need the whole row as a junk var */ + var = makeWholeRowVar(rt_fetch(rc->rti, range_table), + rc->rti, + 0, + false); + snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId); + tle = makeTargetEntry((Expr *) var, + list_length(tlist) + 1, + pstrdup(resname), + true); + tlist = lappend(tlist, tle); + } } } else *** a/src/backend/optimizer/prep/prepunion.c --- b/src/backend/optimizer/prep/prepunion.c *************** *** 1237,1242 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1237,1243 ---- LOCKMODE lockmode; List *inhOIDs; List *appinfos; + bool hasForeignChildren; ListCell *l; /* Does RT entry allow inheritance? */ *************** *** 1309,1314 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1310,1316 ---- /* Scan the inheritance set and expand it */ appinfos = NIL; + hasForeignChildren = false; foreach(l, inhOIDs) { Oid childOID = lfirst_oid(l); *************** *** 1338,1348 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID, ! * and set inh = false. Also, set requiredPerms to zero since all ! * required permissions checks are done on the original RTE. */ childrte = copyObject(rte); childrte->relid = childOID; childrte->inh = false; childrte->requiredPerms = 0; parse->rtable = lappend(parse->rtable, childrte); --- 1340,1351 ---- /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID, ! * relkind and set inh = false. Also, set requiredPerms to zero since ! * all required permissions checks are done on the original RTE. */ childrte = copyObject(rte); childrte->relid = childOID; + childrte->relkind = newrelation->rd_rel->relkind; childrte->inh = false; childrte->requiredPerms = 0; parse->rtable = lappend(parse->rtable, childrte); *************** *** 1388,1400 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->rti = childRTindex; newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; ! newrc->markType = oldrc->markType; newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; root->rowMarks = lappend(root->rowMarks, newrc); } /* Close child relations, but keep locks */ if (childOID != parentOID) heap_close(newrelation, NoLock); --- 1391,1409 ---- newrc->rti = childRTindex; newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; ! if (childrte->relkind == RELKIND_FOREIGN_TABLE) ! newrc->markType = ROW_MARK_COPY; ! else ! newrc->markType = oldrc->markType; newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; root->rowMarks = lappend(root->rowMarks, newrc); } + if (childrte->relkind == RELKIND_FOREIGN_TABLE) + hasForeignChildren = true; + /* Close child relations, but keep locks */ if (childOID != parentOID) heap_close(newrelation, NoLock); *************** *** 1416,1421 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1425,1433 ---- /* Otherwise, OK to add to root->append_rel_list */ root->append_rel_list = list_concat(root->append_rel_list, appinfos); + + /* And set rte->hasForeignChildren */ + rte->hasForeignChildren = hasForeignChildren; } /* *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 4335,4366 **** AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o CreateForeignTableStmt: CREATE FOREIGN TABLE qualified_name '(' OptTableElementList ')' ! SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $4->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $4; n->base.tableElts = $6; ! n->base.inhRelations = NIL; n->base.if_not_exists = false; /* FDW-specific data */ ! n->servername = $9; ! n->options = $10; $$ = (Node *) n; } | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name '(' OptTableElementList ')' ! SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $7->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $7; n->base.tableElts = $9; ! n->base.inhRelations = NIL; n->base.if_not_exists = true; /* FDW-specific data */ ! n->servername = $12; ! n->options = $13; $$ = (Node *) n; } ; --- 4335,4366 ---- CreateForeignTableStmt: CREATE FOREIGN TABLE qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $4->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $4; n->base.tableElts = $6; ! n->base.inhRelations = $8; n->base.if_not_exists = false; /* FDW-specific data */ ! n->servername = $10; ! n->options = $11; $$ = (Node *) n; } | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $7->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $7; n->base.tableElts = $9; ! n->base.inhRelations = $11; n->base.if_not_exists = true; /* FDW-specific data */ ! n->servername = $13; ! n->options = $14; $$ = (Node *) n; } ; *** a/src/include/commands/explain.h --- b/src/include/commands/explain.h *************** *** 91,94 **** extern void ExplainPropertyLong(const char *qlabel, long value, --- 91,99 ---- extern void ExplainPropertyFloat(const char *qlabel, double value, int ndigits, ExplainState *es); + extern void ExplainOpenForeignModifyGroup(ModifyTable *plan, int subplan_index, + ExplainState *es); + extern void ExplainCloseForeignModifyGroup(ModifyTable *plan, int subplan_index, + ExplainState *es); + #endif /* EXPLAIN_H */ *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 812,817 **** typedef struct RangeTblEntry --- 812,819 ---- Alias *eref; /* expanded reference names */ bool lateral; /* subquery, function, or values is LATERAL? */ bool inh; /* inheritance requested? */ + bool hasForeignChildren; /* does inheritance set include + * any foreign tables? */ bool inFromCl; /* present in FROM clause? */ AclMode requiredPerms; /* bitmask of required access permissions */ Oid checkAsUser; /* if valid, check access as this role */ *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** *** 850,855 **** typedef enum RowMarkType --- 850,857 ---- * The tableoid column is only present for an inheritance hierarchy. * When markType == ROW_MARK_COPY, there is instead a single column named * wholerow%u whole-row value of relation + * The wholerow column is also present for an inheritance hierarchy that + * contains foreign tables. * In all three cases, %u represents the rowmark ID number (rowmarkId). * This number is unique within a plan tree, except that child relation * entries copy their parent's rowmarkId. (Assigning unique numbers *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *************** *** 1242,1247 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; --- 1242,1652 ---- DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1; DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; DROP FUNCTION dummy_trigger(); + -- Table inheritance + CREATE TABLE pt1 ( + c1 integer NOT NULL, + c2 text, + c3 date + ); + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | | + c2 | text | | extended | | + c3 | date | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + DROP FOREIGN TABLE ft2; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | | + c2 | text | | extended | | + c3 | date | | plain | | + + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + + ALTER FOREIGN TABLE ft2 INHERIT pt1; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | | + c2 | text | | extended | | + c3 | date | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date; + ALTER TABLE v1 INHERIT pt1; -- ERROR + ERROR: "v1" is not a table or foreign table + DROP VIEW v1; + -- add attributes recursively + ALTER TABLE pt1 ADD COLUMN c4 integer; + ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0; + ALTER TABLE pt1 ADD COLUMN c6 integer; + ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL; + ALTER TABLE pt1 ADD COLUMN c8 integer; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | | + c2 | text | | extended | | + c3 | date | | plain | | + c4 | integer | | plain | | + c5 | integer | default 0 | plain | | + c6 | integer | | plain | | + c7 | integer | not null | plain | | + c8 | integer | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + c4 | integer | | | plain | | + c5 | integer | default 0 | | plain | | + c6 | integer | | | plain | | + c7 | integer | not null | | plain | | + c8 | integer | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- alter attributes recursively + ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0; + ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT; + ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL; + ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL; + ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR + ERROR: "ft2" is not a table + ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10); + ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text; + ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000; + ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100); + ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + c4 | integer | default 0 | plain | | + c5 | integer | | plain | | + c6 | integer | not null | plain | | + c7 | integer | | plain | | + c8 | text | | extended | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | 10000 | + c2 | text | | | extended | | + c3 | date | | | plain | | + c4 | integer | default 0 | | plain | | + c5 | integer | | | plain | | + c6 | integer | not null | | plain | | + c7 | integer | | | plain | | + c8 | text | | | extended | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- drop attributes recursively + ALTER TABLE pt1 DROP COLUMN c4; + ALTER TABLE pt1 DROP COLUMN c5; + ALTER TABLE pt1 DROP COLUMN c6; + ALTER TABLE pt1 DROP COLUMN c7; + ALTER TABLE pt1 DROP COLUMN c8; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | 10000 | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- cannot change storage mode for an attribute of foreign tables + ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | external | | + c3 | date | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | 10000 | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | external | | + c3 | date | | plain | | + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED; + -- add constraints recursively + ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT; + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); + -- connoinherit should be true for NO INHERIT constraint + SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2; + relname | conname | contype | conislocal | coninhcount | connoinherit + ---------+---------+---------+------------+-------------+-------------- + pt1 | pt1chk1 | c | t | 0 | t + pt1 | pt1chk2 | c | t | 0 | f + (2 rows) + + -- child does not inherit NO INHERIT constraints + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + Check constraints: + "pt1chk1" CHECK (c1 > 0) NO INHERIT + "pt1chk2" CHECK (c2 <> ''::text) + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + -- child must have parent's INHERIT constraints + ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR + ERROR: child table is missing constraint "pt1chk2" + ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); + ALTER FOREIGN TABLE ft2 INHERIT pt1; + -- child does not inherit NO INHERIT constraints + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + Check constraints: + "pt1chk1" CHECK (c1 > 0) NO INHERIT + "pt1chk2" CHECK (c2 <> ''::text) + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- drop constraints recursively + ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE; + ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE; + -- NOT VALID should be ignored for foreign tables + INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date); + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID; + NOTICE: merging constraint "pt1chk2" with inherited definition + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) NOT VALID + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- VALIDATE CONSTRAINT should work by ignoring foreign tables + ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + c1 | integer | not null | plain | 10000 | + c2 | text | | extended | | + c3 | date | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + c1 | integer | not null | | plain | | + c2 | text | | | extended | | + c3 | date | | | plain | | + Check constraints: + "pt1chk2" CHECK (c2 <> ''::text) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- cannot add an OID system column to foreign tables + ALTER TABLE pt1 SET WITH OIDS; -- ERROR + ERROR: cannot add OID column to foreign table "ft2" + DROP FOREIGN TABLE ft2; + ALTER TABLE pt1 SET WITH OIDS; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR + ERROR: cannot inherit from relation with OIDs + ALTER TABLE pt1 SET WITHOUT OIDS; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + -- changes name of an attribute recursively + ALTER TABLE pt1 RENAME COLUMN c1 TO f1; + ALTER TABLE pt1 RENAME COLUMN c2 TO f2; + ALTER TABLE pt1 RENAME COLUMN c3 TO f3; + -- changes name of a constraint recursively + ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + f1 | integer | not null | plain | 10000 | + f2 | text | | extended | | + f3 | date | | plain | | + Check constraints: + "f2_check" CHECK (f2 <> ''::text) + Child tables: ft2 + + \d+ ft2 + Foreign table "public.ft2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + f1 | integer | not null | | plain | | + f2 | text | | | extended | | + f3 | date | | | plain | | + Check constraints: + "f2_check" CHECK (f2 <> ''::text) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + -- TRUNCATE should work by ignoring foreign tables + TRUNCATE pt1; + TRUNCATE ft2; -- ERROR + ERROR: "ft2" is not a table + DROP TABLE pt1 CASCADE; + NOTICE: drop cascades to foreign table ft2 -- IMPORT FOREIGN SCHEMA IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR ERROR: foreign-data wrapper "foo" has no handler *** a/src/test/regress/sql/foreign_data.sql --- b/src/test/regress/sql/foreign_data.sql *************** *** 542,547 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; --- 542,676 ---- DROP FUNCTION dummy_trigger(); + -- Table inheritance + CREATE TABLE pt1 ( + c1 integer NOT NULL, + c2 text, + c3 date + ); + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + \d+ ft2 + DROP FOREIGN TABLE ft2; + \d+ pt1 + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ ft2 + ALTER FOREIGN TABLE ft2 INHERIT pt1; + \d+ pt1 + \d+ ft2 + CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date; + ALTER TABLE v1 INHERIT pt1; -- ERROR + DROP VIEW v1; + + -- add attributes recursively + ALTER TABLE pt1 ADD COLUMN c4 integer; + ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0; + ALTER TABLE pt1 ADD COLUMN c6 integer; + ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL; + ALTER TABLE pt1 ADD COLUMN c8 integer; + \d+ pt1 + \d+ ft2 + + -- alter attributes recursively + ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0; + ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT; + ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL; + ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL; + ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR + ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10); + ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text; + ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000; + ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100); + ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1; + \d+ pt1 + \d+ ft2 + + -- drop attributes recursively + ALTER TABLE pt1 DROP COLUMN c4; + ALTER TABLE pt1 DROP COLUMN c5; + ALTER TABLE pt1 DROP COLUMN c6; + ALTER TABLE pt1 DROP COLUMN c7; + ALTER TABLE pt1 DROP COLUMN c8; + \d+ pt1 + \d+ ft2 + + -- cannot change storage mode for an attribute of foreign tables + ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL; + \d+ pt1 + \d+ ft2 + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + \d+ ft2 + ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED; + + -- add constraints recursively + ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT; + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); + -- connoinherit should be true for NO INHERIT constraint + SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2; + -- child does not inherit NO INHERIT constraints + \d+ pt1 + \d+ ft2 + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + -- child must have parent's INHERIT constraints + ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR + ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> ''); + ALTER FOREIGN TABLE ft2 INHERIT pt1; + -- child does not inherit NO INHERIT constraints + \d+ pt1 + \d+ ft2 + + -- drop constraints recursively + ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE; + ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE; + + -- NOT VALID should be ignored for foreign tables + INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date); + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID; + \d+ pt1 + \d+ ft2 + -- VALIDATE CONSTRAINT should work by ignoring foreign tables + ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2; + \d+ pt1 + \d+ ft2 + + -- cannot add an OID system column to foreign tables + ALTER TABLE pt1 SET WITH OIDS; -- ERROR + DROP FOREIGN TABLE ft2; + ALTER TABLE pt1 SET WITH OIDS; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR + ALTER TABLE pt1 SET WITHOUT OIDS; + CREATE FOREIGN TABLE ft2 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + + -- changes name of an attribute recursively + ALTER TABLE pt1 RENAME COLUMN c1 TO f1; + ALTER TABLE pt1 RENAME COLUMN c2 TO f2; + ALTER TABLE pt1 RENAME COLUMN c3 TO f3; + -- changes name of a constraint recursively + ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check; + \d+ pt1 + \d+ ft2 + + -- TRUNCATE should work by ignoring foreign tables + TRUNCATE pt1; + TRUNCATE ft2; -- ERROR + + DROP TABLE pt1 CASCADE; + -- IMPORT FOREIGN SCHEMA IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR --------------000608020900030007080606 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 --------------000608020900030007080606--