On 2015/01/15 16:35, Etsuro Fujita wrote:
> On 2014/12/23 0:36, Tom Lane wrote:
>> Yeah, we need to do something about the PlanRowMark data structure.
>> Aside from the pre-existing issue in postgres_fdw, we need to fix it
>> to support inheritance trees in which more than one rowmark method
>> is being used. That rte.hasForeignChildren thing is a crock,
>
>> The idea I'd had about that was to convert the markType field into a
>> bitmask, so that a parent node's markType could represent the logical
>> OR of the rowmark methods being used by all its children.
> As I said before, that seems to me like a good idea. So I'll update the
> patch based on that if you're okey with it.
Done based on your ideas: (a) add a field to PlanRowMark to record the
original lock strength to fix the postgres_fdw issue and (b) convert its
markType field into a bitmask to support the inheritance trees. I think
that both work well and that (a) is useful for the other places.
Patch attached, which has been created on top of [1].
Best regards,
Etsuro Fujita
[1] http://www.postgresql.org/message-id/54bcbbf8.3020...@lab.ntt.co.jp
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 148,153 **** EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
--- 148,167 ----
SELECT * FROM agg_csv WHERE a < 0;
RESET constraint_exclusion;
+ -- table inheritance tests
+ CREATE TABLE agg (a int2, b float4);
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ SELECT tableoid::regclass, * FROM agg;
+ SELECT tableoid::regclass, * FROM agg_csv;
+ SELECT tableoid::regclass, * FROM ONLY agg;
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ DELETE FROM agg WHERE a = 100;
+ -- but this should be ignored
+ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
+ ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
+ DROP TABLE agg;
+
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 249,254 **** SELECT * FROM agg_csv WHERE a < 0;
--- 249,294 ----
(0 rows)
RESET constraint_exclusion;
+ -- table inheritance tests
+ CREATE TABLE agg (a int2, b float4);
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ SELECT tableoid::regclass, * FROM agg;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ SELECT tableoid::regclass, * FROM agg_csv;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ SELECT tableoid::regclass, * FROM ONLY agg;
+ tableoid | a | b
+ ----------+---+---
+ (0 rows)
+
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ ERROR: cannot update foreign table "agg_csv"
+ DELETE FROM agg WHERE a = 100;
+ ERROR: cannot delete from foreign table "agg_csv"
+ -- but this should be ignored
+ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
+ DROP TABLE agg;
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 3027,3032 **** NOTICE: NEW: (13,"test triggered !")
--- 3027,3544 ----
(1 row)
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ (6 rows)
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE b SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE a SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ (6 rows)
+
+ DELETE FROM a;
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----+----
+ (0 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ DROP TABLE a CASCADE;
+ NOTICE: drop cascades to foreign table b
+ DROP TABLE loct;
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for update;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for share;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------
+ Update on public.bar
+ For public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ -> Hash Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar2.f1 = foo.f1)
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (36 rows)
+
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 101
+ bar | 2 | 102
+ bar | 3 | 103
+ bar | 4 | 4
+ bar2 | 1 | 101
+ bar2 | 2 | 102
+ bar2 | 3 | 103
+ bar2 | 4 | 4
+ (8 rows)
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------
+ Update on public.bar
+ For public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
+ Hash Cond: (foo.f1 = bar.f1)
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Hash
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Merge Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))
+ Merge Cond: (bar2.f1 = foo.f1)
+ -> Sort
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Sort Key: bar2.f1
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Sort
+ Output: (ROW(foo.f1)), foo.f1
+ Sort Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ (44 rows)
+
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 201
+ bar | 2 | 202
+ bar | 3 | 203
+ bar | 4 | 104
+ bar2 | 1 | 201
+ bar2 | 2 | 202
+ bar2 | 3 | 203
+ bar2 | 4 | 104
+ (8 rows)
+
+ drop table foo cascade;
+ NOTICE: drop cascades to foreign table foo2
+ drop table bar cascade;
+ NOTICE: drop cascades to foreign table bar2
+ drop table loct1;
+ drop table loct2;
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ -- Test TRUNCATE
+ truncate ptbl;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 2 | bar
+ (1 row)
+
+ select * from only ptbl;
+ a | b
+ ---+---
+ (0 rows)
+
+ select * from locc;
+ a | b
+ ---+---
+ (0 rows)
+
+ select * from remc;
+ a | b
+ ---+-----
+ 2 | bar
+ (1 row)
+
+ truncate remc; -- ERROR
+ ERROR: "remc" is not a table
+ drop table ptbl cascade;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table locc
+ drop cascades to foreign table remc
+ drop table ltbl;
+ -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 34,39 ****
--- 34,40 ----
#include "parser/parsetree.h"
#include "utils/builtins.h"
#include "utils/guc.h"
+ #include "utils/lockclausestrength.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
***************
*** 822,828 **** postgresGetForeignPlan(PlannerInfo *root,
}
else
{
! RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid);
if (rc)
{
--- 823,829 ----
}
else
{
! PlanRowMark *rc = get_plan_rowmark(root->rowMarks, baserel->relid);
if (rc)
{
***************
*** 837,842 **** postgresGetForeignPlan(PlannerInfo *root,
--- 838,846 ----
*/
switch (rc->strength)
{
+ case LCS_NOLOCK:
+ /* No locking needed */
+ break;
case LCS_FORKEYSHARE:
case LCS_FORSHARE:
appendStringInfoString(&sql, " FOR SHARE");
***************
*** 1682,1691 **** postgresExplainForeignModify(ModifyTableState *mtstate,
{
if (es->verbose)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
! ExplainPropertyText("Remote SQL", sql, es);
}
}
--- 1686,1717 ----
{
if (es->verbose)
{
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
+ Index rti = list_nth_int(node->resultRelations, subplan_index);
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
! if (rti != node->nominalRelation)
! {
! /*
! * Inherited UPDATE/DELETE case; show the SQL statement together
! * with the target relation.
! */
! ExplainBeginInheritedForeignModify(node, subplan_index, es);
! ExplainPropertyText("Remote SQL", sql, es);
! ExplainEndInheritedForeignModify(node, subplan_index, es);
! }
! else
! {
! /*
! * Non-inherited normal case.
! *
! * Note: foreign tables can't be the parent of an inheritance set.
! * So if the RT index matches nominalRelation, we are in a non-
! * inherited normal case.
! */
! ExplainPropertyText("Remote SQL", sql, es);
! }
}
}
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 666,671 **** UPDATE rem1 SET f2 = 'testo';
--- 666,822 ----
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE b SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DELETE FROM a;
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DROP TABLE a CASCADE;
+ DROP TABLE loct;
+
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ select * from bar where f1 in (select f1 from foo) for update;
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ select * from bar where f1 in (select f1 from foo) for share;
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ drop table foo cascade;
+ drop table bar cascade;
+ drop table loct1;
+ drop table loct2;
+
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ -- Test TRUNCATE
+ truncate ptbl;
+ select * from ptbl;
+ select * from only ptbl;
+ select * from locc;
+ select * from remc;
+ truncate remc; -- ERROR
+
+ drop table ptbl cascade;
+ drop table ltbl;
+
+ -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 2503,2508 **** VALUES ('Albany', NULL, NULL, 'NY');
--- 2503,2526 ----
further privileges to be granted.
</para>
+ <para>
+ Note that a foreign table can also inherit from more than one parent
+ table (see <xref linkend="ddl-foreign-data"> for an overview of
+ foreign tables).
+ 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</> and <command>ALTER FOREIGN TABLE</>
+ follow the same rules for duplicate column merging and rejection as
+ <command>CREATE TABLE</> and <command>ALTER TABLE</>, respectively.
+ </para>
+
<sect2 id="ddl-inherit-caveats">
<title>Caveats</title>
***************
*** 2650,2656 **** VALUES ('Albany', NULL, NULL, 'NY');
table of a single parent table. The parent table itself is normally
empty; it exists just to represent the entire data set. You should be
familiar with inheritance (see <xref linkend="ddl-inherit">) before
! attempting to set up partitioning.
</para>
<para>
--- 2668,2677 ----
table of a single parent table. The parent table itself is normally
empty; it exists just to represent the entire data set. You should be
familiar with inheritance (see <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>
***************
*** 2713,2720 **** 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>
--- 2734,2741 ----
</para>
<para>
! We will refer to the child tables as partitions, though we assume
! here that they are normal <productname>PostgreSQL</> tables.
</para>
</listitem>
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
***************
*** 48,53 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 48,55 ----
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>
***************
*** 188,193 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 190,215 ----
</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>
***************
*** 384,389 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 406,421 ----
</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>
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 1011,1016 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1011,1029 ----
</para>
<para>
+ A recursive <literal>SET STORAGE</literal> operation will make
+ the storage mode unchanged for any column of descendant tables
+ that are foreign.
+ </para>
+
+ <para>
+ A recursive <literal>SET WITH OIDS</literal> operation will be
+ rejected if any of descendant tables is foreign, 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.
***************
*** 1019,1024 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1032,1045 ----
</para>
<para>
+ When adding a <literal>CHECK</> constraint with the <literal>NOT VALID
+ </literal> option recursively, the inherited constraint on a descendant
+ table that is foreign will be marked valid without checking consistency
+ with the foreign server. It is the user's resposibility to ensure that
+ the constraint definition matches the remote side.
+ </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 any of 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
***************
*** 23,28 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 23,29 ----
| <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>' [, ... ] ) ]
***************
*** 121,126 **** CHECK ( <replaceable class="PARAMETER">expression</replaceable> )
--- 122,158 ----
</varlistentry>
<varlistentry>
+ <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ The optional <literal>INHERITS</> clause specifies a list of
+ tables from which the new foreign table automatically inherits
+ all columns. See the similar form of
+ <xref linkend="sql-createtable"> for more details.
+ </para>
+
+ <para>
+ Note that unlike <command>CREATE TABLE</>, column
+ <literal>STORAGE</> settings are not copied from parent tables,
+ resulting in the copied columns in the new foreign table having
+ type-specific default settings.
+ </para>
+ </listitem>
+ </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
+ more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>NOT NULL</></term>
<listitem>
<para>
***************
*** 249,254 **** CHECK ( <replaceable class="PARAMETER">expression</replaceable> )
--- 281,292 ----
responsibility to ensure that the constraint definition matches
reality.
</para>
+
+ <para>
+ Since it is not permitted to add an <literal>oid</> system column to
+ foreign tables, the command will be rejected if any of parent tables
+ has an <literal>oid</> system column.
+ </para>
</refsect1>
<refsect1 id="SQL-CREATEFOREIGNTABLE-examples">
*** a/doc/src/sgml/ref/truncate.sgml
--- b/doc/src/sgml/ref/truncate.sgml
***************
*** 179,184 **** TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [
--- 179,189 ----
This is similar to the usual behavior of <function>currval()</> after
a failed transaction.
</para>
+
+ <para>
+ A recursive <command>TRUNCATE</> ignores any descendant tables that are
+ foreign, and applies the operation to descendant tables that are normal.
+ </para>
</refsect1>
<refsect1>
*** a/src/backend/commands/analyze.c
--- b/src/backend/commands/analyze.c
***************
*** 56,61 ****
--- 56,69 ----
#include "utils/tqual.h"
+ /* Possible modes for ANALYZE */
+ typedef enum
+ {
+ ANL_MODE_ALL, /* Analyze all relations */
+ ANL_MODE_SINGLE, /* Analyze a specific relation */
+ ANL_MODE_AUTOVACUUM /* Autovacuum worker */
+ } AnalyzeMode;
+
/* Data structure for Algorithm S from Knuth 3.4.2 */
typedef struct
{
***************
*** 81,86 **** typedef struct AnlIndexData
--- 89,95 ----
int default_statistics_target = 100;
/* A few variables that don't seem worth passing around as parameters */
+ static AnalyzeMode anl_mode;
static MemoryContext anl_context = NULL;
static BufferAccessStrategy vac_strategy;
***************
*** 102,107 **** static int acquire_sample_rows(Relation onerel, int elevel,
--- 111,117 ----
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
static int compare_rows(const void *a, const void *b);
+ static bool has_foreign_children(Oid parentOID, List *tableOIDs);
static int acquire_inherited_sample_rows(Relation onerel, int elevel,
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
***************
*** 130,135 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
--- 140,154 ----
elevel = DEBUG2;
/* Set up static variables */
+ if (IsAutoVacuumWorkerProcess())
+ anl_mode = ANL_MODE_AUTOVACUUM;
+ else
+ {
+ if (!vacstmt->relation)
+ anl_mode = ANL_MODE_ALL;
+ else
+ anl_mode = ANL_MODE_SINGLE;
+ }
vac_strategy = bstrategy;
/*
***************
*** 297,305 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * If we supported foreign tables in inheritance trees,
! * acquire_inherited_sample_rows would need to determine the appropriate
! * acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
--- 316,323 ----
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * For the inherited case, acquire_inherited_sample_rows determines the
! * appropriate acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
***************
*** 1444,1454 **** compare_rows(const void *a, const void *b)
/*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1462,1512 ----
/*
+ * Detect wether the inheritance tree contains any foreign tables
+ */
+ static bool
+ has_foreign_children(Oid parentOID, List *tableOIDs)
+ {
+ bool found;
+ ListCell *lc;
+
+ /* There are no children */
+ if (list_length(tableOIDs) < 2)
+ return false;
+
+ found = false;
+ foreach(lc, tableOIDs)
+ {
+ Oid childOID = lfirst_oid(lc);
+ Relation childrel;
+
+ /* Parent should not be foreign */
+ if (childOID == parentOID)
+ continue;
+
+ /* We already got the needed lock */
+ childrel = heap_open(childOID, NoLock);
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /* Found it */
+ found = true;
+ }
+ heap_close(childrel, NoLock);
+
+ if (found)
+ return true;
+ }
+ return false;
+ }
+
+ /*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children, or if
! * inheritance tree contains any foreign tables and we are doing an
! * ANALYZE-with-no-parameter or in an autovacuum process.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
***************
*** 1457,1462 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1515,1521 ----
{
List *tableOIDs;
Relation *rels;
+ AcquireSampleRowsFunc *acquirefunc;
double *relblocks;
double totalblocks;
int numrows,
***************
*** 1491,1500 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1550,1580 ----
}
/*
+ * If we are doing an ANALYZE-with-no-parameter or in an autovacuum process
+ * and inheritance tree contains any foreign tables, then fail.
+ */
+ if (anl_mode != ANL_MODE_SINGLE)
+ {
+ if (has_foreign_children(RelationGetRelid(onerel), tableOIDs))
+ {
+ ereport(elevel,
+ (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains foreign tables",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel)),
+ errhint("Try ANALYZE \"%s.%s\" for the inheritance statistics.",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel))));
+ return 0;
+ }
+ }
+
+ /*
* Count the blocks in all the relations. The result could overflow
* BlockNumber, so we use double arithmetic.
*/
rels = (Relation *) palloc(list_length(tableOIDs) * sizeof(Relation));
+ acquirefunc = (AcquireSampleRowsFunc *) palloc(list_length(tableOIDs)
+ * sizeof(AcquireSampleRowsFunc));
relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double));
totalblocks = 0;
nrels = 0;
***************
*** 1516,1522 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
}
rels[nrels] = childrel;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
totalblocks += relblocks[nrels];
nrels++;
}
--- 1596,1631 ----
}
rels[nrels] = childrel;
!
! if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
! {
! FdwRoutine *fdwroutine;
! BlockNumber relpages = 0;
! bool ok = false;
!
! /* Check whether the FDW supports analysis */
! fdwroutine = GetFdwRoutineForRelation(childrel, false);
! if (fdwroutine->AnalyzeForeignTable != NULL)
! ok = fdwroutine->AnalyzeForeignTable(childrel,
! &acquirefunc[nrels],
! &relpages);
! if (!ok)
! {
! /* Give up if the FDW doesn't support analysis */
! ereport(WARNING,
! (errmsg("skipping analyze of inheritance tree \"%s\" --- cannot analyze foreign table \"%s\"",
! RelationGetRelationName(onerel),
! RelationGetRelationName(childrel))));
! return 0;
! }
! relblocks[nrels] = (double) relpages;
! }
! else
! {
! acquirefunc[nrels] = acquire_sample_rows;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
! }
!
totalblocks += relblocks[nrels];
nrels++;
}
***************
*** 1534,1539 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1643,1649 ----
{
Relation childrel = rels[i];
double childblocks = relblocks[i];
+ AcquireSampleRowsFunc childacquirefunc = acquirefunc[i];
if (childblocks > 0)
{
***************
*** 1549,1560 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = acquire_sample_rows(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
--- 1659,1670 ----
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = childacquirefunc(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 2302,2331 **** ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
}
/*
* Show extra information for a ModifyTable node
*/
static void
show_modifytable_info(ModifyTableState *mtstate, ExplainState *es)
{
! FdwRoutine *fdwroutine = mtstate->resultRelInfo->ri_FdwRoutine;
! /*
! * If the first target relation is a foreign table, call its FDW to
! * display whatever additional fields it wants to. For now, we ignore the
! * possibility of other targets being foreign tables, although the API for
! * ExplainForeignModify is designed to allow them to be processed.
! */
! if (fdwroutine != NULL &&
! fdwroutine->ExplainForeignModify != NULL)
{
! ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
! List *fdw_private = (List *) linitial(node->fdwPrivLists);
! fdwroutine->ExplainForeignModify(mtstate,
! mtstate->resultRelInfo,
! fdw_private,
! 0,
! es);
}
}
--- 2302,2399 ----
}
/*
+ * Open a group for inherited foreign modification
+ */
+ void
+ ExplainBeginInheritedForeignModify(ModifyTable *plan, int subplan_index,
+ ExplainState *es)
+ {
+ char *objectname;
+ char *namespace;
+ char *refname;
+ Index rti;
+ RangeTblEntry *rte;
+
+ Assert(subplan_index > 0);
+
+ /* Open a group */
+ ExplainOpenGroup("Foreign Modification", "Foreign Modification", true, es);
+
+ /* Show the target relation */
+ Assert(plan->resultRelations != NIL);
+ rti = list_nth_int(plan->resultRelations, subplan_index);
+ rte = rt_fetch(rti, es->rtable);
+ /* Assert it's on a real relation */
+ Assert(rte->rtekind == RTE_RELATION);
+ objectname = get_rel_name(rte->relid);
+ namespace = get_namespace_name(get_rel_namespace(rte->relid));
+ refname = (char *) list_nth(es->rtable_names, rti - 1);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfoString(es->str, "For");
+ appendStringInfo(es->str, " %s.%s", quote_identifier(namespace),
+ quote_identifier(objectname));
+ if (strcmp(refname, objectname) != 0)
+ appendStringInfo(es->str, " %s", quote_identifier(refname));
+ appendStringInfoChar(es->str, '\n');
+ es->indent++;
+ }
+ else
+ {
+ ExplainPropertyText("Relation Name", objectname, es);
+ ExplainPropertyText("Schema", namespace, es);
+ ExplainPropertyText("Alias", refname, es);
+ }
+ }
+
+ /*
+ * Close a group for inherited foreign modification
+ */
+ void
+ ExplainEndInheritedForeignModify(ModifyTable *plan, int subplan_index,
+ ExplainState *es)
+ {
+ Assert(subplan_index > 0);
+
+ /* Undo the indentation we added in text format */
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ es->indent--;
+
+ /* Close a group */
+ ExplainCloseGroup("Foreign Modification", "Foreign Modification", true, es);
+ }
+
+ /*
* Show extra information for a ModifyTable node
*/
static void
show_modifytable_info(ModifyTableState *mtstate, ExplainState *es)
{
! ModifyTable *plan = (ModifyTable *) mtstate->ps.plan;
! int j;
! for (j = 0; j < mtstate->mt_nplans; j++)
{
! ResultRelInfo *resultRelInfo = mtstate->resultRelInfo + j;
! FdwRoutine *fdwroutine = resultRelInfo->ri_FdwRoutine;
! /*
! * If the target relation is a foreign table, call its FDW to display
! * whatever additional fields it wants to.
! */
! if (fdwroutine != NULL &&
! fdwroutine->ExplainForeignModify != NULL)
! {
! List *fdw_private = (List *) list_nth(plan->fdwPrivLists, j);
!
! fdwroutine->ExplainForeignModify(mtstate,
! resultRelInfo,
! fdw_private,
! j,
! es);
! }
}
}
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 318,324 **** static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel);
static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode);
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
LOCKMODE lockmode);
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
--- 318,325 ----
static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse,
! bool include_foreign, LOCKMODE lockmode);
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
LOCKMODE lockmode);
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
***************
*** 568,573 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
--- 569,598 ----
stmt->relation->relpersistence,
&inheritOids, &old_constraints, &parentOidCount);
+ if (relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /*
+ * Don't allow a foreign table to inherit from parents that have OID
+ * system columns.
+ */
+ if (parentOidCount > 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot inherit from relation with OIDs")));
+
+ /*
+ * Reset the storage parameter for inherited attributes that have
+ * non-default values.
+ */
+ foreach(listptr, schema)
+ {
+ ColumnDef *colDef = lfirst(listptr);
+
+ if (colDef->storage != 0)
+ colDef->storage = 0;
+ }
+ }
+
/*
* Create a tuple descriptor from the relation schema. Note that this
* deals with column names, types, and NOT NULL constraints, but not
***************
*** 1025,1030 **** ExecuteTruncate(TruncateStmt *stmt)
--- 1050,1061 ----
/* find_all_inheritors already got lock */
rel = heap_open(childrelid, NoLock);
+ /* ignore if the rel is a foreign table */
+ if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ heap_close(rel, NoLock);
+ continue;
+ }
truncate_check_rel(rel);
rels = lappend(rels, rel);
relids = lappend_oid(relids, childrelid);
***************
*** 3102,3125 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
* rules.
*/
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_DROP;
break;
case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* Performs own permission checks */
ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode);
pass = AT_PASS_MISC;
--- 3133,3160 ----
* rules.
*/
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_DROP;
break;
case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* Performs own permission checks */
ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode);
pass = AT_PASS_MISC;
***************
*** 3132,3138 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
--- 3167,3174 ----
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
! /* Don't recurse to child tables that are foreign */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, false, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
***************
*** 3254,3264 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
! ATSimplePermissions(rel, ATT_TABLE);
/* This command never recurses */
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
--- 3290,3306 ----
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* This command never recurses */
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
+ case AT_DropInherit: /* NO INHERIT */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
***************
*** 3292,3298 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
- case AT_DropInherit: /* NO INHERIT */
case AT_AddOf: /* OF */
case AT_DropOf: /* NOT OF */
case AT_EnableRowSecurity:
--- 3334,3339 ----
***************
*** 4292,4298 **** ATWrongRelkindError(Relation rel, int allowed_targets)
*/
static void
ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode)
{
/*
* Propagate to children if desired. Non-table relations never have
--- 4333,4340 ----
*/
static void
ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse,
! bool include_foreign, LOCKMODE lockmode)
{
/*
* Propagate to children if desired. Non-table relations never have
***************
*** 4320,4327 **** ATSimpleRecursion(List **wqueue, Relation rel,
continue;
/* find_all_inheritors already got lock */
childrel = relation_open(childrelid, NoLock);
! CheckTableNotInUse(childrel, "ALTER TABLE");
! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode);
relation_close(childrel, NoLock);
}
}
--- 4362,4373 ----
continue;
/* find_all_inheritors already got lock */
childrel = relation_open(childrelid, NoLock);
! if (childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE
! || include_foreign)
! {
! CheckTableNotInUse(childrel, "ALTER TABLE");
! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode);
! }
relation_close(childrel, NoLock);
}
}
***************
*** 4611,4617 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
--- 4657,4663 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
***************
*** 4907,4912 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 4953,4963 ----
/* find_inheritance_children already got lock */
childrel = heap_open(childrelid, NoLock);
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE && isOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot add OID column to foreign table \"%s\"",
+ RelationGetRelationName(childrel))));
CheckTableNotInUse(childrel, "ALTER TABLE");
/* Find or create work queue entry for this table */
***************
*** 5507,5513 **** ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
/*
* get the number of the attribute
--- 5558,5564 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* get the number of the attribute
***************
*** 5899,5905 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
/*
* Call AddRelationNewConstraints to do the work, making sure it works on
--- 5950,5956 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* Call AddRelationNewConstraints to do the work, making sure it works on
***************
*** 5910,5918 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* omitted from the returned list, which is what we want: we do not need
* to do any validation work. That can only happen at child tables,
* though, since we disallow merging at the top level.
*/
newcons = AddRelationNewConstraints(rel, NIL,
! list_make1(copyObject(constr)),
recursing, /* allow_merge */
!recursing, /* is_local */
is_readd); /* is_internal */
--- 5961,5977 ----
* omitted from the returned list, which is what we want: we do not need
* to do any validation work. That can only happen at child tables,
* though, since we disallow merging at the top level.
+ *
+ * When propagating a NOT VALID option to children that are foreign tables,
+ * we quietly ignore the option. Note that this is safe because foreign
+ * tables don't have any children.
*/
+ constr = (Constraint *) copyObject(constr);
+ if (tab->relkind == RELKIND_FOREIGN_TABLE &&
+ constr->skip_validation && recursing)
+ constr->skip_validation = false;
newcons = AddRelationNewConstraints(rel, NIL,
! list_make1(constr),
recursing, /* allow_merge */
!recursing, /* is_local */
is_readd); /* is_internal */
***************
*** 7399,7405 **** ATExecDropConstraint(Relation rel, const char *constrName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
--- 7458,7464 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
***************
*** 7734,7740 **** ATPrepAlterColumnType(List **wqueue,
* alter would put them out of step.
*/
if (recurse)
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
else if (!recursing &&
find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
ereport(ERROR,
--- 7793,7802 ----
* alter would put them out of step.
*/
if (recurse)
! {
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
! }
else if (!recursing &&
find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
ereport(ERROR,
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 1920,1940 **** ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist)
aerm->rowmark = erm;
/* Look up the resjunk columns associated with this rowmark */
if (erm->relation &&
erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
Assert(erm->markType != ROW_MARK_COPY);
- /* if child rel, need tableoid */
- if (erm->rti != erm->prti)
- {
- snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId);
- aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist,
- resname);
- if (!AttributeNumberIsValid(aerm->toidAttNo))
- elog(ERROR, "could not find junk %s column", resname);
- }
-
/* always need ctid for real relations */
snprintf(resname, sizeof(resname), "ctid%u", erm->rowmarkId);
aerm->ctidAttNo = ExecFindJunkAttributeInTlist(targetlist,
--- 1920,1941 ----
aerm->rowmark = erm;
/* Look up the resjunk columns associated with this rowmark */
+
+ /* if child rel, need tableoid */
+ if (erm->rti != erm->prti)
+ {
+ snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId);
+ aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist,
+ resname);
+ if (!AttributeNumberIsValid(aerm->toidAttNo))
+ elog(ERROR, "could not find junk %s column", resname);
+ }
+
if (erm->relation &&
erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
Assert(erm->markType != ROW_MARK_COPY);
/* always need ctid for real relations */
snprintf(resname, sizeof(resname), "ctid%u", erm->rowmarkId);
aerm->ctidAttNo = ExecFindJunkAttributeInTlist(targetlist,
***************
*** 2385,2416 **** EvalPlanQualFetchRowMarks(EPQState *epqstate)
/* clear any leftover test tuple for this rel */
EvalPlanQualSetTuple(epqstate, erm->rti, NULL);
! if (erm->relation &&
! erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
{
! Buffer buffer;
! Assert(erm->markType == ROW_MARK_REFERENCE);
! /* if child rel, must check whether it produced this row */
! if (erm->rti != erm->prti)
{
! Oid tableoid;
! datum = ExecGetJunkAttribute(epqstate->origslot,
! aerm->toidAttNo,
! &isNull);
! /* non-locked rels could be on the inside of outer joins */
! if (isNull)
! continue;
! tableoid = DatumGetObjectId(datum);
! if (tableoid != RelationGetRelid(erm->relation))
! {
! /* this child is inactive right now */
! continue;
! }
! }
/* fetch the tuple's ctid */
datum = ExecGetJunkAttribute(epqstate->origslot,
--- 2386,2419 ----
/* clear any leftover test tuple for this rel */
EvalPlanQualSetTuple(epqstate, erm->rti, NULL);
! /* if child rel, must check whether it produced this row */
! if (erm->rti != erm->prti)
{
! Oid tableoid;
! Assert(erm->relation);
!
! datum = ExecGetJunkAttribute(epqstate->origslot,
! aerm->toidAttNo,
! &isNull);
! /* non-locked rels could be on the inside of outer joins */
! if (isNull)
! continue;
! tableoid = DatumGetObjectId(datum);
! if (tableoid != RelationGetRelid(erm->relation))
{
! /* this child is inactive right now */
! continue;
! }
! }
! if (erm->relation &&
! erm->relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
! {
! Buffer buffer;
! Assert(erm->markType == ROW_MARK_REFERENCE);
/* fetch the tuple's ctid */
datum = ExecGetJunkAttribute(epqstate->origslot,
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 990,995 **** _copyPlanRowMark(const PlanRowMark *from)
--- 990,996 ----
COPY_SCALAR_FIELD(prti);
COPY_SCALAR_FIELD(rowmarkId);
COPY_SCALAR_FIELD(markType);
+ COPY_SCALAR_FIELD(strength);
COPY_SCALAR_FIELD(waitPolicy);
COPY_SCALAR_FIELD(isParent);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 850,856 **** _outPlanRowMark(StringInfo str, const PlanRowMark *node)
WRITE_UINT_FIELD(rti);
WRITE_UINT_FIELD(prti);
WRITE_UINT_FIELD(rowmarkId);
! WRITE_ENUM_FIELD(markType, RowMarkType);
WRITE_BOOL_FIELD(waitPolicy);
WRITE_BOOL_FIELD(isParent);
}
--- 850,857 ----
WRITE_UINT_FIELD(rti);
WRITE_UINT_FIELD(prti);
WRITE_UINT_FIELD(rowmarkId);
! WRITE_UINT_FIELD(markType);
! WRITE_ENUM_FIELD(strength, LockClauseStrength);
WRITE_BOOL_FIELD(waitPolicy);
WRITE_BOOL_FIELD(isParent);
}
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 2249,2254 **** preprocess_rowmarks(PlannerInfo *root)
--- 2249,2255 ----
newrc->markType = ROW_MARK_KEYSHARE;
break;
}
+ newrc->strength = rc->strength;
newrc->waitPolicy = rc->waitPolicy;
newrc->isParent = false;
***************
*** 2262,2267 **** preprocess_rowmarks(PlannerInfo *root)
--- 2263,2269 ----
foreach(l, parse->rtable)
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(l);
+ RowMarkClause *rc;
PlanRowMark *newrc;
i++;
***************
*** 2277,2282 **** preprocess_rowmarks(PlannerInfo *root)
--- 2279,2289 ----
newrc->markType = ROW_MARK_REFERENCE;
else
newrc->markType = ROW_MARK_COPY;
+ rc = get_parse_rowmark(parse, i);
+ if (rc)
+ newrc->strength = rc->strength;
+ else
+ newrc->strength = LCS_NOLOCK;
newrc->waitPolicy = LockWaitBlock; /* doesn't matter */
newrc->isParent = false;
*** a/src/backend/optimizer/prep/prepsecurity.c
--- b/src/backend/optimizer/prep/prepsecurity.c
***************
*** 229,257 **** expand_security_qual(PlannerInfo *root, List *tlist, int rt_index,
rc = get_plan_rowmark(root->rowMarks, rt_index);
if (rc != NULL)
{
! switch (rc->markType)
! {
! case ROW_MARK_EXCLUSIVE:
! applyLockingClause(subquery, 1, LCS_FORUPDATE,
! rc->waitPolicy, false);
! break;
! case ROW_MARK_NOKEYEXCLUSIVE:
! applyLockingClause(subquery, 1, LCS_FORNOKEYUPDATE,
! rc->waitPolicy, false);
! break;
! case ROW_MARK_SHARE:
! applyLockingClause(subquery, 1, LCS_FORSHARE,
! rc->waitPolicy, false);
! break;
! case ROW_MARK_KEYSHARE:
! applyLockingClause(subquery, 1, LCS_FORKEYSHARE,
! rc->waitPolicy, false);
! break;
! case ROW_MARK_REFERENCE:
! case ROW_MARK_COPY:
! /* No locking needed */
! break;
! }
root->rowMarks = list_delete(root->rowMarks, rc);
}
--- 229,237 ----
rc = get_plan_rowmark(root->rowMarks, rt_index);
if (rc != NULL)
{
! if (rc->strength != LCS_NOLOCK)
! applyLockingClause(subquery, 1,
! rc->strength, rc->waitPolicy, false);
root->rowMarks = list_delete(root->rowMarks, rc);
}
*** a/src/backend/optimizer/prep/preptlist.c
--- b/src/backend/optimizer/prep/preptlist.c
***************
*** 92,98 **** preprocess_targetlist(PlannerInfo *root, List *tlist)
if (rc->rti != rc->prti)
continue;
! if (rc->markType != ROW_MARK_COPY)
{
/* It's a regular table, so fetch its TID */
var = makeVar(rc->rti,
--- 92,98 ----
if (rc->rti != rc->prti)
continue;
! if (rc->markType & ~ROW_MARK_COPY)
{
/* It's a regular table, so fetch its TID */
var = makeVar(rc->rti,
***************
*** 123,128 **** preprocess_targetlist(PlannerInfo *root, List *tlist)
--- 123,144 ----
pstrdup(resname),
true);
tlist = lappend(tlist, tle);
+
+ /* if containing foreign tables, fetch the whole row too */
+ if (rc->markType & ROW_MARK_COPY)
+ {
+ /* Not a table, so we need the whole row as a junk var */
+ var = makeWholeRowVar(rt_fetch(rc->rti, range_table),
+ rc->rti,
+ 0,
+ false);
+ snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);
+ tle = makeTargetEntry((Expr *) var,
+ list_length(tlist) + 1,
+ pstrdup(resname),
+ true);
+ tlist = lappend(tlist, tle);
+ }
}
}
else
*** a/src/backend/optimizer/prep/prepunion.c
--- b/src/backend/optimizer/prep/prepunion.c
***************
*** 1237,1242 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1237,1243 ----
LOCKMODE lockmode;
List *inhOIDs;
List *appinfos;
+ RowMarkType alltypes;
ListCell *l;
/* Does RT entry allow inheritance? */
***************
*** 1309,1314 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1310,1316 ----
/* Scan the inheritance set and expand it */
appinfos = NIL;
+ alltypes = 0;
foreach(l, inhOIDs)
{
Oid childOID = lfirst_oid(l);
***************
*** 1338,1348 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* Build an RTE for the child, and attach to query's rangetable list.
* We copy most fields of the parent's RTE, but replace relation OID,
! * and set inh = false. Also, set requiredPerms to zero since all
! * required permissions checks are done on the original RTE.
*/
childrte = copyObject(rte);
childrte->relid = childOID;
childrte->inh = false;
childrte->requiredPerms = 0;
parse->rtable = lappend(parse->rtable, childrte);
--- 1340,1351 ----
/*
* Build an RTE for the child, and attach to query's rangetable list.
* We copy most fields of the parent's RTE, but replace relation OID,
! * relkind and set inh = false. Also, set requiredPerms to zero since
! * all required permissions checks are done on the original RTE.
*/
childrte = copyObject(rte);
childrte->relid = childOID;
+ childrte->relkind = newrelation->rd_rel->relkind;
childrte->inh = false;
childrte->requiredPerms = 0;
parse->rtable = lappend(parse->rtable, childrte);
***************
*** 1388,1396 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrc->rti = childRTindex;
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
! newrc->markType = oldrc->markType;
newrc->waitPolicy = oldrc->waitPolicy;
newrc->isParent = false;
root->rowMarks = lappend(root->rowMarks, newrc);
}
--- 1391,1404 ----
newrc->rti = childRTindex;
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
! if (childrte->relkind != RELKIND_FOREIGN_TABLE)
! newrc->markType = oldrc->markType;
! else
! newrc->markType = ROW_MARK_COPY;
! newrc->strength = oldrc->strength;
newrc->waitPolicy = oldrc->waitPolicy;
newrc->isParent = false;
+ alltypes |= newrc->markType;
root->rowMarks = lappend(root->rowMarks, newrc);
}
***************
*** 1416,1421 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1424,1433 ----
/* Otherwise, OK to add to root->append_rel_list */
root->append_rel_list = list_concat(root->append_rel_list, appinfos);
+
+ /* and replace the parent markType with alltypes */
+ if (oldrc)
+ oldrc->markType = alltypes;
}
/*
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 4363,4394 **** AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o
CreateForeignTableStmt:
CREATE FOREIGN TABLE qualified_name
'(' OptTableElementList ')'
! SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$4->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $4;
n->base.tableElts = $6;
! n->base.inhRelations = NIL;
n->base.if_not_exists = false;
/* FDW-specific data */
! n->servername = $9;
! n->options = $10;
$$ = (Node *) n;
}
| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
'(' OptTableElementList ')'
! SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$7->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $7;
n->base.tableElts = $9;
! n->base.inhRelations = NIL;
n->base.if_not_exists = true;
/* FDW-specific data */
! n->servername = $12;
! n->options = $13;
$$ = (Node *) n;
}
;
--- 4363,4394 ----
CreateForeignTableStmt:
CREATE FOREIGN TABLE qualified_name
'(' OptTableElementList ')'
! OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$4->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $4;
n->base.tableElts = $6;
! n->base.inhRelations = $8;
n->base.if_not_exists = false;
/* FDW-specific data */
! n->servername = $10;
! n->options = $11;
$$ = (Node *) n;
}
| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
'(' OptTableElementList ')'
! OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$7->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $7;
n->base.tableElts = $9;
! n->base.inhRelations = $11;
n->base.if_not_exists = true;
/* FDW-specific data */
! n->servername = $13;
! n->options = $14;
$$ = (Node *) n;
}
;
*** a/src/backend/tcop/utility.c
--- b/src/backend/tcop/utility.c
***************
*** 2352,2373 **** CreateCommandTag(Node *parsetree)
else if (stmt->rowMarks != NIL)
{
/* not 100% but probably close enough */
! switch (((PlanRowMark *) linitial(stmt->rowMarks))->markType)
{
! case ROW_MARK_EXCLUSIVE:
tag = "SELECT FOR UPDATE";
break;
! case ROW_MARK_NOKEYEXCLUSIVE:
tag = "SELECT FOR NO KEY UPDATE";
break;
! case ROW_MARK_SHARE:
tag = "SELECT FOR SHARE";
break;
! case ROW_MARK_KEYSHARE:
tag = "SELECT FOR KEY SHARE";
break;
! case ROW_MARK_REFERENCE:
! case ROW_MARK_COPY:
tag = "SELECT";
break;
default:
--- 2352,2372 ----
else if (stmt->rowMarks != NIL)
{
/* not 100% but probably close enough */
! switch (((PlanRowMark *) linitial(stmt->rowMarks))->strength)
{
! case LCS_FORUPDATE:
tag = "SELECT FOR UPDATE";
break;
! case LCS_FORNOKEYUPDATE:
tag = "SELECT FOR NO KEY UPDATE";
break;
! case LCS_FORSHARE:
tag = "SELECT FOR SHARE";
break;
! case LCS_FORKEYSHARE:
tag = "SELECT FOR KEY SHARE";
break;
! case LCS_NOLOCK:
tag = "SELECT";
break;
default:
*** a/src/backend/utils/cache/plancache.c
--- b/src/backend/utils/cache/plancache.c
***************
*** 1488,1493 **** AcquireExecutorLocks(List *stmt_list, bool acquire)
--- 1488,1497 ----
if (rte->rtekind != RTE_RELATION)
continue;
+ /* Ignore inheritance parents. */
+ if (rte->inh)
+ continue;
+
/*
* Acquire the appropriate type of lock on each relation OID. Note
* that we don't actually try to open the rel, and hence will not
*** a/src/include/commands/explain.h
--- b/src/include/commands/explain.h
***************
*** 92,95 **** extern void ExplainPropertyLong(const char *qlabel, long value,
--- 92,100 ----
extern void ExplainPropertyFloat(const char *qlabel, double value, int ndigits,
ExplainState *es);
+ extern void ExplainBeginInheritedForeignModify(ModifyTable *plan, int subplan_index,
+ ExplainState *es);
+ extern void ExplainEndInheritedForeignModify(ModifyTable *plan, int subplan_index,
+ ExplainState *es);
+
#endif /* EXPLAIN_H */
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 428,434 **** typedef struct ExecRowMark
Index rti; /* its range table index */
Index prti; /* parent range table index, if child */
Index rowmarkId; /* unique identifier for resjunk columns */
! RowMarkType markType; /* see enum in nodes/plannodes.h */
LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */
ItemPointerData curCtid; /* ctid of currently locked tuple, if any */
} ExecRowMark;
--- 428,434 ----
Index rti; /* its range table index */
Index prti; /* parent range table index, if child */
Index rowmarkId; /* unique identifier for resjunk columns */
! RowMarkType markType; /* see flags in nodes/plannodes.h */
LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED */
ItemPointerData curCtid; /* ctid of currently locked tuple, if any */
} ExecRowMark;
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 23,28 ****
--- 23,29 ----
#include "nodes/bitmapset.h"
#include "nodes/primnodes.h"
#include "nodes/value.h"
+ #include "utils/lockclausestrength.h"
#include "utils/lockwaitpolicy.h"
/* Possible sources of a Query */
***************
*** 618,632 **** typedef struct DefElem
* a location field --- currently, parse analysis insists on unqualified
* names in LockingClause.)
*/
- typedef enum LockClauseStrength
- {
- /* order is important -- see applyLockingClause */
- LCS_FORKEYSHARE,
- LCS_FORSHARE,
- LCS_FORNOKEYUPDATE,
- LCS_FORUPDATE
- } LockClauseStrength;
-
typedef struct LockingClause
{
NodeTag type;
--- 619,624 ----
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 18,23 ****
--- 18,24 ----
#include "lib/stringinfo.h"
#include "nodes/bitmapset.h"
#include "nodes/primnodes.h"
+ #include "utils/lockclausestrength.h"
#include "utils/lockwaitpolicy.h"
***************
*** 785,791 **** typedef struct Limit
/*
* RowMarkType -
! * enums for types of row-marking operations
*
* The first four of these values represent different lock strengths that
* we can take on tuples according to SELECT FOR [KEY] UPDATE/SHARE requests.
--- 786,792 ----
/*
* RowMarkType -
! * flags for types of row-marking operations
*
* The first four of these values represent different lock strengths that
* we can take on tuples according to SELECT FOR [KEY] UPDATE/SHARE requests.
***************
*** 814,828 **** typedef struct Limit
* it's probably still faster than doing a second remote fetch, so it doesn't
* seem worth the extra complexity to permit ROW_MARK_REFERENCE.
*/
! typedef enum RowMarkType
! {
! ROW_MARK_EXCLUSIVE, /* obtain exclusive tuple lock */
! ROW_MARK_NOKEYEXCLUSIVE, /* obtain no-key exclusive tuple lock */
! ROW_MARK_SHARE, /* obtain shared tuple lock */
! ROW_MARK_KEYSHARE, /* obtain keyshare tuple lock */
! ROW_MARK_REFERENCE, /* just fetch the TID */
! ROW_MARK_COPY /* physically copy the row value */
! } RowMarkType;
#define RowMarkRequiresRowShareLock(marktype) ((marktype) <= ROW_MARK_KEYSHARE)
--- 815,828 ----
* it's probably still faster than doing a second remote fetch, so it doesn't
* seem worth the extra complexity to permit ROW_MARK_REFERENCE.
*/
! typedef uint16 RowMarkType; /* a bitmask of row-marking operation bits */
!
! #define ROW_MARK_EXCLUSIVE 0x0001 /* obtain exclusive tuple lock */
! #define ROW_MARK_NOKEYEXCLUSIVE 0x0002 /* obtain no-key exclusive tuple lock */
! #define ROW_MARK_SHARE 0x0004 /* obtain shared tuple lock */
! #define ROW_MARK_KEYSHARE 0x0008 /* obtain keyshare tuple lock */
! #define ROW_MARK_REFERENCE 0x0010 /* just fetch the TID */
! #define ROW_MARK_COPY 0x0020 /* physically copy the row value */
#define RowMarkRequiresRowShareLock(marktype) ((marktype) <= ROW_MARK_KEYSHARE)
***************
*** 851,856 **** typedef enum RowMarkType
--- 851,858 ----
* The tableoid column is only present for an inheritance hierarchy.
* When markType == ROW_MARK_COPY, there is instead a single column named
* wholerow%u whole-row value of relation
+ * The wholerow column is also present for an inheritance hierarchy that
+ * contains foreign tables.
* In all three cases, %u represents the rowmark ID number (rowmarkId).
* This number is unique within a plan tree, except that child relation
* entries copy their parent's rowmarkId. (Assigning unique numbers
***************
*** 859,864 **** typedef enum RowMarkType
--- 861,870 ----
* Note this means that all tables in an inheritance hierarchy share the
* same resjunk column names. However, in an inherited UPDATE/DELETE the
* columns could have different physical column numbers in each subplan.
+ *
+ * A parent PlanRowMark's markType represents the logical OR of the rowmark
+ * methods being used by all its children. So, don't use the
+ * RowMarkRequiresRowShareLock macro with parent PlanRowMarks.
*/
typedef struct PlanRowMark
{
***************
*** 866,872 **** typedef struct PlanRowMark
Index rti; /* range table index of markable relation */
Index prti; /* range table index of parent relation */
Index rowmarkId; /* unique identifier for resjunk columns */
! RowMarkType markType; /* see enum above */
LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */
bool isParent; /* true if this is a "dummy" parent entry */
} PlanRowMark;
--- 872,879 ----
Index rti; /* range table index of markable relation */
Index prti; /* range table index of parent relation */
Index rowmarkId; /* unique identifier for resjunk columns */
! RowMarkType markType; /* see flags above */
! LockClauseStrength strength;
LockWaitPolicy waitPolicy; /* NOWAIT and SKIP LOCKED options */
bool isParent; /* true if this is a "dummy" parent entry */
} PlanRowMark;
*** /dev/null
--- b/src/include/utils/lockclausestrength.h
***************
*** 0 ****
--- 1,32 ----
+ /*-------------------------------------------------------------------------
+ *
+ * lockclausestrength.h
+ * Header file for LockClauseStrength enum.
+ *
+ * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/utils/lockclausestrength.h
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef LOCKCLAUSESTRENGTH_H
+ #define LOCKCLAUSESTRENGTH_H
+
+ /*
+ * This enum represents the locking strength of a FOR UPDATE/SHARE clause.
+ * The ordering here is important, because the highest numerical value takes
+ * precedence when a RTE is specified multiple ways. See applyLockingClause.
+ */
+ typedef enum LockClauseStrength
+ {
+ /* order is important -- see applyLockingClause */
+ LCS_NOLOCK, /* No locking needed (this is only used in
+ * PlanRowMarks.) */
+ LCS_FORKEYSHARE,
+ LCS_FORSHARE,
+ LCS_FORNOKEYUPDATE,
+ LCS_FORUPDATE
+ } LockClauseStrength;
+
+ #endif /* LOCKCLAUSESTRENGTH_H */
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
***************
*** 1234,1239 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
--- 1234,1644 ----
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ );
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date;
+ ALTER TABLE v1 INHERIT pt1; -- ERROR
+ ERROR: "v1" is not a table or foreign table
+ DROP VIEW v1;
+ -- add attributes recursively
+ ALTER TABLE pt1 ADD COLUMN c4 integer;
+ ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ ALTER TABLE pt1 ADD COLUMN c6 integer;
+ ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ ALTER TABLE pt1 ADD COLUMN c8 integer;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | | plain | |
+ c5 | integer | default 0 | plain | |
+ c6 | integer | | plain | |
+ c7 | integer | not null | plain | |
+ c8 | integer | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | | | plain | |
+ c5 | integer | default 0 | | plain | |
+ c6 | integer | | | plain | |
+ c7 | integer | not null | | plain | |
+ c8 | integer | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- alter attributes recursively
+ ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ ERROR: "ft2" is not a table
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | default 0 | plain | |
+ c5 | integer | | plain | |
+ c6 | integer | not null | plain | |
+ c7 | integer | | plain | |
+ c8 | text | | extended | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | default 0 | | plain | |
+ c5 | integer | | | plain | |
+ c6 | integer | not null | | plain | |
+ c7 | integer | | | plain | |
+ c8 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- drop attributes recursively
+ ALTER TABLE pt1 DROP COLUMN c4;
+ ALTER TABLE pt1 DROP COLUMN c5;
+ ALTER TABLE pt1 DROP COLUMN c6;
+ ALTER TABLE pt1 DROP COLUMN c7;
+ ALTER TABLE pt1 DROP COLUMN c8;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- cannot change storage mode for an attribute of foreign tables
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | external | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | external | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED;
+ -- add constraints recursively
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ -- connoinherit should be true for NO INHERIT constraint
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | connoinherit
+ ---------+---------+---------+------------+-------------+--------------
+ pt1 | pt1chk1 | c | t | 0 | t
+ pt1 | pt1chk2 | c | t | 0 | f
+ (2 rows)
+
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ ERROR: child table is missing constraint "pt1chk2"
+ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- drop constraints recursively
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID;
+ NOTICE: merging constraint "pt1chk2" with inherited definition
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text) NOT VALID
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- cannot add an OID system column to foreign tables
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+ ERROR: cannot add OID column to foreign table "ft2"
+ DROP FOREIGN TABLE ft2;
+ ALTER TABLE pt1 SET WITH OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ERROR: cannot inherit from relation with OIDs
+ ALTER TABLE pt1 SET WITHOUT OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- changes name of an attribute recursively
+ ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+ -- changes name of a constraint recursively
+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ f1 | integer | not null | plain | 10000 |
+ f2 | text | | extended | |
+ f3 | date | | plain | |
+ Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ f1 | integer | not null | | plain | |
+ f2 | text | | | extended | |
+ f3 | date | | | plain | |
+ Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- TRUNCATE should work by ignoring foreign tables
+ TRUNCATE pt1;
+ TRUNCATE ft2; -- ERROR
+ ERROR: "ft2" is not a table
+ DROP TABLE pt1 CASCADE;
+ NOTICE: drop cascades to foreign table ft2
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
ERROR: foreign-data wrapper "foo" has no handler
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
***************
*** 536,541 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
--- 536,670 ----
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ );
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ \d+ pt1
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ ft2
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ \d+ pt1
+ \d+ ft2
+ CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date;
+ ALTER TABLE v1 INHERIT pt1; -- ERROR
+ DROP VIEW v1;
+
+ -- add attributes recursively
+ ALTER TABLE pt1 ADD COLUMN c4 integer;
+ ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ ALTER TABLE pt1 ADD COLUMN c6 integer;
+ ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ ALTER TABLE pt1 ADD COLUMN c8 integer;
+ \d+ pt1
+ \d+ ft2
+
+ -- alter attributes recursively
+ ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ \d+ pt1
+ \d+ ft2
+
+ -- drop attributes recursively
+ ALTER TABLE pt1 DROP COLUMN c4;
+ ALTER TABLE pt1 DROP COLUMN c5;
+ ALTER TABLE pt1 DROP COLUMN c6;
+ ALTER TABLE pt1 DROP COLUMN c7;
+ ALTER TABLE pt1 DROP COLUMN c8;
+ \d+ pt1
+ \d+ ft2
+
+ -- cannot change storage mode for an attribute of foreign tables
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL;
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ \d+ ft2
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED;
+
+ -- add constraints recursively
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ -- connoinherit should be true for NO INHERIT constraint
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ \d+ ft2
+
+ -- drop constraints recursively
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID;
+ \d+ pt1
+ \d+ ft2
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+ \d+ pt1
+ \d+ ft2
+
+ -- cannot add an OID system column to foreign tables
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+ DROP FOREIGN TABLE ft2;
+ ALTER TABLE pt1 SET WITH OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ALTER TABLE pt1 SET WITHOUT OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+
+ -- changes name of an attribute recursively
+ ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+ -- changes name of a constraint recursively
+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check;
+ \d+ pt1
+ \d+ ft2
+
+ -- TRUNCATE should work by ignoring foreign tables
+ TRUNCATE pt1;
+ TRUNCATE ft2; -- ERROR
+
+ DROP TABLE pt1 CASCADE;
+
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
