From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Noah Misch <noah@leadboat.com>
Cc: shigeru.hanada@gmail.com, Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
Date: Thu, 28 Aug 2014 18:00:04 +0900
Message-ID: <53FEEF94.6040101@lab.ntt.co.jp> (raw)
In-Reply-To: <20140822025137.GA563733@tornado.leadboat.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/x-diff] foreign_inherit-v15.patch (119.9K, ../53FEEF94.6040101@lab.ntt.co.jp/2-foreign_inherit-v15.patch)
download | inline diff:
*** 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.
</para>
+ <para>+ Note that a foreign table can also inherit from more than one parent+ table (see <xref linkend="ddl-foreign-data">).+ Like normal tables, table inheritance is typically established when+ the foreign table is created, using the <literal>INHERITS</> clause of+ the <xref linkend="sql-createforeigntable"> statement. Alternatively,+ a foreign table which is already defined in a compatible way can have+ a new parent relationship added, using the <literal>INHERIT</literal>+ variant of <xref linkend="sql-alterforeigntable">.+ Similarly an inheritance link can be removed from a child using the+ <literal>NO INHERIT</literal> variant of <command>ALTER FOREIGN TABLE</>.+ <command>CREATE FOREIGN TABLE</command> and+ <command>ALTER FOREIGN TABLE</command> follow the same rules for+ duplicate column merging and rejection that apply during+ <command>CREATE TABLE</command> and <command>ALTER TABLE</command>,+ respectively.+ </para>+
<sect2 id="ddl-inherit-caveats">
<title>Caveats</title>
***************
*** 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 <xref linkend="ddl-inherit">) before
! attempting to set up partitioning.
</para>
<para>
--- 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 <xref linkend="ddl-inherit">) 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.)
</para>
<para>
***************
*** 2434,2441 **** VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
! We will refer to the child tables as partitions, though they
! are in every way normal <productname>PostgreSQL</> tables.
</para>
</listitem>
--- 2455,2462 ----
</para>
<para>
! We will refer to the child tables as partitions, though we assume
! that they are normal <productname>PostgreSQL</> tables.
</para>
</listitem>
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
***************
*** 162,167 **** GetForeignPaths (PlannerInfo *root,
--- 162,198 ----
<para>
<programlisting>
+ ForeignPath *+ ReparameterizeForeignPath (PlannerInfo *root,+ RelOptInfo *baserel,+ Oid foreigntableid,+ ForeignPath *path,+ Relids required_outer);+ </programlisting>+ + Create an access path for a scan on a foreign table using join+ clauses, which is called a <quote>parameterized path</>.+ This is called during query planning.+ The parameters are as for <function>GetForeignRelSize</>, plus+ the <structname>ForeignPath</> (previously produced by+ <function>GetForeignPaths</>), and the IDs of all other tables+ that provide the join clauses.+ </para>+ + <para>+ 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.+ </para>+ + <para>+ See <xref linkend="fdw-planning"> for additional information.+ </para>+ + <para>+ <programlisting>
ForeignScan *
GetForeignPlan (PlannerInfo *root,
RelOptInfo *baserel,
***************
*** 868,877 **** GetForeignServerByName(const char *name, bool missing_ok);
<para>
The FDW callback functions <function>GetForeignRelSize</>,
! <function>GetForeignPaths</>, <function>GetForeignPlan</>, and
! <function>PlanForeignModify</> must fit into the workings of the
! <productname>PostgreSQL</> planner. Here are some notes about what
! they must do.
</para>
<para>
--- 899,908 ----
<para>
The FDW callback functions <function>GetForeignRelSize</>,
! <function>GetForeignPaths</>, <function>ReparameterizeForeignPath</>,
! <function>GetForeignPlan</>, and <function>PlanForeignModify</> must fit
! into the workings of the <productname>PostgreSQL</> planner. Here are
! some notes about what they must do.
</para>
<para>
***************
*** 901,914 **** GetForeignServerByName(const char *name, bool missing_ok);
to initialize it to NULL when the <literal>baserel</> node is created.
It is useful for passing information forward from
<function>GetForeignRelSize</> to <function>GetForeignPaths</> and/or
! <function>GetForeignPaths</> to <function>GetForeignPlan</>, thereby
! avoiding recalculation.
</para>
<para>
! <function>GetForeignPaths</> can identify the meaning of different
! access paths by storing private information in the
! <structfield>fdw_private</> field of <structname>ForeignPath</> nodes.
<structfield>fdw_private</> is declared as a <type>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 <literal>baserel</> node is created.
It is useful for passing information forward from
<function>GetForeignRelSize</> to <function>GetForeignPaths</> and/or
! <function>GetForeignPaths</> to <function>ReparameterizeForeignPath</>
! and/or
! <function>GetForeignPaths</> or <function>ReparameterizeForeignPath</>
! to <function>GetForeignPlan</>, thereby avoiding recalculation.
</para>
<para>
! <function>GetForeignPaths</> or <function>ReparameterizeForeignPath</>
! can identify the meaning of different access paths by storing private
! information in the <structfield>fdw_private</> field of
! <structname>ForeignPath</> nodes.
<structfield>fdw_private</> is declared as a <type>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);
<replaceable>sub_expression</>, which it determines can be executed on
the remote server given the locally-evaluated value of the
<replaceable>sub_expression</>. The actual identification of such a
! clause should happen during <function>GetForeignPaths</>, since it would
! affect the cost estimate for the path. The path's
! <structfield>fdw_private</> field would probably include a pointer to
! the identified clause's <structname>RestrictInfo</> node. Then
<function>GetForeignPlan</> would remove that clause from <literal>scan_clauses</>,
but add the <replaceable>sub_expression</> to <structfield>fdw_exprs</>
to ensure that it gets massaged into executable form. It would probably
--- 985,995 ----
<replaceable>sub_expression</>, which it determines can be executed on
the remote server given the locally-evaluated value of the
<replaceable>sub_expression</>. The actual identification of such a
! clause should happen during <function>GetForeignPaths</> or
! <function>ReparameterizeForeignPath</>, since it would affect the cost
! estimate for the path. The path's <structfield>fdw_private</> field
! would probably include a pointer to the identified clause's
! <structname>RestrictInfo</> node. Then
<function>GetForeignPlan</> would remove that clause from <literal>scan_clauses</>,
but add the <replaceable>sub_expression</> to <structfield>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 ] <replaceable class="PARAMETER">name</replaceab
--- 42,55 ----
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
+ ADD <replaceable class="PARAMETER">table_constraint</replaceable>+ DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
+ INHERIT <replaceable class="PARAMETER">parent_table</replaceable>+ NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
</synopsis>
***************
*** 153,158 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 157,186 ----
</varlistentry>
<varlistentry>
+ <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>+ <listitem>+ <para>+ This form adds a new constraint to a table using the same syntax as+ <xref linkend="SQL-CREATEFOREIGNTABLE">.+ 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.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>+ <listitem>+ <para>+ This form drops the specified constraint on a table.+ If <literal>IF EXISTS</literal> is specified and the constraint+ does not exist, no error is thrown. In this case a notice is issued instead.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
<listitem>
<para>
***************
*** 164,169 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 192,217 ----
</varlistentry>
<varlistentry>
+ <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>+ <listitem>+ <para>+ This form adds the target foreign table as a new child of the specified+ parent table. The parent table must be an ordinary table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>+ <listitem>+ <para>+ This form removes the target foreign table from the list of children of+ the specified parent table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>OWNER</literal></term>
<listitem>
<para>
***************
*** 285,290 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 333,356 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">table_constraint</replaceable></term>+ <listitem>+ <para>+ New table constraint for the table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><replaceable class="PARAMETER">constraint_name</replaceable></term>+ <listitem>+ <para>+ Name of an existing constraint to drop.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>CASCADE</literal></term>
<listitem>
<para>
***************
*** 336,341 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 402,417 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">parent_table</replaceable></term>+ <listitem>+ <para>+ A parent table to associate or de-associate with this foreign table.+ The parent table must be an ordinary table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
<listitem>
<para>
***************
*** 365,374 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
<para>
Consistency with the foreign server is not checked when a column is added
or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, a <literal>NOT NULL</> constraint is
! added, or a column type is changed with <literal>SET DATA TYPE</>. It is
! the user's responsibility to ensure that the table definition matches the
! remote side.
</para>
<para>
--- 441,450 ----
<para>
Consistency with the foreign server is not checked when a column is added
or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, a <literal>NOT NULL</> constraint or
! <literal>CHECK</> constraint is added, or a column type is changed with
! <literal>SET DATA TYPE</>. It is the user's responsibility to ensure that
! the table definition matches the remote side.
</para>
<para>
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 993,998 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 993,1005 ----
</para>
<para>
+ If a table has any descendant tables that are foreign, a recursive+ <literal>SET STORAGE</literal> operation will be rejected since it+ is not permitted to add an <literal>oid</literal> system column to+ foreign tables.+ </para>+ + <para>
The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
and <literal>TABLESPACE</> actions never recurse to descendant tables;
that is, they always act as though <literal>ONLY</> were specified.
***************
*** 1001,1006 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1008,1026 ----
</para>
<para>
+ When adding a <literal>CHECK</> constraint with the <literal>NOT VALID+ </literal> option recursively, an inherited constraint on a descendant+ table that is foreign will be marked valid without checking+ consistency with the foreign server.+ </para>+ + <para>+ A recursive <literal>SET STORAGE</literal> operation will make the+ storage mode for a descendant table's column unchanged if the table is+ foreign.+ </para>+ + <para>
Changing any part of a system catalog table is not permitted.
</para>
*** a/doc/src/sgml/ref/analyze.sgml
--- b/doc/src/sgml/ref/analyze.sgml
***************
*** 200,205 **** ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> [
--- 200,212 ----
</para>
<para>
+ 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+ <command>ANALYZE</command>, the command prints a warning and does nothing.+ </para>+ + <para>
If the table being analyzed is completely empty, <command>ANALYZE</command>
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 ****
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
--- 19,29 ----
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
! | <replaceable>table_constraint</replaceable> }
[, ... ]
] )
+ [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
SERVER <replaceable class="parameter">server_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
***************
*** 30,36 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 32,44 ----
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
+ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
DEFAULT <replaceable>default_expr</replaceable> }
+ + <phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>+ + [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]+ { CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) }
</synopsis>
</refsynopsisdiv>
***************
*** 138,143 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 146,172 ----
</varlistentry>
<varlistentry>
+ <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>+ <listitem>+ <para>+ The <literal>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.+ </para>+ + <para>+ Currently, <literal>CHECK</literal> expressions cannot contain+ subqueries nor refer to variables other than columns of the+ current row. The system column <literal>tableoid</literal>+ may be referenced, but not any other system column.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>DEFAULT
<replaceable>default_expr</replaceable></literal></term>
<listitem>
***************
*** 159,164 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 188,205 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">parent_table</replaceable></term>+ <listitem>+ <para>+ 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 <xref linkend="ddl-inherit"> for the+ details of table inheritance.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><replaceable class="PARAMETER">server_name</replaceable></term>
<listitem>
<para>
***************
*** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 228,251 ----
</refsect1>
+ <refsect1>+ <title>Notes</title>+ + <para>+ 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 <xref linkend="ddl-partitioning">).+ </para>+ + <para>+ Since it is not permitted to add an <literal>oid</> system column to+ foreign tables, the command will be rejected if any parent tables+ have <literal>oid</> system columns.+ </para>+ </refsect1>
<refsect1 id="SQL-CREATEFOREIGNTABLE-examples">
<title>Examples</title>
*** 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 <math.h>
+ #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
[text/x-diff] analyze.patch (611B, ../53FEEF94.6040101@lab.ntt.co.jp/3-analyze.patch)
download | inline diff:
*** 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;
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, noah@leadboat.com, horiguchi.kyotaro@lab.ntt.co.jp
Subject: Re: inherit support for foreign tables
In-Reply-To: <53FEEF94.6040101@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox