From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: noah@leadboat.com
Cc: shigeru.hanada@gmail.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
Date: Fri, 24 Oct 2014 15:15:52 +0900
Message-ID: <5449EE98.1040704@lab.ntt.co.jp> (raw)
In-Reply-To: <54461BEB.9000707@lab.ntt.co.jp>
References: <53F48245.6020204@lab.ntt.co.jp>
<20140822025137.GA563733@tornado.leadboat.com>
<53FEEF94.6040101@lab.ntt.co.jp>
<20140902.142218.253402812.horiguchi.kyotaro@lab.ntt.co.jp>
<5410A76B.20102@vmware.com>
<541169C3.3030900@lab.ntt.co.jp>
<54117D6E.3000008@vmware.com>
<541187D5.4050301@lab.ntt.co.jp>
<54118CDA.90009@vmware.com>
<5412A112.9060009@lab.ntt.co.jp>
<543D0261.6020803@lab.ntt.co.jp>
<54461BEB.9000707@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(2014/10/21 17:40), Etsuro Fujita wrote:
> (2014/10/14 20:00), Etsuro Fujita wrote:
>> Here are separated patches.
>>
>> fdw-chk.patch - CHECK constraints on foreign tables
>> fdw-inh.patch - table inheritance with foreign tables
>>
>> The latter has been created on top of [1].
>
>> [1] http://www.postgresql.org/message-id/540DA168.3040407@lab.ntt.co.jp
>
> To be exact, it has been created on top of [1] and fdw-chk.patch.
>
> I noticed that the latter disallows TRUNCATE on inheritance trees that
> contain at least one child foreign table. But I think it would be
> better to allow it, with the semantics that we quietly ignore the child
> foreign tables and apply the operation to the child plain tables, which
> is the same semantics as ALTER COLUMN SET STORAGE on such inheritance
> trees. Comments welcome.
Done. And I've also a bit revised regression tests for both patches.
Patches attached.
Thanks,
Best regards,
Etsuro Fujita
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/x-diff] fdw-chk-2.patch (25.1K, ../5449EE98.1040704@lab.ntt.co.jp/2-fdw-chk-2.patch)
download | inline diff:
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 134,139 **** DELETE FROM agg_csv WHERE a = 100;
--- 134,149 ----
-- but this should be ignored
SELECT * FROM agg_csv FOR UPDATE;
+ -- constraint exclusion tests+ ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0);+ \t on+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;+ SET constraint_exclusion = 'on';+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;+ SET constraint_exclusion = 'partition';+ \t off+ ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check;+
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 219,224 **** SELECT * FROM agg_csv FOR UPDATE;
--- 219,242 ----
42 | 324.78
(3 rows)
+ -- constraint exclusion tests+ ALTER FOREIGN TABLE agg_csv ADD CONSTRAINT agg_csv_a_check CHECK (a >= 0);+ \t on+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;+ Foreign Scan on public.agg_csv+ Output: a, b+ Filter: (agg_csv.a < 0)+ Foreign File: @abs_srcdir@/data/agg.csv+ + SET constraint_exclusion = 'on';+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;+ Result+ Output: a, b+ One-Time Filter: false+ + SET constraint_exclusion = 'partition';+ \t off+ ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check;
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 2488,2493 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
--- 2488,2515 ----
(13 rows)
-- ===================================================================
+ -- test constraint exclusion stuff+ -- ===================================================================+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0;+ QUERY PLAN + ------------------------------------------------------------------------------------------+ Foreign Scan on public.ft1+ Output: c1, c2, c3, c4, c5, c6, c7, c8+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c2 < 0))+ (3 rows)+ + SET constraint_exclusion = 'on';+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0;+ QUERY PLAN + ------------------------------------------+ Result+ Output: c1, c2, c3, c4, c5, c6, c7, c8+ One-Time Filter: false+ (3 rows)+ + SET constraint_exclusion = 'partition';+ -- ===================================================================
-- test serial columns (ie, sequence-based defaults)
-- ===================================================================
create table loc1 (f1 serial, f2 text);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 387,392 **** select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
--- 387,401 ----
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
-- ===================================================================
+ -- test constraint exclusion stuff+ -- ===================================================================+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0;+ SET constraint_exclusion = 'on';+ EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 WHERE c2 < 0;+ SET constraint_exclusion = 'partition';+ + -- ===================================================================
-- test serial columns (ie, sequence-based defaults)
-- ===================================================================
create table loc1 (f1 serial, f2 text);
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
***************
*** 42,47 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 42,49 ----
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
+ ADD <replaceable class="PARAMETER">table_constraint</replaceable>+ DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
***************
*** 152,157 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 154,183 ----
</listitem>
</varlistentry>
+ <varlistentry>+ <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>+ <listitem>+ <para>+ This form adds a new constraint to a table using the same syntax as+ <xref linkend="SQL-CREATEFOREIGNTABLE">.+ Unlike the case when adding a constraint to a regular table, nothing happens+ to the underlying storage: this action simply declares that+ some new constraint holds for all rows in the foreign table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>+ <listitem>+ <para>+ This form drops the specified constraint on a table.+ If <literal>IF EXISTS</literal> is specified and the constraint+ does not exist, no error is thrown. In this case a notice is issued instead.+ </para>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
<listitem>
***************
*** 284,289 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 310,333 ----
</listitem>
</varlistentry>
+ <varlistentry>+ <term><replaceable class="PARAMETER">table_constraint</replaceable></term>+ <listitem>+ <para>+ New table constraint for the table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><replaceable class="PARAMETER">constraint_name</replaceable></term>+ <listitem>+ <para>+ Name of an existing constraint to drop.+ </para>+ </listitem>+ </varlistentry>+
<varlistentry>
<term><literal>CASCADE</literal></term>
<listitem>
***************
*** 365,374 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
<para>
Consistency with the foreign server is not checked when a column is added
or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, a <literal>NOT NULL</> constraint is
! added, or a column type is changed with <literal>SET DATA TYPE</>. It is
! the user's responsibility to ensure that the table definition matches the
! remote side.
</para>
<para>
--- 409,418 ----
<para>
Consistency with the foreign server is not checked when a column is added
or removed with <literal>ADD COLUMN</literal> or
! <literal>DROP COLUMN</literal>, a <literal>NOT NULL</> constraint or
! <literal>CHECK</> constraint is added, or a column type is changed with
! <literal>SET DATA TYPE</>. It is the user's responsibility to ensure that
! the table definition matches the remote side.
</para>
<para>
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
***************
*** 19,25 ****
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
--- 19,26 ----
<refsynopsisdiv>
<synopsis>
CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name</replaceable> ( [
! { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ] [ COLLATE <replaceable>collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
! | <replaceable>table_constraint</replaceable> }
[, ... ]
] )
SERVER <replaceable class="parameter">server_name</replaceable>
***************
*** 30,36 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 31,43 ----
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL |
NULL |
+ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
DEFAULT <replaceable>default_expr</replaceable> }
+ + <phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase>+ + [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]+ { CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) }
</synopsis>
</refsynopsisdiv>
***************
*** 138,143 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 145,171 ----
</varlistentry>
<varlistentry>
+ <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term>+ <listitem>+ <para>+ The <literal>CHECK</> clause specifies an expression producing a+ Boolean result which each row must satisfy.+ Expressions evaluating to TRUE or UNKNOWN succeed.+ A check constraint specified as a column constraint should+ reference that column's value only, while an expression+ appearing in a table constraint can reference multiple columns.+ </para>+ + <para>+ Currently, <literal>CHECK</literal> expressions cannot contain+ subqueries nor refer to variables other than columns of the+ current row. The system column <literal>tableoid</literal>+ may be referenced, but not any other system column.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>DEFAULT
<replaceable>default_expr</replaceable></literal></term>
<listitem>
***************
*** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 215,230 ----
</refsect1>
+ <refsect1>+ <title>Notes</title>+ + <para>+ Constraints on foreign tables are not enforced on insert or update.+ Those definitions simply declare the constraints hold for all rows+ in the foreign tables. It is the user's responsibility to ensure+ that those definitions match the remote side.+ </para>+ </refsect1>
<refsect1 id="SQL-CREATEFOREIGNTABLE-examples">
<title>Examples</title>
*** a/src/backend/catalog/heap.c
--- b/src/backend/catalog/heap.c
***************
*** 2221,2226 **** AddRelationNewConstraints(Relation rel,
--- 2221,2240 ----
if (cdef->contype != CONSTR_CHECK)
continue;
+ /* Don't allow NO INHERIT for foreign tables */+ if (cdef->is_no_inherit &&+ rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("CHECK constraints on foreign tables cannot be marked NO INHERIT")));+ + /* Don't allow NOT VALID for foreign tables */+ if (cdef->skip_validation &&+ rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("NOT VALID is not supported for CHECK constraints on foreign tables")));+
if (cdef->raw_expr != NULL)
{
Assert(cdef->cooked_expr == NULL);
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 477,486 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("ON COMMIT can only be used on temporary tables")));
- if (stmt->constraints != NIL && relkind == RELKIND_FOREIGN_TABLE)- ereport(ERROR,- (errcode(ERRCODE_WRONG_OBJECT_TYPE),- errmsg("constraints are not supported on foreign tables")));
/*
* Look up the namespace in which we are supposed to create the relation,
--- 477,482 ----
***************
*** 3142,3148 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_ADD_INDEX;
break;
case AT_AddConstraint: /* ADD CONSTRAINT */
! ATSimplePermissions(rel, ATT_TABLE);
/* Recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
--- 3138,3144 ----
pass = AT_PASS_ADD_INDEX;
break;
case AT_AddConstraint: /* ADD CONSTRAINT */
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* Recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
***************
*** 3156,3162 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_ADD_CONSTR;
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
! ATSimplePermissions(rel, ATT_TABLE);
/* Recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
--- 3152,3158 ----
pass = AT_PASS_ADD_CONSTR;
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* Recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 515,526 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
break;
case CONSTR_CHECK:
- if (cxt->isforeign)- ereport(ERROR,- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),- errmsg("constraints are not supported on foreign tables"),- parser_errposition(cxt->pstate,- constraint->location)));
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
--- 515,520 ----
***************
*** 529,535 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
if (constraint->keys == NIL)
--- 523,529 ----
if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("primary key or unique constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
if (constraint->keys == NIL)
***************
*** 546,552 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
--- 540,546 ----
if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
***************
*** 605,614 **** transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
static void
transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
{
! if (cxt->isforeign)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
--- 599,612 ----
static void
transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
{
! if (cxt->isforeign &&
! (constraint->contype == CONSTR_PRIMARY ||
! constraint->contype == CONSTR_UNIQUE ||
! constraint->contype == CONSTR_EXCLUSION ||
! constraint->contype == CONSTR_FOREIGN))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("primary key, unique, exclusion, or foreign key constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
***************
*** 670,676 **** LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
--- 670,682 ----
^
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT,
! c3 date
! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT
! CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''),
c3 date
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
***************
*** 682,687 **** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
--- 688,695 ----
c1 | integer | not null | ("param 1" 'val1') | plain | | ft1.c1
c2 | text | | (param2 'val2', param3 'val3') | extended | |
c3 | date | | | plain | |
+ Check constraints:+ "ft1_c2_check" CHECK (c2 <> ''::text)
Server: s0
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
***************
*** 740,745 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
--- 748,755 ----
c8 | text | | (p2 'V2') | extended | |
c9 | integer | | | plain | |
c10 | integer | | (p1 'v1') | plain | |
+ Check constraints:+ "ft1_c2_check" CHECK (c2 <> ''::text)
Server: s0
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
***************
*** 748,763 **** CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type
DROP TABLE use_ft1_column_type;
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR
! ERROR: constraints are not supported on foreign tables
! LINE 1: ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c...
! ^
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
! ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
! ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
! ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
--- 758,775 ----
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
ERROR: cannot alter foreign table "ft1" because column "use_ft1_column_type.x" uses its row type
DROP TABLE use_ft1_column_type;
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NO INHERIT; -- ERROR
! ERROR: CHECK constraints on foreign tables cannot be marked NO INHERIT
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR
! ERROR: NOT VALID is not supported for CHECK constraints on foreign tables
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0);
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
! ERROR: constraint "no_const" of relation "ft1" does not exist
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
! NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
! ERROR: constraint "ft1_c1_check" of relation "ft1" does not exist
! ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
***************
*** 785,790 **** ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1;
--- 797,804 ----
c7 | integer | | (p1 'v1', p2 'v2')
c8 | text | | (p2 'V2')
c10 | integer | | (p1 'v1')
+ Check constraints:+ "ft1_c2_check" CHECK (c2 <> ''::text)
Server: s0
FDW Options: (quote '~', "be quoted" 'value', escape '@')
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
***************
*** 270,276 **** CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3'),
c3 date
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
--- 270,281 ----
CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; -- ERROR
CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> '') NO INHERIT,
! c3 date
! ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
! CREATE FOREIGN TABLE ft1 (
! c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
! c2 text OPTIONS (param2 'val2', param3 'val3') CHECK (c2 <> ''),
c3 date
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
***************
*** 314,323 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
DROP TABLE use_ft1_column_type;
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0); -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
--- 319,331 ----
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
DROP TABLE use_ft1_column_type;
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NO INHERIT; -- ERROR
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0);
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
[text/x-diff] fdw-inh-2.patch (87.6K, ../5449EE98.1040704@lab.ntt.co.jp/3-fdw-inh-2.patch)
download | inline diff:
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 144,149 **** SET constraint_exclusion = 'partition';
--- 144,166 ----
\t off
ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check;
+ -- table inheritance tests+ CREATE TABLE agg (a int2, b float4);+ ALTER FOREIGN TABLE agg_csv INHERIT agg;+ ALTER FOREIGN TABLE agg_text INHERIT agg;+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b;+ SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b;+ SELECT * FROM agg_csv WHERE b < 10.0 ORDER BY a, b;+ SELECT * FROM agg_text WHERE b < 10.0 ORDER BY a, b;+ -- updates aren't supported+ UPDATE agg SET a = 1;+ DELETE FROM agg WHERE a = 100;+ -- but this should be ignored+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE;+ ALTER FOREIGN TABLE agg_csv NO INHERIT agg;+ ALTER FOREIGN TABLE agg_text NO INHERIT agg;+ DROP TABLE agg;+
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 237,242 **** EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
--- 237,289 ----
SET constraint_exclusion = 'partition';
\t off
ALTER FOREIGN TABLE agg_csv DROP CONSTRAINT agg_csv_a_check;
+ -- table inheritance tests+ CREATE TABLE agg (a int2, b float4);+ ALTER FOREIGN TABLE agg_csv INHERIT agg;+ ALTER FOREIGN TABLE agg_text INHERIT agg;+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b;+ a | b + ----+---------+ 0 | 0.09561+ 0 | 0.09561+ 56 | 7.8+ (3 rows)+ + SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b;+ a | b + ---+---+ (0 rows)+ + SELECT * FROM agg_csv WHERE b < 10.0 ORDER BY a, b;+ a | b + ---+---------+ 0 | 0.09561+ (1 row)+ + SELECT * FROM agg_text WHERE b < 10.0 ORDER BY a, b;+ a | b + ----+---------+ 0 | 0.09561+ 56 | 7.8+ (2 rows)+ + -- updates aren't supported+ UPDATE agg SET a = 1;+ ERROR: cannot update foreign table "agg_text"+ DELETE FROM agg WHERE a = 100;+ ERROR: cannot delete from foreign table "agg_text"+ -- but this should be ignored+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE;+ a | b + ----+---------+ 0 | 0.09561+ 0 | 0.09561+ 56 | 7.8+ (3 rows)+ + ALTER FOREIGN TABLE agg_csv NO INHERIT agg;+ ALTER FOREIGN TABLE agg_text NO INHERIT agg;+ DROP TABLE agg;
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 2863,2868 **** NOTICE: NEW: (13,"test triggered !")
--- 2863,3376 ----
(1 row)
-- ===================================================================
+ -- test inheritance features+ -- ===================================================================+ CREATE TABLE a (aa TEXT);+ CREATE TABLE loct (aa TEXT, bb TEXT);+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)+ SERVER loopback OPTIONS (table_name 'loct');+ INSERT INTO a(aa) VALUES('aaa');+ INSERT INTO a(aa) VALUES('aaaa');+ INSERT INTO a(aa) VALUES('aaaaa');+ INSERT INTO a(aa) VALUES('aaaaaa');+ INSERT INTO a(aa) VALUES('aaaaaaa');+ INSERT INTO a(aa) VALUES('aaaaaaaa');+ INSERT INTO b(aa) VALUES('bbb');+ INSERT INTO b(aa) VALUES('bbbb');+ INSERT INTO b(aa) VALUES('bbbbb');+ INSERT INTO b(aa) VALUES('bbbbbb');+ INSERT INTO b(aa) VALUES('bbbbbbb');+ INSERT INTO b(aa) VALUES('bbbbbbbb');+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+----------+ a | aaa+ a | aaaa+ a | aaaaa+ a | aaaaaa+ a | aaaaaaa+ a | aaaaaaaa+ b | bbb+ b | bbbb+ b | bbbbb+ b | bbbbbb+ b | bbbbbbb+ b | bbbbbbbb+ (12 rows)+ + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ relname | aa | bb + ---------+----------+----+ b | bbb | + b | bbbb | + b | bbbbb | + b | bbbbbb | + b | bbbbbbb | + b | bbbbbbbb | + (6 rows)+ + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+----------+ a | aaa+ a | aaaa+ a | aaaaa+ a | aaaaaa+ a | aaaaaaa+ a | aaaaaaaa+ (6 rows)+ + UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+----------+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ b | bbb+ b | bbbb+ b | bbbbb+ b | bbbbbb+ b | bbbbbbb+ b | bbbbbbbb+ (12 rows)+ + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ relname | aa | bb + ---------+----------+----+ b | bbb | + b | bbbb | + b | bbbbb | + b | bbbbbb | + b | bbbbbbb | + b | bbbbbbbb | + (6 rows)+ + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+--------+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ (6 rows)+ + UPDATE b SET aa='new';+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+--------+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ b | new+ b | new+ b | new+ b | new+ b | new+ b | new+ (12 rows)+ + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ relname | aa | bb + ---------+-----+----+ b | new | + b | new | + b | new | + b | new | + b | new | + b | new | + (6 rows)+ + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+--------+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ a | zzzzzz+ (6 rows)+ + UPDATE a SET aa='new';+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+-----+ a | new+ a | new+ a | new+ a | new+ a | new+ a | new+ b | new+ b | new+ b | new+ b | new+ b | new+ b | new+ (12 rows)+ + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ relname | aa | bb + ---------+-----+----+ b | new | + b | new | + b | new | + b | new | + b | new | + b | new | + (6 rows)+ + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+-----+ a | new+ a | new+ a | new+ a | new+ a | new+ a | new+ (6 rows)+ + DELETE FROM a;+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+----+ (0 rows)+ + SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ relname | aa | bb + ---------+----+----+ (0 rows)+ + SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ relname | aa + ---------+----+ (0 rows)+ + DROP TABLE a CASCADE;+ NOTICE: drop cascades to foreign table b+ DROP TABLE loct;+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table+ create table loct1 (f1 int, f2 int, f3 int);+ create table loct2 (f1 int, f2 int, f3 int);+ create table foo (f1 int, f2 int);+ create foreign table foo2 (f3 int) inherits (foo)+ server loopback options (table_name 'loct1');+ create table bar (f1 int, f2 int);+ create foreign table bar2 (f3 int) inherits (bar)+ server loopback options (table_name 'loct2');+ insert into foo values(1,1);+ insert into foo values(3,3);+ insert into foo2 values(2,2,2);+ insert into foo2 values(3,3,3);+ insert into bar values(1,1);+ insert into bar values(2,2);+ insert into bar values(3,3);+ insert into bar values(4,4);+ insert into bar2 values(1,1,1);+ insert into bar2 values(2,2,2);+ insert into bar2 values(3,3,3);+ insert into bar2 values(4,4,4);+ explain (verbose, costs off)+ select * from bar where f1 in (select f1 from foo) for update;+ QUERY PLAN + ----------------------------------------------------------------------------------------------+ LockRows+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*+ -> Hash Join+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*+ Hash Cond: (bar.f1 = foo.f1)+ -> Append+ -> Seq Scan on public.bar+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*+ -> Foreign Scan on public.bar2+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE+ -> Hash+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> HashAggregate+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ Group Key: foo.f1+ -> Append+ -> Seq Scan on public.foo+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> Foreign Scan on public.foo2+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1+ (22 rows)+ + select * from bar where f1 in (select f1 from foo) for update;+ f1 | f2 + ----+----+ 1 | 1+ 2 | 2+ 3 | 3+ 1 | 1+ 2 | 2+ 3 | 3+ (6 rows)+ + explain (verbose, costs off)+ select * from bar where f1 in (select f1 from foo) for share;+ QUERY PLAN + ----------------------------------------------------------------------------------------------+ LockRows+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*+ -> Hash Join+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*+ Hash Cond: (bar.f1 = foo.f1)+ -> Append+ -> Seq Scan on public.bar+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*+ -> Foreign Scan on public.bar2+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE+ -> Hash+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> HashAggregate+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ Group Key: foo.f1+ -> Append+ -> Seq Scan on public.foo+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> Foreign Scan on public.foo2+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1+ (22 rows)+ + select * from bar where f1 in (select f1 from foo) for share;+ f1 | f2 + ----+----+ 1 | 1+ 2 | 2+ 3 | 3+ 1 | 1+ 2 | 2+ 3 | 3+ (6 rows)+ + -- Check UPDATE with inherited target and an inherited source table+ explain (verbose, costs off)+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);+ QUERY PLAN + ---------------------------------------------------------------------------------------------+ Update on public.bar+ -> Hash Join+ Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.*+ Hash Cond: (bar.f1 = foo.f1)+ -> Seq Scan on public.bar+ Output: bar.f1, bar.f2, bar.ctid+ -> Hash+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> HashAggregate+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ Group Key: foo.f1+ -> Append+ -> Seq Scan on public.foo+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> Foreign Scan on public.foo2+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1+ -> Hash Join+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.*+ Hash Cond: (bar2.f1 = foo.f1)+ -> Foreign Scan on public.bar2+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE+ -> Hash+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> HashAggregate+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ Group Key: foo.f1+ -> Append+ -> Seq Scan on public.foo+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1+ -> Foreign Scan on public.foo2+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1+ (34 rows)+ + update bar set f2 = f2 + 100 where f1 in (select f1 from foo);+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;+ relname | f1 | f2 + ---------+----+-----+ bar | 1 | 101+ bar | 2 | 102+ bar | 3 | 103+ bar | 4 | 4+ bar2 | 1 | 101+ bar2 | 2 | 102+ bar2 | 3 | 103+ bar2 | 4 | 4+ (8 rows)+ + -- Check UPDATE with inherited target and an appendrel subquery+ explain (verbose, costs off)+ update bar set f2 = f2 + 100+ from+ ( select f1 from foo union all select f1+3 from foo ) ss+ where bar.f1 = ss.f1;+ QUERY PLAN + --------------------------------------------------------------------------------------+ Update on public.bar+ -> Hash Join+ Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))+ Hash Cond: (foo.f1 = bar.f1)+ -> Append+ -> Seq Scan on public.foo+ Output: ROW(foo.f1), foo.f1+ -> Foreign Scan on public.foo2+ Output: ROW(foo2.f1), foo2.f1+ Remote SQL: SELECT f1 FROM public.loct1+ -> Seq Scan on public.foo foo_1+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ -> Foreign Scan on public.foo2 foo2_1+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Remote SQL: SELECT f1 FROM public.loct1+ -> Hash+ Output: bar.f1, bar.f2, bar.ctid+ -> Seq Scan on public.bar+ Output: bar.f1, bar.f2, bar.ctid+ -> Merge Join+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))+ Merge Cond: (bar2.f1 = foo.f1)+ -> Sort+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid+ Sort Key: bar2.f1+ -> Foreign Scan on public.bar2+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE+ -> Sort+ Output: (ROW(foo.f1)), foo.f1+ Sort Key: foo.f1+ -> Append+ -> Seq Scan on public.foo+ Output: ROW(foo.f1), foo.f1+ -> Foreign Scan on public.foo2+ Output: ROW(foo2.f1), foo2.f1+ Remote SQL: SELECT f1 FROM public.loct1+ -> Seq Scan on public.foo foo_1+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ -> Foreign Scan on public.foo2 foo2_1+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Remote SQL: SELECT f1 FROM public.loct1+ (42 rows)+ + update bar set f2 = f2 + 100+ from+ ( select f1 from foo union all select f1+3 from foo ) ss+ where bar.f1 = ss.f1;+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;+ relname | f1 | f2 + ---------+----+-----+ bar | 1 | 201+ bar | 2 | 202+ bar | 3 | 203+ bar | 4 | 104+ bar2 | 1 | 201+ bar2 | 2 | 202+ bar2 | 3 | 203+ bar2 | 4 | 104+ (8 rows)+ + drop table foo cascade;+ NOTICE: drop cascades to foreign table foo2+ drop table bar cascade;+ NOTICE: drop cascades to foreign table bar2+ drop table loct1;+ drop table loct2;+ -- Test that WHERE CURRENT OF is not supported+ create table ltbl (a int, b text);+ create table ptbl (a int, b text);+ create table locc () inherits (ptbl);+ insert into locc values(1, 'foo');+ create foreign table remc () inherits (ptbl)+ server loopback options (table_name 'ltbl');+ insert into remc values(2, 'bar');+ select * from ptbl;+ a | b + ---+-----+ 1 | foo+ 2 | bar+ (2 rows)+ + begin;+ declare c cursor for select 1 from ptbl where b = 'foo';+ fetch from c;+ ?column? + ----------+ 1+ (1 row)+ + update ptbl set b = null where current of c;+ ERROR: WHERE CURRENT OF is not supported for this table type+ rollback;+ select * from ptbl;+ a | b + ---+-----+ 1 | foo+ 2 | bar+ (2 rows)+ + begin;+ declare c cursor for select 1 from ptbl where b = 'bar';+ fetch from c;+ ?column? + ----------+ 1+ (1 row)+ + update ptbl set b = null where current of c;+ ERROR: WHERE CURRENT OF is not supported for this table type+ rollback;+ select * from ptbl;+ a | b + ---+-----+ 1 | foo+ 2 | bar+ (2 rows)+ + -- Test TRUNCATE+ truncate ptbl;+ select * from ptbl;+ a | b + ---+-----+ 2 | bar+ (1 row)+ + select * from only ptbl;+ a | b + ---+---+ (0 rows)+ + select * from locc;+ a | b + ---+---+ (0 rows)+ + select * from remc;+ a | b + ---+-----+ 2 | bar+ (1 row)+ + truncate remc; -- ERROR+ ERROR: "remc" is not a table+ drop table ptbl cascade;+ NOTICE: drop cascades to 2 other objects+ DETAIL: drop cascades to table locc+ drop cascades to foreign table remc+ drop table ltbl;+ -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 823,828 **** postgresGetForeignPlan(PlannerInfo *root,
--- 823,839 ----
{
RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid);
+ if (rc == NULL)+ {+ PlanRowMark *prm = get_plan_rowmark(root->rowMarks, baserel->relid);+ + if (prm)+ {+ if (prm->rti != prm->prti)+ rc = get_parse_rowmark(root->parse, prm->prti);+ }+ }+
if (rc)
{
/*
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 626,631 **** UPDATE rem1 SET f2 = 'testo';
--- 626,782 ----
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
-- ===================================================================
+ -- test inheritance features+ -- ===================================================================+ + CREATE TABLE a (aa TEXT);+ CREATE TABLE loct (aa TEXT, bb TEXT);+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)+ SERVER loopback OPTIONS (table_name 'loct');+ + INSERT INTO a(aa) VALUES('aaa');+ INSERT INTO a(aa) VALUES('aaaa');+ INSERT INTO a(aa) VALUES('aaaaa');+ INSERT INTO a(aa) VALUES('aaaaaa');+ INSERT INTO a(aa) VALUES('aaaaaaa');+ INSERT INTO a(aa) VALUES('aaaaaaaa');+ + INSERT INTO b(aa) VALUES('bbb');+ INSERT INTO b(aa) VALUES('bbbb');+ INSERT INTO b(aa) VALUES('bbbbb');+ INSERT INTO b(aa) VALUES('bbbbbb');+ INSERT INTO b(aa) VALUES('bbbbbbb');+ INSERT INTO b(aa) VALUES('bbbbbbbb');+ + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ + UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';+ + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ + UPDATE b SET aa='new';+ + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ + UPDATE a SET aa='new';+ + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ + DELETE FROM a;+ + SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;+ + DROP TABLE a CASCADE;+ DROP TABLE loct;+ + -- Check SELECT FOR UPDATE/SHARE with an inherited source table+ create table loct1 (f1 int, f2 int, f3 int);+ create table loct2 (f1 int, f2 int, f3 int);+ + create table foo (f1 int, f2 int);+ create foreign table foo2 (f3 int) inherits (foo)+ server loopback options (table_name 'loct1');+ create table bar (f1 int, f2 int);+ create foreign table bar2 (f3 int) inherits (bar)+ server loopback options (table_name 'loct2');+ + insert into foo values(1,1);+ insert into foo values(3,3);+ insert into foo2 values(2,2,2);+ insert into foo2 values(3,3,3);+ insert into bar values(1,1);+ insert into bar values(2,2);+ insert into bar values(3,3);+ insert into bar values(4,4);+ insert into bar2 values(1,1,1);+ insert into bar2 values(2,2,2);+ insert into bar2 values(3,3,3);+ insert into bar2 values(4,4,4);+ + explain (verbose, costs off)+ select * from bar where f1 in (select f1 from foo) for update;+ select * from bar where f1 in (select f1 from foo) for update;+ + explain (verbose, costs off)+ select * from bar where f1 in (select f1 from foo) for share;+ select * from bar where f1 in (select f1 from foo) for share;+ + -- Check UPDATE with inherited target and an inherited source table+ explain (verbose, costs off)+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);+ + select tableoid::regclass::text as relname, bar.* from bar order by 1,2;+ + -- Check UPDATE with inherited target and an appendrel subquery+ explain (verbose, costs off)+ update bar set f2 = f2 + 100+ from+ ( select f1 from foo union all select f1+3 from foo ) ss+ where bar.f1 = ss.f1;+ update bar set f2 = f2 + 100+ from+ ( select f1 from foo union all select f1+3 from foo ) ss+ where bar.f1 = ss.f1;+ + select tableoid::regclass::text as relname, bar.* from bar order by 1,2;+ + drop table foo cascade;+ drop table bar cascade;+ drop table loct1;+ drop table loct2;+ + -- Test that WHERE CURRENT OF is not supported+ create table ltbl (a int, b text);+ + create table ptbl (a int, b text);+ create table locc () inherits (ptbl);+ insert into locc values(1, 'foo');+ + create foreign table remc () inherits (ptbl)+ server loopback options (table_name 'ltbl');+ insert into remc values(2, 'bar');+ + select * from ptbl;+ + begin;+ declare c cursor for select 1 from ptbl where b = 'foo';+ fetch from c;+ update ptbl set b = null where current of c;+ rollback;+ + select * from ptbl;+ + begin;+ declare c cursor for select 1 from ptbl where b = 'bar';+ fetch from c;+ update ptbl set b = null where current of c;+ rollback;+ + select * from ptbl;+ + -- Test TRUNCATE+ truncate ptbl;+ select * from ptbl;+ select * from only ptbl;+ select * from locc;+ select * from remc;+ truncate remc; -- ERROR+ + drop table ptbl cascade;+ drop table ltbl;+ + -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 2503,2508 **** VALUES ('Albany', NULL, NULL, 'NY');
--- 2503,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">).+ Like normal tables, table inheritance is typically established when+ the foreign table is created, using the <literal>INHERITS</> clause of+ the <xref linkend="sql-createforeigntable"> statement. Alternatively,+ a foreign table which is already defined in a compatible way can have+ a new parent relationship added, using the <literal>INHERIT</literal>+ variant of <xref linkend="sql-alterforeigntable">.+ Similarly an inheritance link can be removed from a child using the+ <literal>NO INHERIT</literal> variant of <command>ALTER FOREIGN TABLE</>.+ <command>CREATE FOREIGN TABLE</command> and+ <command>ALTER FOREIGN TABLE</command> follow the same rules for+ duplicate column merging and rejection that apply during+ <command>CREATE TABLE</command> and <command>ALTER TABLE</command>,+ respectively.+ </para>+
<sect2 id="ddl-inherit-caveats">
<title>Caveats</title>
***************
*** 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
! 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>
***************
*** 190,195 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 192,217 ----
</varlistentry>
<varlistentry>
+ <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>+ <listitem>+ <para>+ This form adds the target foreign table as a new child of the specified+ parent table. The parent table must be an ordinary table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>+ <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>+ <listitem>+ <para>+ This form removes the target foreign table from the list of children of+ the specified parent table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><literal>OWNER</literal></term>
<listitem>
<para>
***************
*** 380,385 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 402,417 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">parent_table</replaceable></term>+ <listitem>+ <para>+ A parent table to associate or de-associate with this foreign table.+ The parent table must be an ordinary table.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
<listitem>
<para>
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
***************
*** 1010,1015 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1010,1022 ----
</para>
<para>
+ If a table has any descendant tables that are foreign, a recursive+ <literal>SET STORAGE</literal> operation will be rejected since it+ is not permitted to add an <literal>oid</literal> system column to+ foreign tables.+ </para>+ + <para>
The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
and <literal>TABLESPACE</> actions never recurse to descendant tables;
that is, they always act as though <literal>ONLY</> were specified.
***************
*** 1018,1023 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1025,1043 ----
</para>
<para>
+ When adding a <literal>CHECK</> constraint with the <literal>NOT VALID+ </literal> option recursively, an inherited constraint on a descendant+ table that is foreign will be marked valid without checking+ consistency with the foreign server.+ </para>+ + <para>+ A recursive <literal>SET STORAGE</literal> operation will make the+ storage mode for a descendant table's column unchanged if the table is+ foreign.+ </para>+ + <para>
Changing any part of a system catalog table is not permitted.
</para>
*** a/doc/src/sgml/ref/analyze.sgml
--- b/doc/src/sgml/ref/analyze.sgml
***************
*** 200,205 **** ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> [
--- 200,212 ----
</para>
<para>
+ The inheritance statistics for a parent table that contains one or more+ children that are foreign tables are collected only when explicitly+ selected. If the foreign table's wrapper does not support+ <command>ANALYZE</command>, the command prints a warning and does nothing.+ </para>+ + <para>
If the table being analyzed is completely empty, <command>ANALYZE</command>
will not record new statistics for that table. Any existing statistics
will be retained.
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
***************
*** 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>' [, ... ] ) ]
***************
*** 187,192 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 188,205 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">parent_table</replaceable></term>+ <listitem>+ <para>+ The name of an existing table from which the new foreign table+ automatically inherits all columns. The specified parent table+ must be an ordinary table. See <xref linkend="ddl-inherit"> for the+ details of table inheritance.+ </para>+ </listitem>+ </varlistentry>+ + <varlistentry>
<term><replaceable class="PARAMETER">server_name</replaceable></term>
<listitem>
<para>
***************
*** 223,228 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 236,250 ----
Those definitions simply declare the constraints hold for all rows
in the foreign tables. It is the user's responsibility to ensure
that those definitions match the remote side.
+ Those constraints are used in some kind of query optimization such+ as constraint exclusion for partitioned tables (see+ <xref linkend="ddl-partitioning">).+ </para>+ + <para>+ Since it is not permitted to add an <literal>oid</> system column to+ foreign tables, the command will be rejected if any parent tables+ have <literal>oid</> system columns.
</para>
</refsect1>
*** a/src/backend/commands/analyze.c
--- b/src/backend/commands/analyze.c
***************
*** 82,87 **** int default_statistics_target = 100;
--- 82,88 ----
/* A few variables that don't seem worth passing around as parameters */
static MemoryContext anl_context = NULL;
+ static VacuumMode vac_mode;
static BufferAccessStrategy vac_strategy;
***************
*** 102,107 **** static int acquire_sample_rows(Relation onerel, int elevel,
--- 103,109 ----
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
static int compare_rows(const void *a, const void *b);
+ static bool has_foreign(Oid parentrelId, List *tableOIDs);
static int acquire_inherited_sample_rows(Relation onerel, int elevel,
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
***************
*** 115,121 **** static Datum ind_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
* analyze_rel() -- analyze one relation
*/
void
! analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy)
{
Relation onerel;
int elevel;
--- 117,126 ----
* analyze_rel() -- analyze one relation
*/
void
! analyze_rel(Oid relid,
! VacuumStmt *vacstmt,
! VacuumMode vacmode,
! BufferAccessStrategy bstrategy)
{
Relation onerel;
int elevel;
***************
*** 129,134 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy)
--- 134,140 ----
elevel = DEBUG2;
/* Set up static variables */
+ vac_mode = vacmode;
vac_strategy = bstrategy;
/*
***************
*** 294,302 **** analyze_rel(Oid relid, VacuumStmt *vacstmt, BufferAccessStrategy bstrategy)
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * If we supported foreign tables in inheritance trees,
! * acquire_inherited_sample_rows would need to determine the appropriate
! * acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
--- 300,307 ----
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * For the inherited case, acquire_inherited_sample_rows determines the
! * appropriate acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
***************
*** 1439,1449 **** compare_rows(const void *a, const void *b)
/*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1444,1490 ----
/*
+ * Check wether to contain at least one foreign table+ */+ static bool+ has_foreign(Oid parentrelId, List *tableOIDs)+ {+ bool result;+ ListCell *lc;+ + /* There are no children */+ if (list_length(tableOIDs) < 2)+ return false;+ + result = false;+ foreach(lc, tableOIDs)+ {+ Oid childOID = lfirst_oid(lc);+ Relation childrel;+ + /* Parent should not be foreign */+ if (childOID == parentrelId)+ continue;+ + /* We already got the needed lock */+ childrel = heap_open(childOID, NoLock);+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)+ {+ /* Found it */+ result = true;+ }+ heap_close(childrel, NoLock);+ }+ return result;+ }+ + /*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children or there are
! * inheritance children that foreign tables.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
***************
*** 1452,1457 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1493,1499 ----
{
List *tableOIDs;
Relation *rels;
+ AcquireSampleRowsFunc *acquirefunc;
double *relblocks;
double totalblocks;
int numrows,
***************
*** 1482,1491 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1524,1554 ----
}
/*
+ * If we are not in VAC_MODE_SINGLE case and the inheritance set contains+ * at least one foreign table, then fail.+ */+ if (vac_mode != VAC_MODE_SINGLE)+ {+ if (has_foreign(RelationGetRelid(onerel), tableOIDs))+ {+ ereport(elevel,+ (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains foreign tables",+ get_namespace_name(RelationGetNamespace(onerel)),+ RelationGetRelationName(onerel)),+ errhint("Try ANALYZE \"%s.%s\" for the inheritance statistics.",+ get_namespace_name(RelationGetNamespace(onerel)),+ RelationGetRelationName(onerel))));+ return 0;+ }+ }+ + /*
* Count the blocks in all the relations. The result could overflow
* BlockNumber, so we use double arithmetic.
*/
rels = (Relation *) palloc(list_length(tableOIDs) * sizeof(Relation));
+ acquirefunc = (AcquireSampleRowsFunc *) palloc(list_length(tableOIDs)+ * sizeof(AcquireSampleRowsFunc));
relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double));
totalblocks = 0;
nrels = 0;
***************
*** 1507,1513 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
}
rels[nrels] = childrel;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
totalblocks += relblocks[nrels];
nrels++;
}
--- 1570,1605 ----
}
rels[nrels] = childrel;
!
! if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
! {
! FdwRoutine *fdwroutine;
! BlockNumber relpages = 0;
! bool ok = false;
!
! /* Check whether the FDW supports analysis */
! fdwroutine = GetFdwRoutineForRelation(childrel, false);
! if (fdwroutine->AnalyzeForeignTable != NULL)
! ok = fdwroutine->AnalyzeForeignTable(childrel,
! &acquirefunc[nrels],
! &relpages);
! if (!ok)
! {
! /* Give up if the FDW doesn't support analysis */
! ereport(WARNING,
! (errmsg("skipping analyze of inheritance tree \"%s\" --- cannot analyze foreign table \"%s\"",
! RelationGetRelationName(onerel),
! RelationGetRelationName(childrel))));
! return 0;
! }
! relblocks[nrels] = (double) relpages;
! }
! else
! {
! acquirefunc[nrels] = acquire_sample_rows;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
! }
!
totalblocks += relblocks[nrels];
nrels++;
}
***************
*** 1525,1530 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1617,1623 ----
{
Relation childrel = rels[i];
double childblocks = relblocks[i];
+ AcquireSampleRowsFunc childacquirefunc = acquirefunc[i];
if (childblocks > 0)
{
***************
*** 1540,1551 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = acquire_sample_rows(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
--- 1633,1644 ----
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = childacquirefunc(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 315,321 **** 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,
--- 315,322 ----
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,
***************
*** 566,571 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
--- 567,596 ----
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
***************
*** 1023,1028 **** ExecuteTruncate(TruncateStmt *stmt)
--- 1048,1059 ----
/* 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);
***************
*** 3090,3113 **** 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;
--- 3121,3148 ----
* 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;
***************
*** 3120,3126 **** 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;
--- 3155,3162 ----
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;
***************
*** 3242,3252 **** 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;
--- 3278,3294 ----
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;
***************
*** 3280,3286 **** 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:
--- 3322,3327 ----
***************
*** 4274,4280 **** 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
--- 4315,4322 ----
*/
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
***************
*** 4302,4309 **** 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);
}
}
--- 4344,4355 ----
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);
}
}
***************
*** 4593,4599 **** 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);
--- 4639,4645 ----
/* 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);
***************
*** 4889,4894 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 4935,4945 ----
/* 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 */
***************
*** 5489,5495 **** 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
--- 5540,5546 ----
/* 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
***************
*** 5881,5887 **** 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
--- 5932,5938 ----
/* 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
***************
*** 5892,5900 **** 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 */
--- 5943,5959 ----
* 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 */
***************
*** 7381,7387 **** 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);
--- 7440,7446 ----
/* 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);
***************
*** 7716,7722 **** 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,
--- 7775,7784 ----
* alter would put them out of step.
*/
if (recurse)
! {
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
! }
else if (!recursing &&
find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
ereport(ERROR,
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***************
*** 61,66 **** int vacuum_multixact_freeze_table_age;
--- 61,67 ----
/* A few variables that don't seem worth passing around as parameters */
static MemoryContext vac_context = NULL;
+ static VacuumMode vac_mode;
static BufferAccessStrategy vac_strategy;
***************
*** 149,154 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
--- 150,169 ----
ALLOCSET_DEFAULT_MAXSIZE);
/*
+ * Identify vacuum mode. If relid is not InvalidOid, the caller should be+ * an autovacuum worker. See the above comments.+ */+ if (relid != InvalidOid)+ vac_mode = VAC_MODE_AUTOVACUUM;+ else+ {+ if (!vacstmt->relation)+ vac_mode = VAC_MODE_ALL;+ else+ vac_mode = VAC_MODE_SINGLE;+ }+ + /*
* If caller didn't give us a buffer strategy object, make one in the
* cross-transaction memory context.
*/
***************
*** 251,257 **** vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
PushActiveSnapshot(GetTransactionSnapshot());
}
! analyze_rel(relid, vacstmt, vac_strategy);
if (use_own_xacts)
{
--- 266,272 ----
PushActiveSnapshot(GetTransactionSnapshot());
}
! analyze_rel(relid, vacstmt, vac_mode, vac_strategy);
if (use_own_xacts)
{
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 2308,2313 **** EvalPlanQualFetchRowMarks(EPQState *epqstate)
--- 2308,2333 ----
Assert(erm->markType == ROW_MARK_COPY);
+ /* if child rel, must check whether it produced this row */+ if (erm->rti != erm->prti)+ {+ Oid tableoid;+ + datum = ExecGetJunkAttribute(epqstate->origslot,+ aerm->toidAttNo,+ &isNull);+ /* non-locked rels could be on the inside of outer joins */+ if (isNull)+ continue;+ tableoid = DatumGetObjectId(datum);+ + if (tableoid != RelationGetRelid(erm->relation))+ {+ /* this child is inactive right now */+ continue;+ }+ }+
/* fetch the whole-row Var for the relation */
datum = ExecGetJunkAttribute(epqstate->origslot,
aerm->wholeAttNo,
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 1994,1999 **** _copyRangeTblEntry(const RangeTblEntry *from)
--- 1994,2000 ----
COPY_NODE_FIELD(eref);
COPY_SCALAR_FIELD(lateral);
COPY_SCALAR_FIELD(inh);
+ COPY_SCALAR_FIELD(hasForeign);
COPY_SCALAR_FIELD(inFromCl);
COPY_SCALAR_FIELD(requiredPerms);
COPY_SCALAR_FIELD(checkAsUser);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 2341,2346 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
--- 2341,2347 ----
COMPARE_NODE_FIELD(eref);
COMPARE_SCALAR_FIELD(lateral);
COMPARE_SCALAR_FIELD(inh);
+ COMPARE_SCALAR_FIELD(hasForeign);
COMPARE_SCALAR_FIELD(inFromCl);
COMPARE_SCALAR_FIELD(requiredPerms);
COMPARE_SCALAR_FIELD(checkAsUser);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2420,2425 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
--- 2420,2426 ----
WRITE_BOOL_FIELD(lateral);
WRITE_BOOL_FIELD(inh);
+ WRITE_BOOL_FIELD(hasForeign);
WRITE_BOOL_FIELD(inFromCl);
WRITE_UINT_FIELD(requiredPerms);
WRITE_OID_FIELD(checkAsUser);
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1249,1254 **** _readRangeTblEntry(void)
--- 1249,1255 ----
READ_BOOL_FIELD(lateral);
READ_BOOL_FIELD(inh);
+ READ_BOOL_FIELD(hasForeign);
READ_BOOL_FIELD(inFromCl);
READ_UINT_FIELD(requiredPerms);
READ_OID_FIELD(checkAsUser);
*** a/src/backend/optimizer/prep/preptlist.c
--- b/src/backend/optimizer/prep/preptlist.c
***************
*** 111,116 **** preprocess_targetlist(PlannerInfo *root, List *tlist)
--- 111,118 ----
/* if parent of inheritance tree, need the tableoid too */
if (rc->isParent)
{
+ RangeTblEntry *rte = rt_fetch(rc->rti, parse->rtable);+
var = makeVar(rc->rti,
TableOidAttributeNumber,
OIDOID,
***************
*** 123,128 **** preprocess_targetlist(PlannerInfo *root, List *tlist)
--- 125,146 ----
pstrdup(resname),
true);
tlist = lappend(tlist, tle);
+ + /* if including foreign tables, fetch the whole row too */+ if (rte->hasForeign)+ {+ /* Not a table, so we need the whole row as a junk var */+ var = makeWholeRowVar(rt_fetch(rc->rti, range_table),+ rc->rti,+ 0,+ false);+ snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(tlist) + 1,+ pstrdup(resname),+ true);+ tlist = lappend(tlist, tle);+ }
}
}
else
*** a/src/backend/optimizer/prep/prepunion.c
--- b/src/backend/optimizer/prep/prepunion.c
***************
*** 1237,1242 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1237,1243 ----
LOCKMODE lockmode;
List *inhOIDs;
List *appinfos;
+ bool hasForeign;
ListCell *l;
/* Does RT entry allow inheritance? */
***************
*** 1309,1314 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1310,1316 ----
/* Scan the inheritance set and expand it */
appinfos = NIL;
+ hasForeign = false;
foreach(l, inhOIDs)
{
Oid childOID = lfirst_oid(l);
***************
*** 1338,1348 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* Build an RTE for the child, and attach to query's rangetable list.
* We copy most fields of the parent's RTE, but replace relation OID,
! * and set inh = false. Also, set requiredPerms to zero since all
! * required permissions checks are done on the original RTE.
*/
childrte = copyObject(rte);
childrte->relid = childOID;
childrte->inh = false;
childrte->requiredPerms = 0;
parse->rtable = lappend(parse->rtable, childrte);
--- 1340,1351 ----
/*
* Build an RTE for the child, and attach to query's rangetable list.
* We copy most fields of the parent's RTE, but replace relation OID,
! * relkind and set inh = false. Also, set requiredPerms to zero since
! * all required permissions checks are done on the original RTE.
*/
childrte = copyObject(rte);
childrte->relid = childOID;
+ childrte->relkind = newrelation->rd_rel->relkind;
childrte->inh = false;
childrte->requiredPerms = 0;
parse->rtable = lappend(parse->rtable, childrte);
***************
*** 1388,1400 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrc->rti = childRTindex;
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
! newrc->markType = oldrc->markType;
newrc->waitPolicy = oldrc->waitPolicy;
newrc->isParent = false;
root->rowMarks = lappend(root->rowMarks, newrc);
}
/* Close child relations, but keep locks */
if (childOID != parentOID)
heap_close(newrelation, NoLock);
--- 1391,1409 ----
newrc->rti = childRTindex;
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
! if (childrte->relkind == RELKIND_FOREIGN_TABLE)
! newrc->markType = ROW_MARK_COPY;
! else
! newrc->markType = oldrc->markType;
newrc->waitPolicy = oldrc->waitPolicy;
newrc->isParent = false;
root->rowMarks = lappend(root->rowMarks, newrc);
}
+ if (childrte->relkind == RELKIND_FOREIGN_TABLE)+ hasForeign = true;+
/* Close child relations, but keep locks */
if (childOID != parentOID)
heap_close(newrelation, NoLock);
***************
*** 1416,1421 **** expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
--- 1425,1434 ----
/* Otherwise, OK to add to root->append_rel_list */
root->append_rel_list = list_concat(root->append_rel_list, appinfos);
+ + /* And mark the parent table as having children that are foreign, if so */+ if (hasForeign)+ rte->hasForeign = true;
}
/*
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 4351,4382 **** 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;
}
;
--- 4351,4382 ----
CreateForeignTableStmt:
CREATE FOREIGN TABLE qualified_name
'(' OptTableElementList ')'
! OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$4->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $4;
n->base.tableElts = $6;
! n->base.inhRelations = $8;
n->base.if_not_exists = false;
/* FDW-specific data */
! n->servername = $10;
! n->options = $11;
$$ = (Node *) n;
}
| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
'(' OptTableElementList ')'
! OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$7->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $7;
n->base.tableElts = $9;
! n->base.inhRelations = $11;
n->base.if_not_exists = true;
/* FDW-specific data */
! n->servername = $13;
! n->options = $14;
$$ = (Node *) n;
}
;
*** a/src/include/commands/vacuum.h
--- b/src/include/commands/vacuum.h
***************
*** 140,145 **** extern int vacuum_multixact_freeze_min_age;
--- 140,154 ----
extern int vacuum_multixact_freeze_table_age;
+ /* Possible modes for vacuum() */+ typedef enum+ {+ VAC_MODE_ALL, /* Vacuum/analyze all relations */+ VAC_MODE_SINGLE, /* Vacuum/analyze a specific relation */+ VAC_MODE_AUTOVACUUM /* Autovacuum worker */+ } VacuumMode;+ +
/* in commands/vacuum.c */
extern void vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel);
***************
*** 174,180 **** extern void lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy);
/* in commands/analyze.c */
! extern void analyze_rel(Oid relid, VacuumStmt *vacstmt,
BufferAccessStrategy bstrategy);
extern bool std_typanalyze(VacAttrStats *stats);
extern double anl_random_fract(void);
--- 183,191 ----
BufferAccessStrategy bstrategy);
/* in commands/analyze.c */
! extern void analyze_rel(Oid relid,
! VacuumStmt *vacstmt,
! VacuumMode vacmode,
BufferAccessStrategy bstrategy);
extern bool std_typanalyze(VacAttrStats *stats);
extern double anl_random_fract(void);
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 812,817 **** typedef struct RangeTblEntry
--- 812,818 ----
Alias *eref; /* expanded reference names */
bool lateral; /* subquery, function, or values is LATERAL? */
bool inh; /* inheritance requested? */
+ bool hasForeign; /* does inheritance include foreign tables? */
bool inFromCl; /* present in FROM clause? */
AclMode requiredPerms; /* bitmask of required access permissions */
Oid checkAsUser; /* if valid, check access as this role */
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 819,824 **** typedef enum RowMarkType
--- 819,826 ----
* The tableoid column is only present for an inheritance hierarchy.
* When markType == ROW_MARK_COPY, there is instead a single column named
* wholerow%u whole-row value of relation
+ * The wholerow column is also present for an inheritance hierarchy + * in cases where the inheritance hierarchy contains foreign tables.
* In all three cases, %u represents the rowmark ID number (rowmarkId).
* This number is unique within a plan tree, except that child relation
* entries copy their parent's rowmarkId. (Assigning unique numbers
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
***************
*** 1207,1212 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
--- 1207,1547 ----
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+ -- Table inheritance+ CREATE TABLE pt1 (ff1 integer, ff2 text);+ CREATE VIEW view1 AS SELECT 1;+ ALTER TABLE view1 INHERIT pt1; -- ERROR+ ERROR: "view1" is not a table or foreign table+ DROP VIEW view1;+ CREATE FOREIGN TABLE ct1 (ff1 integer, ff2 text)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ ALTER TABLE ct1 INHERIT pt1;+ DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER FOREIGN TABLE ct1 NO INHERIT pt1;+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER TABLE pt1 ADD COLUMN ff3 integer;+ ALTER TABLE pt1 ADD COLUMN ff4 integer;+ ALTER TABLE pt1 ADD COLUMN ff5 integer DEFAULT 0;+ ALTER TABLE pt1 ADD COLUMN ff6 integer;+ ALTER TABLE pt1 ADD COLUMN ff7 integer NOT NULL;+ ALTER TABLE pt1 ALTER COLUMN ff3 TYPE char(10) USING '0'; -- ERROR+ ERROR: "ct1" is not a table+ ALTER TABLE pt1 ALTER COLUMN ff3 TYPE char(10);+ ALTER TABLE pt1 ALTER COLUMN ff3 SET DATA TYPE text;+ ALTER TABLE pt1 ALTER COLUMN ff4 SET DEFAULT 0;+ ALTER TABLE pt1 ALTER COLUMN ff5 DROP DEFAULT;+ ALTER TABLE pt1 ALTER COLUMN ff6 SET NOT NULL;+ ALTER TABLE pt1 ALTER COLUMN ff7 DROP NOT NULL;+ ALTER TABLE pt1 ALTER COLUMN ff1 SET STATISTICS 10000;+ ALTER TABLE pt1 ALTER COLUMN ff1 SET (n_distinct = 100);+ ALTER TABLE pt1 ALTER COLUMN ff3 SET STATISTICS -1;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | 10000 | + ff2 | text | | extended | | + ff3 | text | | extended | | + ff4 | integer | default 0 | plain | | + ff5 | integer | | plain | | + ff6 | integer | not null | plain | | + ff7 | integer | | plain | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | 10000 | + ff2 | text | | | extended | | + ff3 | text | | | extended | | + ff4 | integer | default 0 | | plain | | + ff5 | integer | | | plain | | + ff6 | integer | not null | | plain | | + ff7 | integer | | | plain | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER TABLE pt1 ALTER COLUMN ff1 SET STATISTICS -1;+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STATISTICS -1;+ ALTER TABLE pt1 DROP COLUMN ff3;+ ALTER TABLE pt1 DROP COLUMN ff4;+ ALTER TABLE pt1 DROP COLUMN ff5;+ ALTER TABLE pt1 DROP COLUMN ff6;+ ALTER TABLE pt1 DROP COLUMN ff7;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + -- cannot change storage modes for foreign tables+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | external | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | external | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER FOREIGN TABLE ct1 NO INHERIT pt1;+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | external | | + Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED;+ -- connoinherit should be true for NO INHERIT constraint+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT;+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;+ relname | conname | contype | conislocal | coninhcount | connoinherit + ---------+---------+---------+------------+-------------+--------------+ pt1 | pt1chk1 | c | t | 0 | t+ pt1 | pt1chk2 | c | t | 0 | f+ (2 rows)+ + -- child does not inherit NO INHERIT constraints+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints:+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT+ "pt1chk2" CHECK (ff1 > 10)+ Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints:+ "pt1chk2" CHECK (ff1 > 10)+ Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 (ff1 integer, ff2 text)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ -- child must have parent's INHERIT constraints+ ALTER FOREIGN TABLE ct1 INHERIT pt1; -- ERROR+ ERROR: child table is missing constraint "pt1chk2"+ ALTER FOREIGN TABLE ct1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints:+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT+ "pt1chk2" CHECK (ff1 > 10)+ Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints:+ "pt1chk2" CHECK (ff1 > 10)+ Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + -- NOT VALID should be ignored for foreign tables+ INSERT INTO pt1 VALUES (1000, 'pt1');+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk3 CHECK (ff1 > 100) NOT VALID;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints:+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT+ "pt1chk2" CHECK (ff1 > 10)+ "pt1chk3" CHECK (ff1 > 100) NOT VALID+ Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints:+ "pt1chk2" CHECK (ff1 > 10)+ "pt1chk3" CHECK (ff1 > 100)+ Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + -- VALIDATE CONSTRAINT should work by ignoring foreign tables+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk3;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ ff1 | integer | | plain | | + ff2 | text | | extended | | + Check constraints:+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT+ "pt1chk2" CHECK (ff1 > 10)+ "pt1chk3" CHECK (ff1 > 100)+ Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ ff1 | integer | | | plain | | + ff2 | text | | | extended | | + Check constraints:+ "pt1chk2" CHECK (ff1 > 10)+ "pt1chk3" CHECK (ff1 > 100)+ Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + ALTER TABLE pt1 DROP CONSTRAINT pt1chk3 CASCADE;+ -- cannot add an OID system column+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR+ ERROR: cannot add OID column to foreign table "ct1"+ -- changes name of an attribute recursively+ ALTER TABLE pt1 RENAME COLUMN ff1 TO x;+ ALTER TABLE pt1 RENAME COLUMN ff2 TO y;+ -- changes name of a constraint recursively+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO x_check;+ \d+ pt1+ Table "public.pt1"+ Column | Type | Modifiers | Storage | Stats target | Description + --------+---------+-----------+----------+--------------+-------------+ x | integer | | plain | | + y | text | | extended | | + Check constraints:+ "pt1chk1" CHECK (x > 0) NO INHERIT+ "x_check" CHECK (x > 10)+ Child tables: ct1+ + \d+ ct1+ Foreign table "public.ct1"+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description + --------+---------+-----------+-------------+----------+--------------+-------------+ x | integer | | | plain | | + y | text | | | extended | | + Check constraints:+ "x_check" CHECK (x > 10)+ Server: s0+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')+ Inherits: pt1+ + DROP TABLE pt1 CASCADE;+ NOTICE: drop cascades to foreign table ct1
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
ERROR: foreign-data wrapper "foo" has no handler
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
***************
*** 522,527 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
--- 522,631 ----
DROP FUNCTION dummy_trigger();
+ -- Table inheritance+ CREATE TABLE pt1 (ff1 integer, ff2 text);+ CREATE VIEW view1 AS SELECT 1;+ ALTER TABLE view1 INHERIT pt1; -- ERROR+ DROP VIEW view1;+ CREATE FOREIGN TABLE ct1 (ff1 integer, ff2 text)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ ALTER TABLE ct1 INHERIT pt1;+ DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ \d+ pt1+ \d+ ct1+ ALTER FOREIGN TABLE ct1 NO INHERIT pt1;+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ \d+ ct1+ + ALTER TABLE pt1 ADD COLUMN ff3 integer;+ ALTER TABLE pt1 ADD COLUMN ff4 integer;+ ALTER TABLE pt1 ADD COLUMN ff5 integer DEFAULT 0;+ ALTER TABLE pt1 ADD COLUMN ff6 integer;+ ALTER TABLE pt1 ADD COLUMN ff7 integer NOT NULL;+ + ALTER TABLE pt1 ALTER COLUMN ff3 TYPE char(10) USING '0'; -- ERROR+ ALTER TABLE pt1 ALTER COLUMN ff3 TYPE char(10);+ ALTER TABLE pt1 ALTER COLUMN ff3 SET DATA TYPE text;+ ALTER TABLE pt1 ALTER COLUMN ff4 SET DEFAULT 0;+ ALTER TABLE pt1 ALTER COLUMN ff5 DROP DEFAULT;+ ALTER TABLE pt1 ALTER COLUMN ff6 SET NOT NULL;+ ALTER TABLE pt1 ALTER COLUMN ff7 DROP NOT NULL;+ ALTER TABLE pt1 ALTER COLUMN ff1 SET STATISTICS 10000;+ ALTER TABLE pt1 ALTER COLUMN ff1 SET (n_distinct = 100);+ ALTER TABLE pt1 ALTER COLUMN ff3 SET STATISTICS -1;+ \d+ pt1+ \d+ ct1+ + ALTER TABLE pt1 ALTER COLUMN ff1 SET STATISTICS -1;+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STATISTICS -1;+ + ALTER TABLE pt1 DROP COLUMN ff3;+ ALTER TABLE pt1 DROP COLUMN ff4;+ ALTER TABLE pt1 DROP COLUMN ff5;+ ALTER TABLE pt1 DROP COLUMN ff6;+ ALTER TABLE pt1 DROP COLUMN ff7;+ \d+ pt1+ \d+ ct1+ + -- cannot change storage modes for foreign tables+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL;+ \d+ pt1+ \d+ ct1+ DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ \d+ pt1+ \d+ ct1+ ALTER FOREIGN TABLE ct1 NO INHERIT pt1;+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ \d+ ct1+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED;+ + -- connoinherit should be true for NO INHERIT constraint+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT;+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;+ -- child does not inherit NO INHERIT constraints+ \d+ pt1+ \d+ ct1+ DROP FOREIGN TABLE ct1;+ CREATE FOREIGN TABLE ct1 (ff1 integer, ff2 text)+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');+ -- child must have parent's INHERIT constraints+ ALTER FOREIGN TABLE ct1 INHERIT pt1; -- ERROR+ ALTER FOREIGN TABLE ct1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);+ ALTER FOREIGN TABLE ct1 INHERIT pt1;+ \d+ pt1+ \d+ ct1+ + -- NOT VALID should be ignored for foreign tables+ INSERT INTO pt1 VALUES (1000, 'pt1');+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk3 CHECK (ff1 > 100) NOT VALID;+ \d+ pt1+ \d+ ct1+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk3;+ \d+ pt1+ \d+ ct1+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk3 CASCADE;+ + -- cannot add an OID system column+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR+ + -- changes name of an attribute recursively+ ALTER TABLE pt1 RENAME COLUMN ff1 TO x;+ ALTER TABLE pt1 RENAME COLUMN ff2 TO y;+ -- changes name of a constraint recursively+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO x_check;+ \d+ pt1+ \d+ ct1+ + 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, hlinnakangas@vmware.com, horiguchi.kyotaro@lab.ntt.co.jp, noah@leadboat.com, shigeru.hanada@gmail.com
Subject: Re: inherit support for foreign tables
In-Reply-To: <5449EE98.1040704@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox