Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XMvZp-0006v8-5u for pgsql-hackers@arkaria.postgresql.org; Thu, 28 Aug 2014 09:01:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XMvZo-0007uh-J1 for pgsql-hackers@arkaria.postgresql.org; Thu, 28 Aug 2014 09:01:04 +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 1XMvZk-0007uZ-Tm for pgsql-hackers@postgresql.org; Thu, 28 Aug 2014 09:01:01 +0000 Received: from tama50.ecl.ntt.co.jp ([129.60.39.147]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XMvZW-0005A0-Qb for pgsql-hackers@postgresql.org; Thu, 28 Aug 2014 09:00:59 +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 s7S90PgW019749; Thu, 28 Aug 2014 18:00:25 +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 22CD8E0121; Thu, 28 Aug 2014 18:00:25 +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 14331E0119; Thu, 28 Aug 2014 18:00:25 +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 s7S90OBJ027960; Thu, 28 Aug 2014 18:00:24 +0900 Message-ID: <53FEEF94.6040101@lab.ntt.co.jp> Date: Thu, 28 Aug 2014 18:00:04 +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: Noah Misch CC: shigeru.hanada@gmail.com, Kyotaro HORIGUCHI , pgsql-hackers@postgresql.org Subject: Re: inherit support for foreign tables References: <53393E18.3080901@lab.ntt.co.jp> <533C01C7.6030809@lab.ntt.co.jp> <53706FB6.6000403@lab.ntt.co.jp> <20140620.170406.123403564.horiguchi.kyotaro@lab.ntt.co.jp> <20140702022327.GC1586927@tornado.leadboat.com> <53F48245.6020204@lab.ntt.co.jp> <20140822025137.GA563733@tornado.leadboat.com> In-Reply-To: <20140822025137.GA563733@tornado.leadboat.com> Content-Type: multipart/mixed; boundary="------------080003050300000307010108" X-TM-AS-MML: disable X-Pg-Spam-Score: 0.7 (/) 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. --------------080003050300000307010108 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit (2014/08/22 11:51), Noah Misch wrote: > On Wed, Aug 20, 2014 at 08:11:01PM +0900, Etsuro Fujita wrote: >> (2014/07/02 11:23), Noah Misch wrote: >>> Your chosen ANALYZE behavior is fair, but the messaging from a database-wide >>> ANALYZE VERBOSE needs work: >>> >>> INFO: analyzing "test_foreign_inherit.parent" >>> INFO: "parent": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows >>> INFO: analyzing "test_foreign_inherit.parent" inheritance tree >>> Please arrange to omit the 'analyzing "tablename" inheritance tree' message, >>> since this ANALYZE actually skipped that task. >> I think it would be better that this is handled in the same way as >> an inheritance tree that turns out to be a singe table that doesn't >> have any descendants in acquire_inherited_sample_rows(). That would >> still output the message as shown below, but I think that that would >> be more consistent with the existing code. The patch works so. > Today's ANALYZE VERBOSE messaging for former inheritance parents (tables with > relhassubclass = true but no pg_inherits.inhparent links) is deceptive, and I > welcome a fix to omit the spurious message. As defects go, this is quite > minor. There's fundamentally no value in collecting inheritance tree > statistics for such a parent, and no PostgreSQL command will do so. > > Your proposed behavior for inheritance parents having at least one foreign > table child is more likely to mislead DBAs in practice. An inheritance tree > genuinely exists, and a different ANALYZE command is quite capable of > collecting statistics on that inheritance tree. Messaging should reflect the > difference between ANALYZE invocations that do such work and ANALYZE > invocations that skip it. I'm anticipating a bug report along these lines: > > I saw poor estimates involving a child foreign table, so I ran "ANALYZE > VERBOSE", which reported 'INFO: analyzing "public.parent" inheritance > tree'. Estimates remained poor, so I scratched my head for awhile before > noticing that pg_stats didn't report such statistics. I then ran "ANALYZE > VERBOSE parent", saw the same 'INFO: analyzing "public.parent" inheritance > tree', but this time saw relevant rows in pg_stats. The message doesn't > reflect the actual behavior. > > I'll sympathize with that complaint. It's a minor point overall, but the code > impact is, I predict, small enough that we may as well get it right. A > credible alternative is to emit a second message indicating that we skipped > the inheritance tree statistics after all, and why we skipped them. I'd like to address this by emitting the second message as shown below: INFO: analyzing "public.parent" INFO: "parent": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows INFO: analyzing "public.parent" inheritance tree INFO: skipping analyze of "public.parent" inheritance tree --- this inheritance tree contains foreign tables Attached is the update version of the patch. Based on the result of discussions about attr_needed upthread, I've changed the patch so that create_foreignscan_plan makes reference to reltargetlist, not to attr_needed. (So, the patch in [1] isn't required, anymore.) Other changes: * Revise code/comments/docs a bit * Add more regression tests A separate patch (analyze.patch) handles the former case in a similar way. [1] http://www.postgresql.org/message-id/53F4707C.8030904@lab.ntt.co.jp Thanks, Best regards, Etsuro Fujita --------------080003050300000307010108 Content-Type: text/x-diff; name="foreign_inherit-v15.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="foreign_inherit-v15.patch" *** a/contrib/file_fdw/file_fdw.c --- b/contrib/file_fdw/file_fdw.c *************** *** 115,120 **** static void fileGetForeignRelSize(PlannerInfo *root, --- 115,125 ---- static void fileGetForeignPaths(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid); + static ForeignPath *fileReparameterizeForeignPath(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer); static ForeignScan *fileGetForeignPlan(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid, *************** *** 143,149 **** static bool check_selective_binary_conversion(RelOptInfo *baserel, static void estimate_size(PlannerInfo *root, RelOptInfo *baserel, FileFdwPlanState *fdw_private); static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel, ! FileFdwPlanState *fdw_private, Cost *startup_cost, Cost *total_cost); static int file_acquire_sample_rows(Relation onerel, int elevel, HeapTuple *rows, int targrows, --- 148,154 ---- static void estimate_size(PlannerInfo *root, RelOptInfo *baserel, FileFdwPlanState *fdw_private); static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel, ! FileFdwPlanState *fdw_private, List *join_conds, Cost *startup_cost, Cost *total_cost); static int file_acquire_sample_rows(Relation onerel, int elevel, HeapTuple *rows, int targrows, *************** *** 161,166 **** file_fdw_handler(PG_FUNCTION_ARGS) --- 166,172 ---- fdwroutine->GetForeignRelSize = fileGetForeignRelSize; fdwroutine->GetForeignPaths = fileGetForeignPaths; + fdwroutine->ReparameterizeForeignPath = fileReparameterizeForeignPath; fdwroutine->GetForeignPlan = fileGetForeignPlan; fdwroutine->ExplainForeignScan = fileExplainForeignScan; fdwroutine->BeginForeignScan = fileBeginForeignScan; *************** *** 509,515 **** fileGetForeignPaths(PlannerInfo *root, (Node *) columns)); /* Estimate costs */ ! estimate_costs(root, baserel, fdw_private, &startup_cost, &total_cost); /* --- 515,522 ---- (Node *) columns)); /* Estimate costs */ ! estimate_costs(root, baserel, ! fdw_private, NIL, &startup_cost, &total_cost); /* *************** *** 534,539 **** fileGetForeignPaths(PlannerInfo *root, --- 541,581 ---- } /* + * fileReparameterizeForeignPath + * Attempt to modify a given path to have greater parameterization + */ + static ForeignPath * + fileReparameterizeForeignPath(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer) + { + ParamPathInfo *param_info; + FileFdwPlanState *fdw_private = (FileFdwPlanState *) baserel->fdw_private; + Cost startup_cost; + Cost total_cost; + + /* Get the ParamPathInfo */ + param_info = get_baserel_parampathinfo(root, baserel, required_outer); + + /* Redo the cost estimates */ + estimate_costs(root, baserel, + fdw_private, + param_info->ppi_clauses, + &startup_cost, &total_cost); + + /* Recreate and return the new path */ + return create_foreignscan_path(root, baserel, + param_info->ppi_rows, + startup_cost, + total_cost, + NIL, /* no pathkeys */ + required_outer, + path->fdw_private); + } + + /* * fileGetForeignPlan * Create a ForeignScan plan node for scanning the foreign table */ *************** *** 962,973 **** estimate_size(PlannerInfo *root, RelOptInfo *baserel, */ static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel, ! FileFdwPlanState *fdw_private, Cost *startup_cost, Cost *total_cost) { BlockNumber pages = fdw_private->pages; double ntuples = fdw_private->ntuples; Cost run_cost = 0; Cost cpu_per_tuple; /* --- 1004,1016 ---- */ static void estimate_costs(PlannerInfo *root, RelOptInfo *baserel, ! FileFdwPlanState *fdw_private, List *join_conds, Cost *startup_cost, Cost *total_cost) { BlockNumber pages = fdw_private->pages; double ntuples = fdw_private->ntuples; Cost run_cost = 0; + QualCost join_cost; Cost cpu_per_tuple; /* *************** *** 978,985 **** estimate_costs(PlannerInfo *root, RelOptInfo *baserel, */ run_cost += seq_page_cost * pages; ! *startup_cost = baserel->baserestrictcost.startup; ! cpu_per_tuple = cpu_tuple_cost * 10 + baserel->baserestrictcost.per_tuple; run_cost += cpu_per_tuple * ntuples; *total_cost = *startup_cost + run_cost; } --- 1021,1031 ---- */ run_cost += seq_page_cost * pages; ! cost_qual_eval(&join_cost, join_conds, root); ! *startup_cost = ! (baserel->baserestrictcost.startup + join_cost.startup); ! cpu_per_tuple = cpu_tuple_cost * 10 + ! (baserel->baserestrictcost.per_tuple + join_cost.per_tuple); run_cost += cpu_per_tuple * ntuples; *total_cost = *startup_cost + run_cost; } *** 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,177 ---- -- but this should be ignored SELECT * FROM agg_csv FOR UPDATE; + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_text INHERIT agg; + ALTER FOREIGN TABLE agg_csv INHERIT agg; + INSERT INTO agg + SELECT x, 5432.0 FROM generate_series(0,1000) x; + 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_text; + SELECT * FROM agg_csv; + -- 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; + -- path reparameterization should work + CREATE TABLE int_tbl (f1) AS SELECT x FROM generate_series(0,1000) x; + ANALYZE int_tbl; + ANALYZE agg; + ANALYZE agg_text; + ANALYZE agg_csv; + \t on + EXPLAIN (VERBOSE, COSTS FALSE) + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + \t off + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + CREATE INDEX agg_idx ON agg (a); + ANALYZE agg; + \t on + EXPLAIN (VERBOSE, COSTS FALSE) + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + \t off + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + ALTER FOREIGN TABLE agg_text NO INHERIT agg; + DROP TABLE int_tbl; + 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 *************** *** 219,224 **** SELECT * FROM agg_csv FOR UPDATE; --- 219,348 ---- 42 | 324.78 (3 rows) + -- table inheritance tests + CREATE TABLE agg (a int2, b float4); + ALTER FOREIGN TABLE agg_text INHERIT agg; + ALTER FOREIGN TABLE agg_csv INHERIT agg; + INSERT INTO agg + SELECT x, 5432.0 FROM generate_series(0,1000) x; + 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_text; + a | b + -----+--------- + 56 | 7.8 + 100 | 99.097 + 0 | 0.09561 + 42 | 324.78 + (4 rows) + + SELECT * FROM agg_csv; + a | b + -----+--------- + 100 | 99.097 + 0 | 0.09561 + 42 | 324.78 + (3 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) + + -- path reparameterization should work + CREATE TABLE int_tbl (f1) AS SELECT x FROM generate_series(0,1000) x; + ANALYZE int_tbl; + ANALYZE agg; + ANALYZE agg_text; + ANALYZE agg_csv; + \t on + EXPLAIN (VERBOSE, COSTS FALSE) + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + Hash Join + Output: agg.a, agg.b, int_tbl.f1 + Hash Cond: (agg.a = int_tbl.f1) + -> Append + -> Seq Scan on public.agg + Output: agg.a, agg.b + -> Foreign Scan on public.agg_text + Output: agg_text.a, agg_text.b + Foreign File: @abs_srcdir@/data/agg.data + -> Foreign Scan on public.agg_csv + Output: agg_csv.a, agg_csv.b + Foreign File: @abs_srcdir@/data/agg.csv + -> Hash + Output: int_tbl.f1 + -> Limit + Output: int_tbl.f1 + -> Seq Scan on public.int_tbl + Output: int_tbl.f1 + + \t off + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + a | b | f1 + ---+---------+---- + 0 | 5432 | 0 + 0 | 0.09561 | 0 + 0 | 0.09561 | 0 + (3 rows) + + CREATE INDEX agg_idx ON agg (a); + ANALYZE agg; + \t on + EXPLAIN (VERBOSE, COSTS FALSE) + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + Nested Loop + Output: agg.a, agg.b, int_tbl.f1 + -> Limit + Output: int_tbl.f1 + -> Seq Scan on public.int_tbl + Output: int_tbl.f1 + -> Append + -> Index Scan using agg_idx on public.agg + Output: agg.a, agg.b + Index Cond: (agg.a = int_tbl.f1) + -> Foreign Scan on public.agg_text + Output: agg_text.a, agg_text.b + Filter: (int_tbl.f1 = agg_text.a) + Foreign File: @abs_srcdir@/data/agg.data + -> Foreign Scan on public.agg_csv + Output: agg_csv.a, agg_csv.b + Filter: (int_tbl.f1 = agg_csv.a) + Foreign File: @abs_srcdir@/data/agg.csv + + \t off + SELECT * FROM agg JOIN (SELECT f1 FROM int_tbl LIMIT 1) ss ON a = f1; + a | b | f1 + ---+---------+---- + 0 | 5432 | 0 + 0 | 0.09561 | 0 + 0 | 0.09561 | 0 + (3 rows) + + ALTER FOREIGN TABLE agg_csv NO INHERIT agg; + ALTER FOREIGN TABLE agg_text NO INHERIT agg; + DROP TABLE int_tbl; + 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 *************** *** 2841,2846 **** NOTICE: NEW: (13,"test triggered !") --- 2841,3398 ---- (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) + + 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 path reparameterization + create table loctbl (id int, x int); + create table parent (id int, x int); + create table locchild () inherits (parent); + create foreign table remchild () inherits (parent) + server loopback options (table_name 'loctbl'); + insert into parent select x, x from generate_series(0,1000) x; + insert into locchild select x, x from generate_series(0,1000) x; + insert into remchild select x, x from generate_series(0,1000) x; + create table inttbl (f1) as select x from generate_series(0,1000) x; + analyze inttbl; + analyze parent; + analyze locchild; + analyze remchild; + explain (costs off) + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + QUERY PLAN + -------------------------------------- + Hash Join + Hash Cond: (parent.id = inttbl.f1) + -> Append + -> Seq Scan on parent + -> Seq Scan on locchild + -> Foreign Scan on remchild + -> Hash + -> Limit + -> Seq Scan on inttbl + (9 rows) + + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + id | x | f1 + ----+---+---- + 0 | 0 | 0 + 0 | 0 | 0 + 0 | 0 | 0 + (3 rows) + + create index parent_idx on parent (id); + create index locchild_idx on locchild (id); + analyze parent; + analyze locchild; + explain (costs off) + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + QUERY PLAN + ------------------------------------------------------- + Nested Loop + -> Limit + -> Seq Scan on inttbl + -> Append + -> Index Scan using parent_idx on parent + Index Cond: (id = inttbl.f1) + -> Index Scan using locchild_idx on locchild + Index Cond: (id = inttbl.f1) + -> Foreign Scan on remchild + (9 rows) + + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + id | x | f1 + ----+---+---- + 0 | 0 | 0 + 0 | 0 | 0 + 0 | 0 | 0 + (3 rows) + + drop table parent cascade; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to table locchild + drop cascades to foreign table remchild + drop table loctbl; + drop table inttbl; + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== CREATE SCHEMA import_source; *** a/contrib/postgres_fdw/postgres_fdw.c --- b/contrib/postgres_fdw/postgres_fdw.c *************** *** 238,243 **** static void postgresGetForeignRelSize(PlannerInfo *root, --- 238,248 ---- static void postgresGetForeignPaths(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid); + static ForeignPath *postgresReparameterizeForeignPath(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer); static ForeignScan *postgresGetForeignPlan(PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid, *************** *** 341,346 **** postgres_fdw_handler(PG_FUNCTION_ARGS) --- 346,352 ---- /* Functions for scanning foreign tables */ routine->GetForeignRelSize = postgresGetForeignRelSize; routine->GetForeignPaths = postgresGetForeignPaths; + routine->ReparameterizeForeignPath = postgresReparameterizeForeignPath; routine->GetForeignPlan = postgresGetForeignPlan; routine->BeginForeignScan = postgresBeginForeignScan; routine->IterateForeignScan = postgresIterateForeignScan; *************** *** 731,736 **** postgresGetForeignPaths(PlannerInfo *root, --- 737,784 ---- } /* + * postgresReparameterizeForeignPath + * Attempt to modify a given path to have greater parameterization + */ + static ForeignPath * + postgresReparameterizeForeignPath(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer) + { + ParamPathInfo *param_info; + double rows; + int width; + Cost startup_cost; + Cost total_cost; + + /* Get the ParamPathInfo */ + param_info = get_baserel_parampathinfo(root, baserel, required_outer); + + /* Redo the cost estimates */ + estimate_path_cost_size(root, baserel, + param_info->ppi_clauses, + &rows, &width, + &startup_cost, &total_cost); + + /* + * ppi_rows currently won't get looked at by anything, but still we + * may as well ensure that it matches our idea of the rowcount. + */ + param_info->ppi_rows = rows; + + /* Recreate and return the new path */ + return create_foreignscan_path(root, baserel, + rows, + startup_cost, + total_cost, + NIL, /* no pathkeys */ + required_outer, + NIL); /* no fdw_private list */ + } + + /* * postgresGetForeignPlan * Create ForeignScan plan node which implements selected best path */ *************** *** 823,828 **** postgresGetForeignPlan(PlannerInfo *root, --- 871,887 ---- { 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) { /* *************** *** 1777,1787 **** estimate_path_cost_size(PlannerInfo *root, } else { ! /* ! * We don't support join conditions in this mode (hence, no ! * parameterized paths can be made). ! */ ! Assert(join_conds == NIL); /* Use rows/width estimates made by set_baserel_size_estimates. */ rows = baserel->rows; --- 1836,1843 ---- } else { ! Selectivity join_sel; ! QualCost join_cost; /* Use rows/width estimates made by set_baserel_size_estimates. */ rows = baserel->rows; *************** *** 1794,1810 **** estimate_path_cost_size(PlannerInfo *root, retrieved_rows = clamp_row_est(rows / fpinfo->local_conds_sel); retrieved_rows = Min(retrieved_rows, baserel->tuples); /* * Cost as though this were a seqscan, which is pessimistic. We ! * effectively imagine the local_conds are being evaluated remotely, ! * too. */ startup_cost = 0; run_cost = 0; run_cost += seq_page_cost * baserel->pages; ! startup_cost += baserel->baserestrictcost.startup; ! cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple; run_cost += cpu_per_tuple * baserel->tuples; total_cost = startup_cost + run_cost; --- 1850,1878 ---- retrieved_rows = clamp_row_est(rows / fpinfo->local_conds_sel); retrieved_rows = Min(retrieved_rows, baserel->tuples); + /* Factor in the selectivity of the join_conds */ + join_sel = clauselist_selectivity(root, + join_conds, + baserel->relid, + JOIN_INNER, + NULL); + + rows = clamp_row_est(rows * join_sel); + /* * Cost as though this were a seqscan, which is pessimistic. We ! * effectively imagine the local_conds and join_conds are being ! * evaluated remotely, too. */ startup_cost = 0; run_cost = 0; run_cost += seq_page_cost * baserel->pages; ! cost_qual_eval(&join_cost, join_conds, root); ! startup_cost += ! (baserel->baserestrictcost.startup + join_cost.startup); ! cpu_per_tuple = cpu_tuple_cost + ! (baserel->baserestrictcost.per_tuple + join_cost.per_tuple); run_cost += cpu_per_tuple * baserel->tuples; total_cost = startup_cost + run_cost; *** a/contrib/postgres_fdw/sql/postgres_fdw.sql --- b/contrib/postgres_fdw/sql/postgres_fdw.sql *************** *** 617,622 **** UPDATE rem1 SET f2 = 'testo'; --- 617,800 ---- 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; + + drop table ptbl cascade; + drop table ltbl; + + -- Test path reparameterization + create table loctbl (id int, x int); + + create table parent (id int, x int); + create table locchild () inherits (parent); + create foreign table remchild () inherits (parent) + server loopback options (table_name 'loctbl'); + insert into parent select x, x from generate_series(0,1000) x; + insert into locchild select x, x from generate_series(0,1000) x; + insert into remchild select x, x from generate_series(0,1000) x; + + create table inttbl (f1) as select x from generate_series(0,1000) x; + + analyze inttbl; + analyze parent; + analyze locchild; + analyze remchild; + + explain (costs off) + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + + create index parent_idx on parent (id); + create index locchild_idx on locchild (id); + analyze parent; + analyze locchild; + + explain (costs off) + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + select * from parent join (select f1 from inttbl limit 1) ss on id = f1; + + drop table parent cascade; + drop table loctbl; + drop table inttbl; + + -- =================================================================== -- test IMPORT FOREIGN SCHEMA -- =================================================================== *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** *** 2224,2229 **** VALUES ('Albany', NULL, NULL, 'NY'); --- 2224,2247 ---- further privileges to be granted. + + Note that a foreign table can also inherit from more than one parent + table (see ). + 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 *************** *** 2371,2377 **** 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. --- 2389,2398 ---- 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.) *************** *** 2434,2441 **** VALUES ('Albany', NULL, NULL, 'NY'); ! We will refer to the child tables as partitions, though they ! are in every way normal PostgreSQL tables. --- 2455,2462 ---- ! We will refer to the child tables as partitions, though we assume ! that they are normal PostgreSQL tables. *** a/doc/src/sgml/fdwhandler.sgml --- b/doc/src/sgml/fdwhandler.sgml *************** *** 162,167 **** GetForeignPaths (PlannerInfo *root, --- 162,198 ---- + ForeignPath * + ReparameterizeForeignPath (PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer); + + + Create an access path for a scan on a foreign table using join + clauses, which is called a parameterized path. + This is called during query planning. + The parameters are as for GetForeignRelSize, plus + the ForeignPath (previously produced by + GetForeignPaths), and the IDs of all other tables + that provide the join clauses. + + + + This function must generate a parameterized path for a scan on the + foreign table. The parameterized path must contain cost estimates, + and can contain any FDW-private information that is needed to identify + the specific scan method intended. Unlike the other scan-related + functions, this function is optional. + + + + See for additional information. + + + + ForeignScan * GetForeignPlan (PlannerInfo *root, RelOptInfo *baserel, *************** *** 868,877 **** GetForeignServerByName(const char *name, bool missing_ok); The FDW callback functions GetForeignRelSize, ! GetForeignPaths, GetForeignPlan, and ! PlanForeignModify must fit into the workings of the ! PostgreSQL planner. Here are some notes about what ! they must do. --- 899,908 ---- The FDW callback functions GetForeignRelSize, ! GetForeignPaths, ReparameterizeForeignPath, ! GetForeignPlan, and PlanForeignModify must fit ! into the workings of the PostgreSQL planner. Here are ! some notes about what they must do. *************** *** 901,914 **** GetForeignServerByName(const char *name, bool missing_ok); to initialize it to NULL when the baserel node is created. It is useful for passing information forward from GetForeignRelSize to GetForeignPaths and/or ! GetForeignPaths to GetForeignPlan, thereby ! avoiding recalculation. ! GetForeignPaths can identify the meaning of different ! access paths by storing private information in the ! fdw_private field of ForeignPath nodes. fdw_private is declared as a List pointer, but could actually contain anything since the core planner does not touch it. However, best practice is to use a representation that's dumpable --- 932,948 ---- to initialize it to NULL when the baserel node is created. It is useful for passing information forward from GetForeignRelSize to GetForeignPaths and/or ! GetForeignPaths to ReparameterizeForeignPath ! and/or ! GetForeignPaths or ReparameterizeForeignPath ! to GetForeignPlan, thereby avoiding recalculation. ! GetForeignPaths or ReparameterizeForeignPath ! can identify the meaning of different access paths by storing private ! information in the fdw_private field of ! ForeignPath nodes. fdw_private is declared as a List pointer, but could actually contain anything since the core planner does not touch it. However, best practice is to use a representation that's dumpable *************** *** 951,960 **** GetForeignServerByName(const char *name, bool missing_ok); sub_expression, which it determines can be executed on the remote server given the locally-evaluated value of the sub_expression. The actual identification of such a ! clause should happen during GetForeignPaths, since it would ! affect the cost estimate for the path. The path's ! fdw_private field would probably include a pointer to ! the identified clause's RestrictInfo node. Then GetForeignPlan would remove that clause from scan_clauses, but add the sub_expression to fdw_exprs to ensure that it gets massaged into executable form. It would probably --- 985,995 ---- sub_expression, which it determines can be executed on the remote server given the locally-evaluated value of the sub_expression. The actual identification of such a ! clause should happen during GetForeignPaths or ! ReparameterizeForeignPath, since it would affect the cost ! estimate for the path. The path's fdw_private field ! would probably include a pointer to the identified clause's ! RestrictInfo node. Then GetForeignPlan would remove that clause from scan_clauses, but add the sub_expression to fdw_exprs to ensure that it gets massaged into executable form. It would probably *** a/doc/src/sgml/ref/alter_foreign_table.sgml --- b/doc/src/sgml/ref/alter_foreign_table.sgml *************** *** 42,51 **** 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 ENABLE ALWAYS TRIGGER trigger_name + INHERIT parent_table + NO INHERIT parent_table OWNER TO new_owner OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) *************** *** 153,158 **** 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 *************** *** 164,169 **** 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 *************** *** 285,290 **** ALTER FOREIGN TABLE [ IF EXISTS ] name + table_constraint + + + New table constraint for the table. + + + + + + constraint_name + + + Name of an existing constraint to drop. + + + + + CASCADE *************** *** 336,341 **** 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 *************** *** 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. --- 441,450 ---- 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/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** *** 993,998 **** ALTER TABLE ALL IN TABLESPACE name --- 993,1005 ---- + If a table has any descendant tables that are foreign, a recursive + SET STORAGE operation will be rejected 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. *************** *** 1001,1006 **** ALTER TABLE ALL IN TABLESPACE name --- 1008,1026 ---- + When adding a CHECK constraint with the NOT VALID + option recursively, an inherited constraint on a descendant + table that is foreign will be marked valid without checking + consistency with the foreign server. + + + + A recursive SET STORAGE operation will make the + storage mode for a descendant table's column unchanged if the table is + foreign. + + + 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 *************** *** 19,27 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] [, ... ] ] ) SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ] --- 19,29 ---- CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ ! { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ] ! | table_constraint } [, ... ] ] ) + [ INHERITS ( parent_table [, ... ] ) ] SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ] *************** *** 30,36 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 32,44 ---- [ 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 --- 146,172 ---- + 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 *************** *** 159,164 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 188,205 ---- + 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 *************** *** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name --- 228,251 ---- + + 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. Such constraints are + used for 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 parent tables + have oid system columns. + + Examples *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 2219,2224 **** AddRelationNewConstraints(Relation rel, --- 2219,2230 ---- if (cdef->contype != CONSTR_CHECK) continue; + 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"))); + if (cdef->raw_expr != NULL) { Assert(cdef->cooked_expr == NULL); *** 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, BufferAccessStrategy bstrategy) { Relation onerel; int elevel; --- 117,126 ---- * analyze_rel() -- analyze one relation */ void ! analyze_rel(Oid relid, ! VacuumStmt *vacstmt, ! VacuumMode vacmode, ! BufferAccessStrategy bstrategy) { Relation onerel; int elevel; *************** *** 129,134 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy) --- 134,140 ---- elevel = DEBUG2; /* Set up static variables */ + vac_mode = vacmode; vac_strategy = bstrategy; /* *************** *** 294,302 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy) * 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, *************** *** 1439,1449 **** 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, --- 1444,1489 ---- /* + * Check wether to contain at least one foreign table + */ + static bool + has_foreign(Oid parentrelId, List *tableOIDs) + { + bool result = false; + ListCell *lc; + + /* There are no children */ + if (list_length(tableOIDs) < 2) + return result; + + 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, *************** *** 1452,1457 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1492,1498 ---- { List *tableOIDs; Relation *rels; + AcquireSampleRowsFunc *acquirefunc; double *relblocks; double totalblocks; int numrows, *************** *** 1482,1491 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1523,1550 ---- } /* + * 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)))); + 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; *************** *** 1507,1513 **** acquire_inherited_sample_rows(Relation onerel, int elevel, } rels[nrels] = childrel; ! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel); totalblocks += relblocks[nrels]; nrels++; } --- 1566,1601 ---- } 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++; } *************** *** 1525,1530 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1613,1619 ---- { Relation childrel = rels[i]; double childblocks = relblocks[i]; + AcquireSampleRowsFunc childacquirefunc = acquirefunc[i]; if (childblocks > 0) { *************** *** 1540,1551 **** 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 && --- 1629,1640 ---- 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 *************** *** 312,318 **** 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, --- 312,319 ---- 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, *************** *** 472,481 **** 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, --- 473,478 ---- *************** *** 565,570 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) --- 562,591 ---- 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 *************** *** 3078,3101 **** 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; --- 3099,3126 ---- * 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; *************** *** 3108,3114 **** 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; --- 3133,3140 ---- 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; *************** *** 3126,3132 **** 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) --- 3152,3158 ---- 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) *************** *** 3140,3146 **** 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) --- 3166,3172 ---- 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) *************** *** 3230,3240 **** 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; --- 3256,3272 ---- 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; *************** *** 3268,3274 **** 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 */ ATSimplePermissions(rel, ATT_TABLE); --- 3300,3305 ---- *************** *** 4254,4260 **** 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 --- 4285,4292 ---- */ 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 *************** *** 4282,4289 **** 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); } } --- 4314,4325 ---- 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); } } *************** *** 4573,4579 **** 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); --- 4609,4615 ---- /* 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); *************** *** 4869,4874 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, --- 4905,4915 ---- /* 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 */ *************** *** 5469,5475 **** 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 --- 5510,5516 ---- /* 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 *************** *** 5861,5867 **** 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 --- 5902,5915 ---- /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) ! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ! ! /* Don't allow ADD CONSTRAINT NOT VALID to be applied to foreign tables */ ! if (tab->relkind == RELKIND_FOREIGN_TABLE && ! constr->skip_validation && !recursing) ! ereport(ERROR, ! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("NOT VALID is not supported on foreign tables"))); /* * Call AddRelationNewConstraints to do the work, making sure it works on *************** *** 5872,5880 **** 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 */ --- 5920,5936 ---- * 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 */ *************** *** 7361,7367 **** 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); --- 7417,7423 ---- /* 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); *************** *** 7696,7702 **** 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, --- 7752,7761 ---- * 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. */ *************** *** 251,257 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast, PushActiveSnapshot(GetTransactionSnapshot()); } ! analyze_rel(relid, vacstmt, vac_strategy); if (use_own_xacts) { --- 266,272 ---- PushActiveSnapshot(GetTransactionSnapshot()); } ! analyze_rel(relid, vacstmt, vac_mode, vac_strategy); if (use_own_xacts) { *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *************** *** 2285,2290 **** EvalPlanQualFetchRowMarks(EPQState *epqstate) --- 2285,2310 ---- 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 *************** *** 2315,2320 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b) --- 2315,2321 ---- 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 *************** *** 2419,2424 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) --- 2419,2425 ---- 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 *************** *** 1248,1253 **** _readRangeTblEntry(void) --- 1248,1254 ---- 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/plan/createplan.c --- b/src/backend/optimizer/plan/createplan.c *************** *** 1945,1950 **** create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path, --- 1945,1952 ---- RelOptInfo *rel = best_path->path.parent; Index scan_relid = rel->relid; RangeTblEntry *rte; + Bitmapset *attrs_used = NULL; + ListCell *lc; int i; /* it should be a base rel... */ *************** *** 1993,2008 **** create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path, * bit of a kluge and might go away someday, so we intentionally leave it * out of the API presented to FDWs. */ scan_plan->fsSystemCol = false; for (i = rel->min_attr; i < 0; i++) { ! if (!bms_is_empty(rel->attr_needed[i - rel->min_attr])) { scan_plan->fsSystemCol = true; break; } } return scan_plan; } --- 1995,2029 ---- * bit of a kluge and might go away someday, so we intentionally leave it * out of the API presented to FDWs. */ + + /* + * Add all the attributes needed for joins or final output. Note: we must + * look at reltargetlist, not the attr_needed data, because attr_needed + * isn't computed for inheritance child rels. + */ + pull_varattnos((Node *) rel->reltargetlist, rel->relid, &attrs_used); + + /* Add all the attributes used by restriction clauses. */ + foreach(lc, rel->baserestrictinfo) + { + RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc); + + pull_varattnos((Node *) rinfo->clause, rel->relid, &attrs_used); + } + + /* Is any system column requested? */ scan_plan->fsSystemCol = false; for (i = rel->min_attr; i < 0; i++) { ! if (bms_is_member(i - rel->min_attr, attrs_used)) { scan_plan->fsSystemCol = true; break; } } + bms_free(attrs_used); + return scan_plan; } *** 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->noWait = oldrc->noWait; 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->noWait = oldrc->noWait; 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/optimizer/util/pathnode.c --- b/src/backend/optimizer/util/pathnode.c *************** *** 16,21 **** --- 16,22 ---- #include + #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "optimizer/clauses.h" *************** *** 1921,1926 **** reparameterize_path(PlannerInfo *root, Path *path, --- 1922,1952 ---- case T_SubqueryScan: return create_subqueryscan_path(root, rel, path->pathkeys, required_outer); + case T_ForeignScan: + { + ForeignPath *newpath = NULL; + + /* Let the FDW reparameterize the path node if possible */ + if (rel->fdwroutine->ReparameterizeForeignPath != NULL) + { + Index scan_relid = rel->relid; + RangeTblEntry *rte; + + /* it should be a base rel... */ + Assert(scan_relid > 0); + Assert(rel->rtekind == RTE_RELATION); + rte = planner_rt_fetch(scan_relid, root); + Assert(rte->rtekind == RTE_RELATION); + + newpath = + rel->fdwroutine->ReparameterizeForeignPath(root, + rel, + rte->relid, + (ForeignPath *) path, + required_outer); + } + return (Path *) newpath; + } default: break; } *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 4316,4347 **** 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; } ; --- 4316,4347 ---- CreateForeignTableStmt: CREATE FOREIGN TABLE qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $4->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $4; n->base.tableElts = $6; ! n->base.inhRelations = $8; n->base.if_not_exists = false; /* FDW-specific data */ ! n->servername = $10; ! n->options = $11; $$ = (Node *) n; } | CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name '(' OptTableElementList ')' ! OptInherit SERVER name create_generic_options { CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt); $7->relpersistence = RELPERSISTENCE_PERMANENT; n->base.relation = $7; n->base.tableElts = $9; ! n->base.inhRelations = $11; n->base.if_not_exists = true; /* FDW-specific data */ ! n->servername = $13; ! n->options = $14; $$ = (Node *) n; } ; *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** *** 515,526 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_CHECK: - if (cxt->isforeign) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("constraints are not supported on foreign tables"), - parser_errposition(cxt->pstate, - constraint->location))); cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); break; --- 515,520 ---- *************** *** 529,535 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) --- 523,529 ---- if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("primary key or unique constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); if (constraint->keys == NIL) *************** *** 546,552 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); --- 540,546 ---- if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); *************** *** 605,614 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { ! if (cxt->isforeign) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); --- 599,612 ---- static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { ! if (cxt->isforeign && ! (constraint->contype == CONSTR_PRIMARY || ! constraint->contype == CONSTR_UNIQUE || ! constraint->contype == CONSTR_EXCLUSION || ! constraint->contype == CONSTR_FOREIGN)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), ! errmsg("primary key, unique, exclusion, or foreign key constraints are not supported on foreign tables"), parser_errposition(cxt->pstate, constraint->location))); *** a/src/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); *************** *** 174,180 **** extern void lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy); /* in commands/analyze.c */ ! extern void analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy); extern bool std_typanalyze(VacAttrStats *stats); extern double anl_random_fract(void); --- 183,191 ---- BufferAccessStrategy bstrategy); /* in commands/analyze.c */ ! extern void analyze_rel(Oid relid, ! VacuumStmt *vacstmt, ! VacuumMode vacmode, BufferAccessStrategy bstrategy); extern bool std_typanalyze(VacAttrStats *stats); extern double anl_random_fract(void); *** a/src/include/foreign/fdwapi.h --- b/src/include/foreign/fdwapi.h *************** *** 31,36 **** typedef void (*GetForeignPaths_function) (PlannerInfo *root, --- 31,42 ---- RelOptInfo *baserel, Oid foreigntableid); + typedef ForeignPath *(*ReparameterizeForeignPath_function) (PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + ForeignPath *path, + Relids required_outer); + typedef ForeignScan *(*GetForeignPlan_function) (PlannerInfo *root, RelOptInfo *baserel, Oid foreigntableid, *************** *** 120,125 **** typedef struct FdwRoutine --- 126,132 ---- /* Functions for scanning foreign tables */ GetForeignRelSize_function GetForeignRelSize; GetForeignPaths_function GetForeignPaths; + ReparameterizeForeignPath_function ReparameterizeForeignPath; GetForeignPlan_function GetForeignPlan; BeginForeignScan_function BeginForeignScan; IterateForeignScan_function IterateForeignScan; *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 810,815 **** typedef struct RangeTblEntry --- 810,816 ---- 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 *************** *** 815,820 **** typedef enum RowMarkType --- 815,822 ---- * 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 + * in cases where the inheritance hierarchy 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 *************** *** 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; --- 748,759 ---- 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); 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_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; *************** *** 1193,1198 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1; --- 1189,1379 ---- 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 (ff1 integer, ff2 text); + CREATE VIEW view1 AS SELECT 1; + ALTER TABLE view1 INHERIT pt1; -- ERROR + ERROR: "view1" is not a table or foreign table + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + ALTER FOREIGN TABLE ct1 NO INHERIT pt1; + ALTER FOREIGN TABLE ct1 INHERIT pt1; + ALTER FOREIGN TABLE ct2 INHERIT pt1; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + ff1 | integer | | plain | | + ff2 | text | | extended | | + Child tables: ct1, + ct2 + + \d+ ct1 + Foreign table "public.ct1" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + \d+ ct2 + Foreign table "public.ct2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + DROP FOREIGN TABLE ct1; + -- cannot change storage modes for foreign tables + ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL; + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + ff1 | integer | | plain | | + ff2 | text | | external | | + Child tables: ct1, + ct2 + + \d+ ct1 + Foreign table "public.ct1" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + \d+ ct2 + Foreign table "public.ct2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + DROP FOREIGN TABLE ct1; + DROP FOREIGN TABLE ct2; + ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED; + -- connoinherit should be true for NO INHERIT constraint + ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT; + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10); + SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2; + relname | conname | contype | conislocal | coninhcount | connoinherit + ---------+---------+---------+------------+-------------+-------------- + pt1 | pt1chk1 | c | t | 0 | t + pt1 | pt1chk2 | c | t | 0 | f + (2 rows) + + -- child does not inherit NO INHERIT constraints + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + -- child must have parent's INHERIT constraints + ALTER FOREIGN TABLE ct2 INHERIT pt1; -- ERROR + ERROR: child table is missing constraint "pt1chk2" + ALTER FOREIGN TABLE ct2 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10); + ALTER FOREIGN TABLE ct2 INHERIT pt1; + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints: + "pt1chk1" CHECK (ff1 > 0) NO INHERIT + "pt1chk2" CHECK (ff1 > 10) + Child tables: ct1, + ct2 + + \d+ ct1 + Foreign table "public.ct1" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints: + "pt1chk2" CHECK (ff1 > 10) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + \d+ ct2 + Foreign table "public.ct2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints: + "pt1chk2" CHECK (ff1 > 10) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE; + -- NOT VALID should be ignored for foreign tables + INSERT INTO pt1 VALUES (100, 'pt1'); + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10) NOT VALID; + NOTICE: merging constraint "pt1chk2" with inherited definition + \d+ pt1 + Table "public.pt1" + Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+------------- + ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints: + "pt1chk1" CHECK (ff1 > 0) NO INHERIT + "pt1chk2" CHECK (ff1 > 10) NOT VALID + Child tables: ct1, + ct2 + + \d+ ct1 + Foreign table "public.ct1" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints: + "pt1chk2" CHECK (ff1 > 10) + Server: s0 + FDW Options: (delimiter ',', quote '"', "be quoted" 'value') + Inherits: pt1 + + \d+ ct2 + Foreign table "public.ct2" + Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+------------- + ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints: + "pt1chk2" CHECK (ff1 > 10) + 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; + -- cannot add an OID system column + ALTER TABLE pt1 SET WITH OIDS; -- ERROR + ERROR: cannot add OID column to foreign table "ct1" + DROP VIEW view1; + DROP TABLE pt1 CASCADE; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to foreign table ct1 + drop cascades to foreign table ct2 -- 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 *************** *** 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 '@'); --- 314,323 ---- 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); 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_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 '@'); *************** *** 326,331 **** ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; --- 326,332 ---- ALTER FOREIGN TABLE ft1 DROP COLUMN c9; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR + ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 *************** *** 514,519 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1; --- 515,583 ---- DROP FUNCTION dummy_trigger(); + -- Table inheritance + CREATE TABLE pt1 (ff1 integer, ff2 text); + CREATE VIEW view1 AS SELECT 1; + ALTER TABLE view1 INHERIT pt1; -- ERROR + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + ALTER FOREIGN TABLE ct1 NO INHERIT pt1; + ALTER FOREIGN TABLE ct1 INHERIT pt1; + ALTER FOREIGN TABLE ct2 INHERIT pt1; + \d+ pt1 + \d+ ct1 + \d+ ct2 + + DROP FOREIGN TABLE ct1; + + -- cannot change storage modes for foreign tables + ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL; + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + \d+ pt1 + \d+ ct1 + \d+ ct2 + + DROP FOREIGN TABLE ct1; + DROP FOREIGN TABLE ct2; + ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED; + + -- connoinherit should be true for NO INHERIT constraint + ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT; + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10); + SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2; + -- child does not inherit NO INHERIT constraints + CREATE FOREIGN TABLE ct1 () INHERITS (pt1) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text) + SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); + -- child must have parent's INHERIT constraints + ALTER FOREIGN TABLE ct2 INHERIT pt1; -- ERROR + ALTER FOREIGN TABLE ct2 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10); + ALTER FOREIGN TABLE ct2 INHERIT pt1; + \d+ pt1 + \d+ ct1 + \d+ ct2 + + ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE; + + -- NOT VALID should be ignored for foreign tables + INSERT INTO pt1 VALUES (100, 'pt1'); + ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10) NOT VALID; + \d+ pt1 + \d+ ct1 + \d+ ct2 + -- VALIDATE CONSTRAINT should work by ignoring foreign tables + ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2; + + -- cannot add an OID system column + ALTER TABLE pt1 SET WITH OIDS; -- ERROR + + DROP VIEW view1; + 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 --------------080003050300000307010108 Content-Type: text/x-diff; name="analyze.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="analyze.patch" *** a/src/backend/commands/analyze.c --- b/src/backend/commands/analyze.c *************** *** 1478,1483 **** acquire_inherited_sample_rows(Relation onerel, int elevel, --- 1478,1487 ---- /* CCI because we already updated the pg_class row in this command */ CommandCounterIncrement(); SetRelationHasSubclass(RelationGetRelid(onerel), false); + ereport(elevel, + (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains no child tables", + get_namespace_name(RelationGetNamespace(onerel)), + RelationGetRelationName(onerel)))); return 0; } --------------080003050300000307010108 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 --------------080003050300000307010108--