Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XmiFL-0006Xn-JO for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Nov 2014 12:02:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XmiFL-0000Tu-1J for pgsql-hackers@arkaria.postgresql.org; Fri, 07 Nov 2014 12:02:31 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XmiFD-0000TR-PP for pgsql-hackers@postgresql.org; Fri, 07 Nov 2014 12:02:28 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XmiF1-0000OK-HN for pgsql-hackers@postgresql.org; Fri, 07 Nov 2014 12:02:20 +0000 Received: from mfs5.rdh.ecl.ntt.co.jp (mfs5.rdh.ecl.ntt.co.jp [129.60.39.144]) by tama50.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id sA7C24jk024611; Fri, 7 Nov 2014 21:02:04 +0900 Received: from mfs5.rdh.ecl.ntt.co.jp (localhost.localdomain [127.0.0.1]) by mfs5.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id 5C598E00C4; Fri, 7 Nov 2014 21:02:04 +0900 (JST) Received: from imail3.m.ecl.ntt.co.jp (imail3.m.ecl.ntt.co.jp [129.60.5.248]) by mfs5.rdh.ecl.ntt.co.jp (Postfix) with ESMTP id 4F811E00C3; Fri, 7 Nov 2014 21:02:04 +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 sA7C23UD003791; Fri, 7 Nov 2014 21:02:04 +0900 Message-ID: <545CB4A2.1030303@lab.ntt.co.jp> Date: Fri, 07 Nov 2014 21:01:38 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.0; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: Kyotaro HORIGUCHI CC: hlinnakangas@vmware.com, noah@leadboat.com, shigeru.hanada@gmail.com, pgsql-hackers@postgresql.org 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> In-Reply-To: <20141107.145713.169337282.horiguchi.kyotaro@lab.ntt.co.jp> Content-Type: multipart/mixed; boundary="------------020304010706070001040506" X-TM-AS-MML: disable X-Pg-Spam-Score: -2.5 (--) 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. --------------020304010706070001040506 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit (2014/11/07 14:57), Kyotaro HORIGUCHI wrote: >>>> Here are separated patches. >>>> >>>> fdw-chk.patch - CHECK constraints on foreign tables >>>> fdw-inh.patch - table inheritance with foreign tables >>>> >>>> The latter has been created on top of [1]. >>> >>>> [1] >>>> http://www.postgresql.org/message-id/540DA168.3040407@lab.ntt.co.jp >> >>> To be exact, it has been created on top of [1] and fdw-chk.patch. > > I tried both patches on the current head, the newly added > parameter to analyze_rel() hampered them from applying but it is > easy to fix seemingly and almost all the other part was applied > cleanly. Thanks for the review! > By the way, are these the result of simply splitting of your last > patch, foreign_inherit-v15.patch? > > http://www.postgresql.org/message-id/53FEEF94.6040101@lab.ntt.co.jp The answer is "no". > The result of apllying whole-in-one version and this splitted > version seem to have many differences. Did you added even other > changes? Or do I understand this patch wrongly? As I said before, I splitted the whole-in-one version into three: 1) CHECK constraint patch (ie fdw-chk.patch), 2) table inheritance patch (ie fdw-inh.patch) and 3) path reparameterization patch (not posted). In addition to that, I slightly modified #1 and #2. IIUC, #3 would be useful not only for the inheritance cases but for union all cases. So, I plan to propose it independently in the next CF. >>> I noticed that the latter disallows TRUNCATE on inheritance trees that >>> contain at least one child foreign table. But I think it would be >>> better to allow it, with the semantics that we quietly ignore the >>> child >>> foreign tables and apply the operation to the child plain tables, >>> which >>> is the same semantics as ALTER COLUMN SET STORAGE on such inheritance >>> trees. Comments welcome. >> >> Done. And I've also a bit revised regression tests for both >> patches. Patches attached. I rebased the patches to the latest head. Here are updated patches. Other changes: * fdw-chk-3.patch: the updated patch revises some ereport messages a little bit. * fdw-inh-3.patch: I noticed that there is a doc bug in the previous patch. The updated patch fixes that, adds a bit more docs, and revises regression tests in foreign_data.sql a bit further. Thanks, Best regards, Etsuro Fujita --------------020304010706070001040506 Content-Type: text/x-patch; name="fdw-inh-3.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-inh-3.patch" *** a/contrib/file_fdw/input/file_fdw.source --- b/contrib/file_fdw/input/file_fdw.source *************** *** 144,149 **** SET constraint_exclusion = 'partition'; --- 144,166 ---- \t off ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_csv INHERIT agg; + ALTER FOREIGN TABLE agg_text INHERIT agg; + SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b; + SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b; + SELECT * FROM agg_csv WHERE b < 10.0 ORDER BY a, b; + SELECT * FROM agg_text WHERE b < 10.0 ORDER BY a, b; + -- updates aren't supported + UPDATE agg SET a = 1; + DELETE FROM agg WHERE a = 100; + -- but this should be ignored + SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE; + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + ALTER FOREIGN TABLE agg_text 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 *************** *** 237,242 **** EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; --- 237,289 ---- SET constraint_exclusion = 'partition'; \t off ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_csv INHERIT agg; + ALTER FOREIGN TABLE agg_text INHERIT agg; + SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b; + a | b + ----+--------- + 0 | 0.09561 + 0 | 0.09561 + 56 | 7.8 + (3 rows) + + SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b; + a | b + ---+--- + (0 rows) + + SELECT * FROM agg_csv WHERE b < 10.0 ORDER BY a, b; + a | b + ---+--------- + 0 | 0.09561 + (1 row) + + SELECT * FROM agg_text WHERE b < 10.0 ORDER BY a, b; + a | b + ----+--------- + 0 | 0.09561 + 56 | 7.8 + (2 rows) + + -- updates aren't supported + UPDATE agg SET a = 1; + ERROR: cannot update foreign table "agg_text" + DELETE FROM agg WHERE a = 100; + ERROR: cannot delete from foreign table "agg_text" + -- but this should be ignored + SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE; + a | b + ----+--------- + 0 | 0.09561 + 0 | 0.09561 + 56 | 7.8 + (3 rows) + + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + ALTER FOREIGN TABLE agg_text 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 *************** *** 2863,2868 **** NOTICE: NEW: (13,"test triggered !") --- 2863,3376 ---- (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 + -> 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 + (34 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 + -> 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 + (42 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 *************** *** 823,828 **** postgresGetForeignPlan(PlannerInfo *root, --- 823,839 ---- { RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid); + if (rc == NULL) + { + PlanRowMark *prm = get_plan_rowmark(root->rowMarks, baserel->relid); + + if (prm) + { + if (prm->rti != prm->prti) + rc = get_parse_rowmark(root->parse, prm->prti); + } + } + if (rc) { /* *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 626,631 **** UPDATE rem1 SET f2 = 'testo'; --- 626,782 ---- 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,2527 ---- 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 that apply during + 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. --- 2669,2678 ---- 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. --- 2735,2742 ---- ! We will refer to the child tables as partitions, though we assume ! 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'] [, ... ]) *************** *** 190,195 **** 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 *************** *** 380,385 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + parent_table + + + A parent table to associate or de-associate with this foreign table. + The parent table must be an ordinary table. + + + + + new_owner *** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** *** 1010,1015 **** ALTER TABLE ALL IN TABLESPACE name --- 1010,1028 ---- + A recursive SET STORAGE operation will make + the storage mode unchanged for any column of descendant tables + that are foreign. + + + + A recursive SET WITH OIDS operation will be + rejected if any of descendant tables is foreign, since it is + not permitted to add an oid system column to + foreign tables. + + + The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to descendant tables; that is, they always act as though ONLY were specified. *************** *** 1018,1023 **** ALTER TABLE ALL IN TABLESPACE name --- 1031,1044 ---- + When adding a CHECK constraint with the NOT VALID + option recursively, the inherited constraint on a descendant + table that is foreign will be marked valid without checking consistency + with the foreign server. It is the user's resposibility to ensure that + the CHECK constraint matches the foreign server. + + + 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 the foreign table's wrapper does not support + ANALYZE, the command prints a warning and does nothing. + + + If the table being analyzed is completely empty, ANALYZE will not record new statistics for that table. Any existing statistics will be retained. *** a/doc/src/sgml/ref/create_foreign_table.sgml --- b/doc/src/sgml/ref/create_foreign_table.sgml *************** *** 23,28 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 23,29 ---- | table_constraint } [, ... ] ] ) + [ INHERITS ( parent_table [, ... ] ) ] SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ] *************** *** 121,126 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 122,147 ---- + INHERITS ( parent_table [, ... ] ) + + + The optional INHERITS clause specifies a list of + tables from which the new foreign table automatically inherits + all columns. + See the similar form of + for more details. + + + + Note that unlike CREATE TABLE, column + STORAGE settings are not copied from parent tables, + resulting in the copied columns in the new foreign table having + type-specific default settings. + + + + + NOT NULL *************** *** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 208,225 ---- + 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 the + details of table inheritance. + + + + + server_name *************** *** 223,228 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 256,270 ---- Those definitions simply declare the constraints hold for all rows in the foreign tables. It is the user's responsibility to ensure that those definitions match the remote side. + Those constraints are used in some kind of query optimization such + as constraint exclusion for partitioned tables (see + ). + + + + Since it is not permitted to add an oid system column to + foreign tables, the command will be rejected if any of parent tables + has an oid system column. *** a/doc/src/sgml/ref/truncate.sgml --- b/doc/src/sgml/ref/truncate.sgml *************** *** 179,184 **** TRUNCATE [ TABLE ] [ ONLY ] name [ --- 179,189 ---- This is similar to the usual behavior of currval() after a failed transaction. + + + A recursive TRUNCATE ignores descendant tables that + are foreign (if any), although it locks these tables exclusively. + *** a/src/backend/commands/analyze.c --- b/src/backend/commands/analyze.c *************** *** 82,87 **** int default_statistics_target = 100; --- 82,88 ---- /* A few variables that don't seem worth passing around as parameters */ static MemoryContext anl_context = NULL; + static VacuumMode vac_mode; static BufferAccessStrategy vac_strategy; *************** *** 102,107 **** static int acquire_sample_rows(Relation onerel, int elevel, --- 103,109 ---- HeapTuple *rows, int targrows, double *totalrows, double *totaldeadrows); static int compare_rows(const void *a, const void *b); + static bool has_foreign(Oid parentrelId, List *tableOIDs); static int acquire_inherited_sample_rows(Relation onerel, int elevel, HeapTuple *rows, int targrows, double *totalrows, double *totaldeadrows); *************** *** 115,121 **** static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull); * analyze_rel() -- analyze one relation */ void ! analyze_rel(Oid relid, VacuumStmt *vacstmt, bool in_outer_xact, BufferAccessStrategy bstrategy) { Relation onerel; --- 117,123 ---- * analyze_rel() -- analyze one relation */ void ! analyze_rel(Oid relid, VacuumStmt *vacstmt, VacuumMode vacmode, bool in_outer_xact, BufferAccessStrategy bstrategy) { Relation onerel; *************** *** 130,135 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, --- 132,138 ---- elevel = DEBUG2; /* Set up static variables */ + vac_mode = vacmode; 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, --- 300,307 ---- * 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, --- 1446,1492 ---- /* + * Check wether to contain at least one foreign table + */ + static bool + has_foreign(Oid parentrelId, List *tableOIDs) + { + bool result; + ListCell *lc; + + /* There are no children */ + if (list_length(tableOIDs) < 2) + return false; + + result = false; + foreach(lc, tableOIDs) + { + Oid childOID = lfirst_oid(lc); + Relation childrel; + + /* Parent should not be foreign */ + if (childOID == parentrelId) + continue; + + /* We already got the needed lock */ + childrel = heap_open(childOID, NoLock); + if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + { + /* Found it */ + result = true; + } + heap_close(childrel, NoLock); + } + return result; + } + + /* * 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 there are ! * inheritance children that foreign tables. */ static int acquire_inherited_sample_rows(Relation onerel, int elevel, *************** *** 1457,1462 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1495,1501 ---- { List *tableOIDs; Relation *rels; + AcquireSampleRowsFunc *acquirefunc; double *relblocks; double totalblocks; int numrows, *************** *** 1487,1496 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1526,1556 ---- } /* + * If we are not in VAC_MODE_SINGLE case and the inheritance set contains + * at least one foreign table, then fail. + */ + if (vac_mode != VAC_MODE_SINGLE) + { + if (has_foreign(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; *************** *** 1512,1518 **** acquire_inherited_sample_rows(Relation onerel, int elevel, } rels[nrels] = childrel; ! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel); totalblocks += relblocks[nrels]; nrels++; } --- 1572,1607 ---- } 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++; } *************** *** 1530,1535 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1619,1625 ---- { Relation childrel = rels[i]; double childblocks = relblocks[i]; + AcquireSampleRowsFunc childacquirefunc = acquirefunc[i]; if (childblocks > 0) { *************** *** 1545,1556 **** 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 && --- 1635,1646 ---- 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/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 316,322 **** 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, --- 316,323 ---- 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, *************** *** 567,572 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) --- 568,597 ---- 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 *************** *** 1024,1029 **** ExecuteTruncate(TruncateStmt *stmt) --- 1049,1060 ---- /* 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); *************** *** 3091,3114 **** 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; --- 3122,3149 ---- * 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; *************** *** 3121,3127 **** 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; --- 3156,3163 ---- 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; *************** *** 3243,3253 **** 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; --- 3279,3295 ---- 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; *************** *** 3281,3287 **** 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: --- 3323,3328 ---- *************** *** 4275,4281 **** 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 --- 4316,4323 ---- */ 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 *************** *** 4303,4310 **** 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); } } --- 4345,4356 ---- 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); } } *************** *** 4594,4600 **** 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); --- 4640,4646 ---- /* 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); *************** *** 4890,4895 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, --- 4936,4946 ---- /* 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 */ *************** *** 5490,5496 **** 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 --- 5541,5547 ---- /* 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 *************** *** 5882,5888 **** 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 --- 5933,5939 ---- /* 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 *************** *** 5893,5901 **** 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 */ --- 5944,5960 ---- * 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 */ *************** *** 7382,7388 **** 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); --- 7441,7447 ---- /* 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); *************** *** 7717,7723 **** 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, --- 7776,7785 ---- * 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/commands/vacuum.c --- b/src/backend/commands/vacuum.c *************** *** 61,66 **** int vacuum_multixact_freeze_table_age; --- 61,67 ---- /* A few variables that don't seem worth passing around as parameters */ static MemoryContext vac_context = NULL; + static VacuumMode vac_mode; static BufferAccessStrategy vac_strategy; *************** *** 149,154 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, --- 150,169 ---- ALLOCSET_DEFAULT_MAXSIZE); /* + * Identify vacuum mode. If relid is not InvalidOid, the caller should be + * an autovacuum worker. See the above comments. + */ + if (relid != InvalidOid) + vac_mode = VAC_MODE_AUTOVACUUM; + else + { + if (!vacstmt->relation) + vac_mode = VAC_MODE_ALL; + else + vac_mode = VAC_MODE_SINGLE; + } + + /* * If caller didn't give us a buffer strategy object, make one in the * cross-transaction memory context. */ *************** *** 253,259 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, PushActiveSnapshot(GetTransactionSnapshot()); } ! analyze_rel(relid, vacstmt, in_outer_xact, vac_strategy); if (use_own_xacts) { --- 268,275 ---- PushActiveSnapshot(GetTransactionSnapshot()); } ! analyze_rel(relid, vacstmt, vac_mode, in_outer_xact, ! vac_strategy); if (use_own_xacts) { *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *************** *** 2308,2313 **** EvalPlanQualFetchRowMarks(EPQState *epqstate) --- 2308,2333 ---- Assert(erm->markType == ROW_MARK_COPY); + /* if child rel, must check whether it produced this row */ + if (erm->rti != erm->prti) + { + Oid tableoid; + + datum = ExecGetJunkAttribute(epqstate->origslot, + aerm->toidAttNo, + &isNull); + /* non-locked rels could be on the inside of outer joins */ + if (isNull) + continue; + tableoid = DatumGetObjectId(datum); + + if (tableoid != RelationGetRelid(erm->relation)) + { + /* this child is inactive right now */ + continue; + } + } + /* fetch the whole-row Var for the relation */ datum = ExecGetJunkAttribute(epqstate->origslot, aerm->wholeAttNo, *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 1994,1999 **** _copyRangeTblEntry(const RangeTblEntry *from) --- 1994,2000 ---- COPY_NODE_FIELD(eref); COPY_SCALAR_FIELD(lateral); COPY_SCALAR_FIELD(inh); + COPY_SCALAR_FIELD(hasForeign); COPY_SCALAR_FIELD(inFromCl); COPY_SCALAR_FIELD(requiredPerms); COPY_SCALAR_FIELD(checkAsUser); *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 2342,2347 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) --- 2342,2348 ---- COMPARE_NODE_FIELD(eref); COMPARE_SCALAR_FIELD(lateral); COMPARE_SCALAR_FIELD(inh); + COMPARE_SCALAR_FIELD(hasForeign); COMPARE_SCALAR_FIELD(inFromCl); COMPARE_SCALAR_FIELD(requiredPerms); COMPARE_SCALAR_FIELD(checkAsUser); *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 2423,2428 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) --- 2423,2429 ---- WRITE_BOOL_FIELD(lateral); WRITE_BOOL_FIELD(inh); + WRITE_BOOL_FIELD(hasForeign); WRITE_BOOL_FIELD(inFromCl); WRITE_UINT_FIELD(requiredPerms); WRITE_OID_FIELD(checkAsUser); *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** *** 1249,1254 **** _readRangeTblEntry(void) --- 1249,1255 ---- READ_BOOL_FIELD(lateral); READ_BOOL_FIELD(inh); + READ_BOOL_FIELD(hasForeign); READ_BOOL_FIELD(inFromCl); READ_UINT_FIELD(requiredPerms); READ_OID_FIELD(checkAsUser); *** a/src/backend/optimizer/prep/preptlist.c --- b/src/backend/optimizer/prep/preptlist.c *************** *** 111,116 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 111,118 ---- /* if parent of inheritance tree, need the tableoid too */ if (rc->isParent) { + RangeTblEntry *rte = rt_fetch(rc->rti, parse->rtable); + var = makeVar(rc->rti, TableOidAttributeNumber, OIDOID, *************** *** 123,128 **** preprocess_targetlist(PlannerInfo *root, List *tlist) --- 125,146 ---- pstrdup(resname), true); tlist = lappend(tlist, tle); + + /* if including foreign tables, fetch the whole row too */ + if (rte->hasForeign) + { + /* Not a table, so we need the whole row as a junk var */ + var = makeWholeRowVar(rt_fetch(rc->rti, range_table), + rc->rti, + 0, + false); + snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId); + tle = makeTargetEntry((Expr *) var, + list_length(tlist) + 1, + pstrdup(resname), + true); + tlist = lappend(tlist, tle); + } } } else *** a/src/backend/optimizer/prep/prepunion.c --- b/src/backend/optimizer/prep/prepunion.c *************** *** 1237,1242 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1237,1243 ---- LOCKMODE lockmode; List *inhOIDs; List *appinfos; + bool hasForeign; 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; + hasForeign = false; foreach(l, inhOIDs) { Oid childOID = lfirst_oid(l); *************** *** 1338,1348 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID, ! * and set inh = false. Also, set requiredPerms to zero since all ! * required permissions checks are done on the original RTE. */ childrte = copyObject(rte); childrte->relid = childOID; childrte->inh = false; childrte->requiredPerms = 0; parse->rtable = lappend(parse->rtable, childrte); --- 1340,1351 ---- /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID, ! * relkind and set inh = false. Also, set requiredPerms to zero since ! * all required permissions checks are done on the original RTE. */ childrte = copyObject(rte); childrte->relid = childOID; + childrte->relkind = newrelation->rd_rel->relkind; childrte->inh = false; childrte->requiredPerms = 0; parse->rtable = lappend(parse->rtable, childrte); *************** *** 1388,1400 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->rti = childRTindex; newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; ! newrc->markType = oldrc->markType; newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; root->rowMarks = lappend(root->rowMarks, newrc); } /* Close child relations, but keep locks */ if (childOID != parentOID) heap_close(newrelation, NoLock); --- 1391,1409 ---- newrc->rti = childRTindex; newrc->prti = rti; newrc->rowmarkId = oldrc->rowmarkId; ! if (childrte->relkind == RELKIND_FOREIGN_TABLE) ! newrc->markType = ROW_MARK_COPY; ! else ! newrc->markType = oldrc->markType; newrc->waitPolicy = oldrc->waitPolicy; newrc->isParent = false; root->rowMarks = lappend(root->rowMarks, newrc); } + if (childrte->relkind == RELKIND_FOREIGN_TABLE) + hasForeign = true; + /* Close child relations, but keep locks */ if (childOID != parentOID) heap_close(newrelation, NoLock); *************** *** 1416,1421 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) --- 1425,1434 ---- /* Otherwise, OK to add to root->append_rel_list */ root->append_rel_list = list_concat(root->append_rel_list, appinfos); + + /* And mark the parent table as having children that are foreign, if so */ + if (hasForeign) + rte->hasForeign = true; } /* *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 4335,4366 **** AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o CreateForeignTableStmt: CREATE FOREIGN TABLE qualified_name '(' OptTableElementList ')' ! SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $4->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $4; n->base.tableElts = $6; ! n->base.inhRelations = NIL; n->base.if_not_exists = false; /* FDW-specific data */ ! n->servername = $9; ! n->options = $10; $$ = (Node *) n; } | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name '(' OptTableElementList ')' ! SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $7->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $7; n->base.tableElts = $9; ! n->base.inhRelations = NIL; n->base.if_not_exists = true; /* FDW-specific data */ ! n->servername = $12; ! n->options = $13; $$ = (Node *) n; } ; --- 4335,4366 ---- CreateForeignTableStmt: CREATE FOREIGN TABLE qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $4->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $4; n->base.tableElts = $6; ! n->base.inhRelations = $8; n->base.if_not_exists = false; /* FDW-specific data */ ! n->servername = $10; ! n->options = $11; $$ = (Node *) n; } | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $7->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $7; n->base.tableElts = $9; ! n->base.inhRelations = $11; n->base.if_not_exists = true; /* FDW-specific data */ ! n->servername = $13; ! n->options = $14; $$ = (Node *) n; } ; *** a/src/include/commands/vacuum.h --- b/src/include/commands/vacuum.h *************** *** 140,145 **** extern int vacuum_multixact_freeze_min_age; --- 140,154 ---- extern int vacuum_multixact_freeze_table_age; + /* Possible modes for vacuum() */ + typedef enum + { + VAC_MODE_ALL, /* Vacuum/analyze all relations */ + VAC_MODE_SINGLE, /* Vacuum/analyze a specific relation */ + VAC_MODE_AUTOVACUUM /* Autovacuum worker */ + } VacuumMode; + + /* in commands/vacuum.c */ extern void vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel); *************** *** 175,181 **** extern void lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy); /* in commands/analyze.c */ ! extern void analyze_rel(Oid relid, VacuumStmt *vacstmt, bool in_outer_xact, BufferAccessStrategy bstrategy); extern bool std_typanalyze(VacAttrStats *stats); extern double anl_random_fract(void); --- 184,190 ---- BufferAccessStrategy bstrategy); /* in commands/analyze.c */ ! extern void analyze_rel(Oid relid, VacuumStmt *vacstmt, VacuumMode vacmode, bool in_outer_xact, BufferAccessStrategy bstrategy); extern bool std_typanalyze(VacAttrStats *stats); extern double anl_random_fract(void); *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 812,817 **** typedef struct RangeTblEntry --- 812,818 ---- Alias *eref; /* expanded reference names */ bool lateral; /* subquery, function, or values is LATERAL? */ bool inh; /* inheritance requested? */ + bool hasForeign; /* does inheritance include foreign tables? */ bool inFromCl; /* present in FROM clause? */ AclMode requiredPerms; /* bitmask of required access permissions */ Oid checkAsUser; /* if valid, check access as this role */ *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** *** 819,824 **** typedef enum RowMarkType --- 819,826 ---- * The tableoid column is only present for an inheritance hierarchy. * When markType == ROW_MARK_COPY, there is instead a single column named * wholerow%u whole-row value of relation + * The wholerow column is also present for an inheritance hierarchy that + * contains foreign tables. * In all three cases, %u represents the rowmark ID number (rowmarkId). * This number is unique within a plan tree, except that child relation * entries copy their parent's rowmarkId. (Assigning unique numbers *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *************** *** 1207,1212 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; --- 1207,1554 ---- 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'); + ALTER FOREIGN TABLE ft2 NO INHERIT pt1; + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + ALTER FOREIGN TABLE ft2 INHERIT pt1; + 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; + \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 + + -- 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) + + 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 *************** *** 522,527 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; --- 522,650 ---- 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'); + ALTER FOREIGN TABLE ft2 NO INHERIT pt1; + DROP FOREIGN TABLE ft2; + CREATE FOREIGN TABLE ft2 ( + c1 integer NOT NULL, + c2 text, + c3 date + ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + ALTER FOREIGN TABLE ft2 INHERIT pt1; + CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date; + ALTER TABLE v1 INHERIT pt1; -- ERROR + DROP VIEW v1; + \d+ pt1 + \d+ ft2 + + -- 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; + 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 --------------020304010706070001040506 Content-Type: text/x-patch; name="fdw-chk-3.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="fdw-chk-3.patch" *** a/contrib/file_fdw/input/file_fdw.source --- b/contrib/file_fdw/input/file_fdw.source *************** *** 134,139 **** DELETE FROM agg_csv WHERE a = 100; --- 134,149 ---- -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; + -- constraint exclusion tests + ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0); + \t on + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + SET constraint_exclusion = 'partition'; + \t off + ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; + -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; *** a/contrib/file_fdw/output/file_fdw.source --- b/contrib/file_fdw/output/file_fdw.source *************** *** 219,224 **** SELECT * FROM agg_csv FOR UPDATE; --- 219,242 ---- 42 | 324.78 (3 rows) + -- constraint exclusion tests + ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0); + \t on + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + Foreign Scan on public.agg_csv + Output: a, b + Filter: (agg_csv.a < 0) + Foreign File: @abs_srcdir@/data/agg.csv + + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0; + Result + Output: a, b + One-Time Filter: false + + SET constraint_exclusion = 'partition'; + \t off + ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check; -- privilege tests SET ROLE file_fdw_superuser; SELECT * FROM agg_text ORDER BY a; *** a/contrib/postgres_fdw/expected/postgres_fdw.out --- b/contrib/postgres_fdw/expected/postgres_fdw.out *************** *** 2488,2493 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; --- 2488,2515 ---- (13 rows) -- =================================================================== + -- test constraint exclusion stuff + -- =================================================================== + ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + QUERY PLAN + ------------------------------------------------------------------------------------------ + Foreign Scan on public.ft1 + Output: c1, c2, c3, c4, c5, c6, c7, c8 + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c2 < 0)) + (3 rows) + + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + QUERY PLAN + ------------------------------------------ + Result + Output: c1, c2, c3, c4, c5, c6, c7, c8 + One-Time Filter: false + (3 rows) + + SET constraint_exclusion = 'partition'; + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 387,392 **** select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1; --- 387,401 ---- select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1; -- =================================================================== + -- test constraint exclusion stuff + -- =================================================================== + ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0); + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + SET constraint_exclusion = 'on'; + EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0; + SET constraint_exclusion = 'partition'; + + -- =================================================================== -- test serial columns (ie, sequence-based defaults) -- =================================================================== create table loc1 (f1 serial, f2 text); *** a/doc/src/sgml/ref/alter_foreign_table.sgml --- b/doc/src/sgml/ref/alter_foreign_table.sgml *************** *** 42,47 **** ALTER FOREIGN TABLE [ IF EXISTS ] namecolumn_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) + ADD table_constraint + DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name *************** *** 152,157 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + + ADD table_constraint + + + This form adds a new constraint to a table using the same syntax as + . + Unlike the case when adding a constraint to a regular table, nothing happens + to the underlying storage: this action simply declares that + some new constraint holds for all rows in the foreign table. + + + + + + DROP CONSTRAINT [ IF EXISTS ] + + + This form drops the specified constraint on a table. + If IF EXISTS is specified and the constraint + does not exist, no error is thrown. In this case a notice is issued instead. + + + + DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER *************** *** 284,289 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + + table_constraint + + + New table constraint for the table. + + + + + + constraint_name + + + Name of an existing constraint to drop. + + + + CASCADE *************** *** 365,374 **** ALTER FOREIGN TABLE [ IF EXISTS ] name Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or ! DROP COLUMN, a NOT NULL constraint is ! added, or a column type is changed with SET DATA TYPE. It is ! the user's responsibility to ensure that the table definition matches the ! remote side. --- 409,418 ---- Consistency with the foreign server is not checked when a column is added or removed with ADD COLUMN or ! DROP COLUMN, a NOT NULL constraint or ! CHECK constraint is added, or a column type is changed with ! SET DATA TYPE. It is the user's responsibility to ensure that ! the table definition matches the remote side. *** a/doc/src/sgml/ref/create_foreign_table.sgml --- b/doc/src/sgml/ref/create_foreign_table.sgml *************** *** 19,25 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] [, ... ] ] ) SERVER server_name --- 19,26 ---- CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] ! | table_constraint } [, ... ] ] ) SERVER server_name *************** *** 30,36 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 31,43 ---- [ CONSTRAINT constraint_name ] { NOT NULL | NULL | + CHECK ( expression ) | DEFAULT default_expr } + + and table_constraint is: + + [ CONSTRAINT constraint_name ] + { CHECK ( expression ) } *************** *** 138,143 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 145,171 ---- + CHECK ( expression ) + + + The CHECK clause specifies an expression producing a + Boolean result which each row must satisfy. + Expressions evaluating to TRUE or UNKNOWN succeed. + A check constraint specified as a column constraint should + reference that column's value only, while an expression + appearing in a table constraint can reference multiple columns. + + + + Currently, CHECK expressions cannot contain + subqueries nor refer to variables other than columns of the + current row. The system column tableoid + may be referenced, but not any other system column. + + + + + DEFAULT default_expr *************** *** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 215,230 ---- + + Notes + + + Constraints on foreign tables are not enforced on insert or update. + Those definitions simply declare the constraints hold for all rows + in the foreign tables. It is the user's responsibility to ensure + that those definitions match the remote side. + + Examples *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 2222,2227 **** AddRelationNewConstraints(Relation rel, --- 2222,2241 ---- if (cdef->contype != CONSTR_CHECK) continue; + /* Don't allow NO INHERIT for foreign tables */ + if (cdef->is_no_inherit && + rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CHECK constraints on foreign tables cannot be marked NO INHERIT"))); + + /* Don't allow NOT VALID for foreign tables */ + if (cdef->skip_validation && + rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("NOT VALID is not supported for CHECK constraints on foreign tables"))); + if (cdef->raw_expr != NULL) { Assert(cdef->cooked_expr == NULL); *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 478,487 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg("ON COMMIT can only be used on temporary tables"))); - if (stmt->constraints != NIL && relkind == RELKIND_FOREIGN_TABLE) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("constraints are not supported on foreign tables"))); /* * Look up the namespace in which we are supposed to create the relation, --- 478,483 ---- *************** *** 3143,3149 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_INDEX; break; case AT_AddConstraint: /* ADD CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) --- 3139,3145 ---- pass = AT_PASS_ADD_INDEX; break; case AT_AddConstraint: /* ADD CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) *************** *** 3157,3163 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = AT_PASS_ADD_CONSTR; break; case AT_DropConstraint: /* DROP CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) --- 3153,3159 ---- pass = AT_PASS_ADD_CONSTR; break; case AT_DropConstraint: /* DROP CONSTRAINT */ ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Recursion occurs during execution phase */ /* No command-specific prep needed except saving recurse flag */ if (recurse) *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** *** 515,526 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_CHECK: - if (cxt->isforeign) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), - parser_errposition(cxt->pstate, - constraint->location))); cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); break; --- 515,520 ---- *************** *** 529,535 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) --- 523,529 ---- if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("unique or primary key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) *************** *** 546,552 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); --- 540,546 ---- if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); *************** *** 605,614 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { ! if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); --- 599,612 ---- static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { ! if (cxt->isforeign && ! (constraint->contype == CONSTR_PRIMARY || ! constraint->contype == CONSTR_UNIQUE || ! constraint->contype == CONSTR_EXCLUSION || ! constraint->contype == CONSTR_FOREIGN)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("unique, primary key, exclusion, or foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); *** a/src/test/regress/expected/foreign_data.out --- b/src/test/regress/expected/foreign_data.out *************** *** 670,676 **** LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; --- 670,682 ---- ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT, ! c3 date ! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT ! CREATE FOREIGN TABLE ft1 ( ! c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; *************** *** 682,687 **** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; --- 688,695 ---- c1 | integer | not null | ("param 1" 'val1') | plain | | ft1.c1 c2 | text | | (param2 'val2', param3 'val3') | extended | | c3 | date | | | plain | | + Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') *************** *** 740,745 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; --- 748,755 ---- c8 | text | | (p2 'V2') | extended | | c9 | integer | | | plain | | c10 | integer | | (p1 'v1') | plain | | + Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) Server: s0 FDW Options: (delimiter ',', quote '"', "be quoted" 'value') *************** *** 748,763 **** CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type DROP TABLE use_ft1_column_type; ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR ! ERROR: constraints are not supported on foreign tables ! LINE 1: ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c... ! ^ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ! ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; ! ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; ! ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; --- 758,775 ---- ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type DROP TABLE use_ft1_column_type; ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NO INHERIT; -- ERROR ! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR ! ERROR: NOT VALID is not supported for CHECK constraints on foreign tables ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ! ERROR: constraint "no_const" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; ! NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; ! ERROR: constraint "ft1_c1_check" of relation "ft1" does not exist ! ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ERROR: "ft1" is not a table ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; *************** *** 785,790 **** ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; --- 797,804 ---- c7 | integer | | (p1 'v1', p2 'v2') c8 | text | | (p2 'V2') c10 | integer | | (p1 'v1') + Check constraints: + "ft1_c2_check" CHECK (c2 <> ''::text) Server: s0 FDW Options: (quote '~', "be quoted" 'value', escape '@') *** a/src/test/regress/sql/foreign_data.sql --- b/src/test/regress/sql/foreign_data.sql *************** *** 270,276 **** CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; --- 270,281 ---- CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT, ! c3 date ! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR ! CREATE FOREIGN TABLE ft1 ( ! c1 integer OPTIONS ("param 1" 'val1') NOT NULL, ! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''), c3 date ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; *************** *** 314,323 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1; CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR DROP TABLE use_ft1_column_type; ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); --- 319,331 ---- CREATE TABLE use_ft1_column_type (x ft1); ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR DROP TABLE use_ft1_column_type; ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NO INHERIT; -- ERROR ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR ! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const; ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check; + ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check; ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role; ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); --------------020304010706070001040506 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 --------------020304010706070001040506--