Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YOhZM-0005oU-HS for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Feb 2015 07:00:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YOhZL-0008Ie-Ot for pgsql-hackers@arkaria.postgresql.org; Fri, 20 Feb 2015 07:00:11 +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 1YOhZJ-0008HQ-0S for pgsql-hackers@postgresql.org; Fri, 20 Feb 2015 07:00:09 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YOhZ6-0002VO-GU for pgsql-hackers@postgresql.org; Fri, 20 Feb 2015 07:00:07 +0000 Received: from vc1.ecl.ntt.co.jp (vc1.ecl.ntt.co.jp [129.60.86.153]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id t1K6xm5L005280; Fri, 20 Feb 2015 15:59:48 +0900 Received: from vc1.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id 56B985F593; Fri, 20 Feb 2015 15:59:48 +0900 (JST) Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248]) by vc1.ecl.ntt.co.jp (Postfix) with ESMTP id 4621B5F587; Fri, 20 Feb 2015 15:59:48 +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 t1K6xlh0001128; Fri, 20 Feb 2015 15:59:48 +0900 Message-ID: <54E6DB4E.40801@lab.ntt.co.jp> Date: Fri, 20 Feb 2015 15:59:26 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Tom Lane CC: Ashutosh Bapat , 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> <5487BB9D.7070605@lab.ntt.co.jp> <30555.1418853840@sss.pgh.pa.us> <5497FE79.7090804@lab.ntt.co.jp> <19367.1419262577@sss.pgh.pa.us> <54B76DD2.1040106@lab.ntt.co.jp> In-Reply-To: <54B76DD2.1040106@lab.ntt.co.jp> Content-Type: multipart/mixed; boundary="------------000700000201020000050307" 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. --------------000700000201020000050307 Content-Type: text/plain; charset=iso-2022-jp Content-Transfer-Encoding: 7bit On 2015/01/15 16:35, Etsuro Fujita wrote: > On 2014/12/23 0:36, Tom Lane wrote: >> Yeah, we need to do something about the PlanRowMark data structure. >> Aside from the pre-existing issue in postgres_fdw, we need to fix it >> to support inheritance trees in which more than one rowmark method >> is being used. That rte.hasForeignChildren thing is a crock, > >> The idea I'd had about that was to convert the markType field into a >> bitmask, so that a parent node's markType could represent the logical >> OR of the rowmark methods being used by all its children. > As I said before, that seems to me like a good idea. So I'll update the > patch based on that if you're okey with it. Done based on your ideas: (a) add a field to PlanRowMark to record the original lock strength to fix the postgres_fdw issue and (b) convert its markType field into a bitmask to support the inheritance trees. I think that both work well and that (a) is useful for the other places. Patch attached, which has been created on top of [1]. Best regards, Etsuro Fujita [1] http://www.postgresql.org/message-id/54BCBBF8.3020103@lab.ntt.co.jp --------------000700000201020000050307 Content-Type: text/x-diff; name="fdw-inh-8.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-inh-8.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; RESET constraint_exclusion; + -- 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) RESET constraint_exclusion; + -- 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 *************** *** 3027,3032 **** NOTICE: NEW: (13,"test triggered !") --- 3027,3544 ---- (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 *************** *** 34,39 **** --- 34,40 ---- #include "parser/parsetree.h" #include "utils/builtins.h" #include "utils/guc.h" + #include "utils/lockclausestrength.h" #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/rel.h" *************** *** 822,828 **** postgresGetForeignPlan(PlannerInfo *root, } else { ! RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid); if (rc) { --- 823,829 ---- } else { ! PlanRowMark *rc = get_plan_rowmark(root->rowMarks, baserel->relid); if (rc) { *************** *** 837,842 **** postgresGetForeignPlan(PlannerInfo *root, --- 838,846 ---- */ switch (rc->strength) { + case LCS_NOLOCK: + /* No locking needed */ + break; case LCS_FORKEYSHARE: case LCS_FORSHARE: appendStringInfoString(&sql, " FOR SHARE"); *************** *** 1682,1691 **** postgresExplainForeignModify(ModifyTableState *mtstate, { if (es->verbose) { char *sql = strVal(list_nth(fdw_private, FdwModifyPrivateUpdateSql)); ! ExplainPropertyText("Remote SQL", sql, es); } } --- 1686,1717 ---- { if (es->verbose) { + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + Index rti = list_nth_int(node->resultRelations, subplan_index); char *sql = strVal(list_nth(fdw_private, FdwModifyPrivateUpdateSql)); ! if (rti != node->nominalRelation) ! { ! /* ! * Inherited UPDATE/DELETE case; show the SQL statement together ! * with the target relation. ! */ ! ExplainBeginInheritedForeignModify(node, subplan_index, es); ! ExplainPropertyText("Remote SQL", sql, es); ! ExplainEndInheritedForeignModify(node, subplan_index, es); ! } ! else ! { ! /* ! * Non-inherited normal case. ! * ! * Note: foreign tables can't be the parent of an inheritance set. ! * So if the RT index matches nominalRelation, we are in a non- ! * inherited normal case. ! */ ! ExplainPropertyText("Remote SQL", sql, es); ! } } } *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 666,671 **** UPDATE rem1 SET f2 = 'testo'; --- 666,822 ---- 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'] [, ... ]) *************** *** 188,193 **** 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 *************** *** 384,389 **** 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 *************** *** 1011,1016 **** ALTER TABLE ALL IN TABLESPACE name --- 1011,1029 ---- + 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. *************** *** 1019,1024 **** ALTER TABLE ALL IN TABLESPACE name --- 1032,1045 ---- + 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 **** CHECK ( expression ) --- 122,158 ---- + 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. + + + + + + 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. + + + + + NOT NULL *************** *** 249,254 **** CHECK ( expression ) --- 281,292 ---- responsibility to ensure that the constraint definition matches reality. + + + 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 *************** *** 2302,2331 **** 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); } } --- 2302,2399 ---- } /* + * Open a group for inherited foreign modification + */ + void + ExplainBeginInheritedForeignModify(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 */ + 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 inherited foreign modification + */ + void + ExplainEndInheritedForeignModify(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 *************** *** 1920,1940 **** ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist) aerm->rowmark = erm; /* Look up the resjunk columns associated with this rowmark */ if (erm->relation && erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) { Assert(erm->markType != ROW_MARK_COPY); - /* if child rel, need tableoid */ - if (erm->rti != erm->prti) - { - snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId); - aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist, - resname); - if (!AttributeNumberIsValid(aerm->toidAttNo)) - elog(ERROR, "could not find junk %s column", resname); - } - /* always need ctid for real relations */ snprintf(resname, sizeof(resname), "ctid%u", erm->rowmarkId); aerm->ctidAttNo = ExecFindJunkAttributeInTlist(targetlist, --- 1920,1941 ---- aerm->rowmark = erm; /* Look up the resjunk columns associated with this rowmark */ + + /* if child rel, need tableoid */ + if (erm->rti != erm->prti) + { + snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId); + aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist, + resname); + if (!AttributeNumberIsValid(aerm->toidAttNo)) + elog(ERROR, "could not find junk %s column", resname); + } + if (erm->relation && erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) { Assert(erm->markType != ROW_MARK_COPY); /* always need ctid for real relations */ snprintf(resname, sizeof(resname), "ctid%u", erm->rowmarkId); aerm->ctidAttNo = ExecFindJunkAttributeInTlist(targetlist, *************** *** 2385,2416 **** EvalPlanQualFetchRowMarks(EPQState *epqstate) /* clear any leftover test tuple for this rel */ EvalPlanQualSetTuple(epqstate, erm->rti, NULL); ! if (erm->relation && ! erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) { ! Buffer buffer; ! Assert(erm->markType == ROW_MARK_REFERENCE); ! /* 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 tuple's ctid */ datum = ExecGetJunkAttribute(epqstate->origslot, --- 2386,2419 ---- /* clear any leftover test tuple for this rel */ EvalPlanQualSetTuple(epqstate, erm->rti, NULL); ! /* if child rel, must check whether it produced this row */ ! if (erm->rti != erm->prti) { ! Oid tableoid; ! Assert(erm->relation); ! ! 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; ! } ! } ! if (erm->relation && ! erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE) ! { ! Buffer buffer; ! Assert(erm->markType == ROW_MARK_REFERENCE); /* fetch the tuple's ctid */ datum = ExecGetJunkAttribute(epqstate->origslot, *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 990,995 **** _copyPlanRowMark(const PlanRowMark *from) --- 990,996 ---- COPY_SCALAR_FIELD(prti); COPY_SCALAR_FIELD(rowmarkId); COPY_SCALAR_FIELD(markType); + COPY_SCALAR_FIELD(strength); COPY_SCALAR_FIELD(waitPolicy); COPY_SCALAR_FIELD(isParent); *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 850,856 **** _outPlanRowMark(StringInfo str, const PlanRowMark *node) WRITE_UINT_FIELD(rti); WRITE_UINT_FIELD(prti); WRITE_UINT_FIELD(rowmarkId); ! WRITE_ENUM_FIELD(markType, RowMarkType); WRITE_BOOL_FIELD(waitPolicy); WRITE_BOOL_FIELD(isParent); } --- 850,857 ---- WRITE_UINT_FIELD(rti); WRITE_UINT_FIELD(prti); WRITE_UINT_FIELD(rowmarkId); ! WRITE_UINT_FIELD(markType); ! WRITE_ENUM_FIELD(strength, LockClauseStrength); WRITE_BOOL_FIELD(waitPolicy); WRITE_BOOL_FIELD(isParent); } *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** *** 2249,2254 **** preprocess_rowmarks(PlannerInfo *root) --- 2249,2255 ---- newrc->markType = ROW_MARK_KEYSHARE; break; } + newrc->strength = rc->strength; newrc->waitPolicy = rc->waitPolicy; newrc->isParent = false; *************** *** 2262,2267 **** preprocess_rowmarks(PlannerInfo *root) --- 2263,2269 ---- foreach(l, parse->rtable) { RangeTblEntry *rte = (RangeTblEntry *) lfirst(l); + RowMarkClause *rc; PlanRowMark *newrc; i++; *************** *** 2277,2282 **** preprocess_rowmarks(PlannerInfo *root) --- 2279,2289 ---- newrc->markType = ROW_MARK_REFERENCE; else newrc->markType = ROW_MARK_COPY; + rc = get_parse_rowmark(parse, i); + if (rc) + newrc->strength = rc->strength; + else + newrc->strength = LCS_NOLOCK; newrc->waitPolicy = LockWaitBlock; /* doesn't matter */ newrc->isParent = false; *** a/src/backend/optimizer/prep/prepsecurity.c --- b/src/backend/optimizer/prep/prepsecurity.c *************** *** 229,257 **** expand_security_qual(PlannerInfo *root, List *tlist, int rt_index, rc = get_plan_rowmark(root->rowMarks, rt_index); if (rc != NULL) { ! switch (rc->markType) ! { ! case ROW_MARK_EXCLUSIVE: ! applyLockingClause(subquery, 1, LCS_FORUPDATE, ! rc->waitPolicy, false); ! break; ! case ROW_MARK_NOKEYEXCLUSIVE: ! applyLockingClause(subquery, 1, LCS_FORNOKEYUPDATE, ! rc->waitPolicy, false); ! break; ! case ROW_MARK_SHARE: ! applyLockingClause(subquery, 1, LCS_FORSHARE, ! rc->waitPolicy, false); ! break; ! case ROW_MARK_KEYSHARE: ! applyLockingClause(subquery, 1, LCS_FORKEYSHARE, ! rc->waitPolicy, false); ! break; ! case ROW_MARK_REFERENCE: ! case ROW_MARK_COPY: ! /* No locking needed */ ! break; ! } root->rowMarks = list_delete(root->rowMarks, rc); } --- 229,237 ---- rc = get_plan_rowmark(root->rowMarks, rt_index); if (rc != NULL) { ! if (rc->strength != LCS_NOLOCK) ! applyLockingClause(subquery, 1, ! rc->strength, rc->waitPolicy, false); root->rowMarks = list_delete(root->rowMarks, rc); } *** a/src/backend/optimizer/prep/preptlist.c --- b/src/backend/optimizer/prep/preptlist.c *************** *** 92,98 **** preprocess_targetlist(PlannerInfo *root, List *tlist) if (rc->rti != rc->prti) continue; ! if (rc->markType != ROW_MARK_COPY) { /* It's a regular table, so fetch its TID */ var = makeVar(rc->rti, --- 92,98 ---- if (rc->rti != rc->prti) continue; ! if (rc->markType & ~ROW_MARK_COPY) { /* It's a regular table, so fetch its TID */ var = makeVar(rc->rti, *************** *** 123,128 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 123,144 ---- pstrdup(resname), true); tlist = lappend(tlist, tle); + + /* if containing foreign tables, fetch the whole row too */ + if (rc->markType & ROW_MARK_COPY) + { + /* 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; + RowMarkType alltypes; 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; + alltypes = 0; 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,1396 **** 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); } --- 1391,1404 ---- newrc->rti = childRTindex; newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; ! if (childrte->relkind != RELKIND_FOREIGN_TABLE) ! newrc->markType = oldrc->markType; ! else ! newrc->markType = ROW_MARK_COPY; ! newrc->strength = oldrc->strength; newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; + alltypes |= newrc->markType; root->rowMarks = lappend(root->rowMarks, newrc); } *************** *** 1416,1421 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1424,1433 ---- /* Otherwise, OK to add to root->append_rel_list */ root->append_rel_list = list_concat(root->append_rel_list, appinfos); + + /* and replace the parent markType with alltypes */ + if (oldrc) + oldrc->markType = alltypes; } /* *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 4363,4394 **** 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; } ; --- 4363,4394 ---- 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/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 2352,2373 **** CreateCommandTag(Node *parsetree) else if (stmt->rowMarks != NIL) { /* not 100% but probably close enough */ ! switch (((PlanRowMark *) linitial(stmt->rowMarks))->markType) { ! case ROW_MARK_EXCLUSIVE: tag = "SELECT FOR UPDATE"; break; ! case ROW_MARK_NOKEYEXCLUSIVE: tag = "SELECT FOR NO KEY UPDATE"; break; ! case ROW_MARK_SHARE: tag = "SELECT FOR SHARE"; break; ! case ROW_MARK_KEYSHARE: tag = "SELECT FOR KEY SHARE"; break; ! case ROW_MARK_REFERENCE: ! case ROW_MARK_COPY: tag = "SELECT"; break; default: --- 2352,2372 ---- else if (stmt->rowMarks != NIL) { /* not 100% but probably close enough */ ! switch (((PlanRowMark *) linitial(stmt->rowMarks))->strength) { ! case LCS_FORUPDATE: tag = "SELECT FOR UPDATE"; break; ! case LCS_FORNOKEYUPDATE: tag = "SELECT FOR NO KEY UPDATE"; break; ! case LCS_FORSHARE: tag = "SELECT FOR SHARE"; break; ! case LCS_FORKEYSHARE: tag = "SELECT FOR KEY SHARE"; break; ! case LCS_NOLOCK: tag = "SELECT"; break; default: *** a/src/backend/utils/cache/plancache.c --- b/src/backend/utils/cache/plancache.c *************** *** 1488,1493 **** AcquireExecutorLocks(List *stmt_list, bool acquire) --- 1488,1497 ---- if (rte->rtekind != RTE_RELATION) continue; + /* Ignore inheritance parents. */ + if (rte->inh) + continue; + /* * Acquire the appropriate type of lock on each relation OID. Note * that we don't actually try to open the rel, and hence will not *** a/src/include/commands/explain.h --- b/src/include/commands/explain.h *************** *** 92,95 **** extern void ExplainPropertyLong(const char *qlabel, long value, --- 92,100 ---- extern void ExplainPropertyFloat(const char *qlabel, double value, int ndigits, ExplainState *es); + extern void ExplainBeginInheritedForeignModify(ModifyTable *plan, int subplan_index, + ExplainState *es); + extern void ExplainEndInheritedForeignModify(ModifyTable *plan, int subplan_index, + ExplainState *es); + #endif /* EXPLAIN_H */ *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** *** 428,434 **** typedef struct ExecRowMark Index rti; /* its range table index */ Index prti; /* parent range table index, if child */ Index rowmarkId; /* unique identifier for resjunk columns */ ! RowMarkType markType; /* see enum in nodes/plannodes.h */ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */ ItemPointerData curCtid; /* ctid of currently locked tuple, if any */ } ExecRowMark; --- 428,434 ---- Index rti; /* its range table index */ Index prti; /* parent range table index, if child */ Index rowmarkId; /* unique identifier for resjunk columns */ ! RowMarkType markType; /* see flags in nodes/plannodes.h */ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */ ItemPointerData curCtid; /* ctid of currently locked tuple, if any */ } ExecRowMark; *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 23,28 **** --- 23,29 ---- #include "nodes/bitmapset.h" #include "nodes/primnodes.h" #include "nodes/value.h" + #include "utils/lockclausestrength.h" #include "utils/lockwaitpolicy.h" /* Possible sources of a Query */ *************** *** 618,632 **** typedef struct DefElem * a location field --- currently, parse analysis insists on unqualified * names in LockingClause.) */ - typedef enum LockClauseStrength - { - /* order is important -- see applyLockingClause */ - LCS_FORKEYSHARE, - LCS_FORSHARE, - LCS_FORNOKEYUPDATE, - LCS_FORUPDATE - } LockClauseStrength; - typedef struct LockingClause { NodeTag type; --- 619,624 ---- *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** *** 18,23 **** --- 18,24 ---- #include "lib/stringinfo.h" #include "nodes/bitmapset.h" #include "nodes/primnodes.h" + #include "utils/lockclausestrength.h" #include "utils/lockwaitpolicy.h" *************** *** 785,791 **** typedef struct Limit /* * RowMarkType - ! * enums for types of row-marking operations * * The first four of these values represent different lock strengths that * we can take on tuples according to SELECT FOR [KEY] UPDATE/SHARE requests. --- 786,792 ---- /* * RowMarkType - ! * flags for types of row-marking operations * * The first four of these values represent different lock strengths that * we can take on tuples according to SELECT FOR [KEY] UPDATE/SHARE requests. *************** *** 814,828 **** typedef struct Limit * it's probably still faster than doing a second remote fetch, so it doesn't * seem worth the extra complexity to permit ROW_MARK_REFERENCE. */ ! typedef enum RowMarkType ! { ! ROW_MARK_EXCLUSIVE, /* obtain exclusive tuple lock */ ! ROW_MARK_NOKEYEXCLUSIVE, /* obtain no-key exclusive tuple lock */ ! ROW_MARK_SHARE, /* obtain shared tuple lock */ ! ROW_MARK_KEYSHARE, /* obtain keyshare tuple lock */ ! ROW_MARK_REFERENCE, /* just fetch the TID */ ! ROW_MARK_COPY /* physically copy the row value */ ! } RowMarkType; #define RowMarkRequiresRowShareLock(marktype) ((marktype) <= ROW_MARK_KEYSHARE) --- 815,828 ---- * it's probably still faster than doing a second remote fetch, so it doesn't * seem worth the extra complexity to permit ROW_MARK_REFERENCE. */ ! typedef uint16 RowMarkType; /* a bitmask of row-marking operation bits */ ! ! #define ROW_MARK_EXCLUSIVE 0x0001 /* obtain exclusive tuple lock */ ! #define ROW_MARK_NOKEYEXCLUSIVE 0x0002 /* obtain no-key exclusive tuple lock */ ! #define ROW_MARK_SHARE 0x0004 /* obtain shared tuple lock */ ! #define ROW_MARK_KEYSHARE 0x0008 /* obtain keyshare tuple lock */ ! #define ROW_MARK_REFERENCE 0x0010 /* just fetch the TID */ ! #define ROW_MARK_COPY 0x0020 /* physically copy the row value */ #define RowMarkRequiresRowShareLock(marktype) ((marktype) <= ROW_MARK_KEYSHARE) *************** *** 851,856 **** typedef enum RowMarkType --- 851,858 ---- * 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 *************** *** 859,864 **** typedef enum RowMarkType --- 861,870 ---- * Note this means that all tables in an inheritance hierarchy share the * same resjunk column names. However, in an inherited UPDATE/DELETE the * columns could have different physical column numbers in each subplan. + * + * A parent PlanRowMark's markType represents the logical OR of the rowmark + * methods being used by all its children. So, don't use the + * RowMarkRequiresRowShareLock macro with parent PlanRowMarks. */ typedef struct PlanRowMark { *************** *** 866,872 **** typedef struct PlanRowMark Index rti; /* range table index of markable relation */ Index prti; /* range table index of parent relation */ Index rowmarkId; /* unique identifier for resjunk columns */ ! RowMarkType markType; /* see enum above */ LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */ bool isParent; /* true if this is a "dummy" parent entry */ } PlanRowMark; --- 872,879 ---- Index rti; /* range table index of markable relation */ Index prti; /* range table index of parent relation */ Index rowmarkId; /* unique identifier for resjunk columns */ ! RowMarkType markType; /* see flags above */ ! LockClauseStrength strength; LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */ bool isParent; /* true if this is a "dummy" parent entry */ } PlanRowMark; *** /dev/null --- b/src/include/utils/lockclausestrength.h *************** *** 0 **** --- 1,32 ---- + /*------------------------------------------------------------------------- + * + * lockclausestrength.h + * Header file for LockClauseStrength enum. + * + * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/utils/lockclausestrength.h + * + *------------------------------------------------------------------------- + */ + #ifndef LOCKCLAUSESTRENGTH_H + #define LOCKCLAUSESTRENGTH_H + + /* + * This enum represents the locking strength of a FOR UPDATE/SHARE clause. + * The ordering here is important, because the highest numerical value takes + * precedence when a RTE is specified multiple ways. See applyLockingClause. + */ + typedef enum LockClauseStrength + { + /* order is important -- see applyLockingClause */ + LCS_NOLOCK, /* No locking needed (this is only used in + * PlanRowMarks.) */ + LCS_FORKEYSHARE, + LCS_FORSHARE, + LCS_FORNOKEYUPDATE, + LCS_FORUPDATE + } LockClauseStrength; + + #endif /* LOCKCLAUSESTRENGTH_H */ *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *************** *** 1234,1239 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; --- 1234,1644 ---- 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 *************** *** 536,541 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; --- 536,670 ---- 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 --------------000700000201020000050307 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 --------------000700000201020000050307--