(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.8030...@lab.ntt.co.jp
Thanks,
Best regards,
Etsuro Fujita
*** 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
*** 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;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
