(2014/09/12 16:30), Etsuro Fujita wrote:
(2014/09/11 20:51), Heikki Linnakangas wrote:
On 09/11/2014 02:30 PM, Etsuro Fujita wrote:
So, should I split the patch into the two?
Yeah, please do.
OK, Will do.
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]. I've addressed the comment
from Horiguchi-san [2] in it also, though I've slightly modified his
proposal.
There is the functionality for path reparameterization for ForeignScan
in the previous patch, but since the functionality would be useful not
only for such table inheritance cases but for UNION ALL cases, I'd add
the functionality as an independent feature maybe to CF 2014-12.
Thanks,
[1] http://www.postgresql.org/message-id/540da168.3040...@lab.ntt.co.jp
[2]
http://www.postgresql.org/message-id/20140902.142218.253402812.horiguchi.kyot...@lab.ntt.co.jp
Best regards,
Etsuro Fujita
*** 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,2512 ----
(13 rows)
-- ===================================================================
+ -- test constraint exclusion stuff
+ -- ===================================================================
+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c1_check CHECK (c1 > 0);
+ \t on
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM ft1 WHERE c1 <= 0;
+ 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 (("C 1" <= 0))
+
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM ft1 WHERE c1 <= 0;
+ Result
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ One-Time Filter: false
+
+ SET constraint_exclusion = 'partition';
+ \t off
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
+ -- ===================================================================
-- 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,404 ----
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_c1_check CHECK (c1 > 0);
+ \t on
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM ft1 WHERE c1 <= 0;
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM ft1 WHERE c1 <= 0;
+ SET constraint_exclusion = 'partition';
+ \t off
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c1_check;
+
+ -- ===================================================================
-- 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 '@');
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 144,149 **** SET constraint_exclusion = 'partition';
--- 144,168 ----
\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_text INHERIT agg;
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ INSERT INTO agg
+ SELECT x, 5432.0 FROM generate_series(0,1000) x;
+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b;
+ SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b;
+ SELECT * FROM agg_text;
+ SELECT * FROM agg_csv;
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ DELETE FROM agg WHERE a = 100;
+ -- but this should be ignored
+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE;
+ 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,295 ----
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_text INHERIT agg;
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ INSERT INTO agg
+ SELECT x, 5432.0 FROM generate_series(0,1000) x;
+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b;
+ a | b
+ ----+---------
+ 0 | 0.09561
+ 0 | 0.09561
+ 56 | 7.8
+ (3 rows)
+
+ SELECT * FROM ONLY agg WHERE b < 10.0 ORDER BY a, b;
+ a | b
+ ---+---
+ (0 rows)
+
+ SELECT * FROM agg_text;
+ a | b
+ -----+---------
+ 56 | 7.8
+ 100 | 99.097
+ 0 | 0.09561
+ 42 | 324.78
+ (4 rows)
+
+ SELECT * FROM agg_csv;
+ a | b
+ -----+---------
+ 100 | 99.097
+ 0 | 0.09561
+ 42 | 324.78
+ (3 rows)
+
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ ERROR: cannot update foreign table "agg_text"
+ DELETE FROM agg WHERE a = 100;
+ ERROR: cannot delete from foreign table "agg_text"
+ -- but this should be ignored
+ SELECT * FROM agg WHERE b < 10.0 ORDER BY a, b FOR UPDATE;
+ a | b
+ ----+---------
+ 0 | 0.09561
+ 0 | 0.09561
+ 56 | 7.8
+ (3 rows)
+
+ 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
***************
*** 2860,2865 **** NOTICE: NEW: (13,"test triggered !")
--- 2860,3347 ----
(1 row)
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ (6 rows)
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE b SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE a SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ (6 rows)
+
+ DELETE FROM a;
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----+----
+ (0 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ DROP TABLE a CASCADE;
+ NOTICE: drop cascades to foreign table b
+ DROP TABLE loct;
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for update;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for share;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------
+ Update on public.bar
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ -> Hash Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar2.f1 = foo.f1)
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (34 rows)
+
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 101
+ bar | 2 | 102
+ bar | 3 | 103
+ bar | 4 | 4
+ bar2 | 1 | 101
+ bar2 | 2 | 102
+ bar2 | 3 | 103
+ bar2 | 4 | 4
+ (8 rows)
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------
+ Update on public.bar
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
+ Hash Cond: (foo.f1 = bar.f1)
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Hash
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Merge Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))
+ Merge Cond: (bar2.f1 = foo.f1)
+ -> Sort
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Sort Key: bar2.f1
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Sort
+ Output: (ROW(foo.f1)), foo.f1
+ Sort Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ (42 rows)
+
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 201
+ bar | 2 | 202
+ bar | 3 | 203
+ bar | 4 | 104
+ bar2 | 1 | 201
+ bar2 | 2 | 202
+ bar2 | 3 | 203
+ bar2 | 4 | 104
+ (8 rows)
+
+ drop table foo cascade;
+ NOTICE: drop cascades to foreign table foo2
+ drop table bar cascade;
+ NOTICE: drop cascades to foreign table bar2
+ drop table loct1;
+ drop table loct2;
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ drop table ptbl cascade;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table locc
+ drop cascades to foreign table remc
+ drop table ltbl;
+ -- ===================================================================
-- test 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
***************
*** 629,634 **** UPDATE rem1 SET f2 = 'testo';
--- 629,777 ----
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE b SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DELETE FROM a;
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DROP TABLE a CASCADE;
+ DROP TABLE loct;
+
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ select * from bar where f1 in (select f1 from foo) for update;
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ select * from bar where f1 in (select f1 from foo) for share;
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ drop table foo cascade;
+ drop table bar cascade;
+ drop table loct1;
+ drop table loct2;
+
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ drop table ptbl cascade;
+ drop table ltbl;
+
+ -- ===================================================================
-- test 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.
+ These 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
***************
*** 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;
--- 3115,3142 ----
* 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;
--- 3149,3156 ----
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;
--- 3272,3288 ----
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:
--- 3316,3321 ----
***************
*** 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
--- 4309,4316 ----
*/
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);
}
}
--- 4338,4349 ----
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);
--- 4633,4639 ----
/* 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,
--- 4929,4939 ----
/* 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
--- 5534,5540 ----
/* 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
--- 5926,5932 ----
/* 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 */
--- 5937,5953 ----
* 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);
--- 7434,7440 ----
/* 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,
--- 7769,7778 ----
* 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,1397 ----
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (ff1 integer, ff2 text);
+ CREATE VIEW view1 AS SELECT 1;
+ ALTER TABLE view1 INHERIT pt1; -- ERROR
+ ERROR: "view1" is not a table or foreign table
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ ALTER FOREIGN TABLE ct1 NO INHERIT pt1;
+ ALTER FOREIGN TABLE ct1 INHERIT pt1;
+ ALTER FOREIGN TABLE ct2 INHERIT pt1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ ff1 | integer | | plain | |
+ ff2 | text | | extended | |
+ Child tables: ct1,
+ ct2
+
+ \d+ ct1
+ Foreign table "public.ct1"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ \d+ ct2
+ Foreign table "public.ct2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ct1;
+ -- cannot change storage modes for foreign tables
+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL;
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ ff1 | integer | | plain | |
+ ff2 | text | | external | |
+ Child tables: ct1,
+ ct2
+
+ \d+ ct1
+ Foreign table "public.ct1"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ \d+ ct2
+ Foreign table "public.ct2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ct1;
+ DROP FOREIGN TABLE ct2;
+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED;
+ -- connoinherit should be true for NO INHERIT constraint
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | connoinherit
+ ---------+---------+---------+------------+-------------+--------------
+ pt1 | pt1chk1 | c | t | 0 | t
+ pt1 | pt1chk2 | c | t | 0 | f
+ (2 rows)
+
+ -- child does not inherit NO INHERIT constraints
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ct2 INHERIT pt1; -- ERROR
+ ERROR: child table is missing constraint "pt1chk2"
+ ALTER FOREIGN TABLE ct2 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);
+ ALTER FOREIGN TABLE ct2 INHERIT pt1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ ff1 | integer | | plain | |
+ ff2 | text | | extended | |
+ Check constraints:
+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT
+ "pt1chk2" CHECK (ff1 > 10)
+ Child tables: ct1,
+ ct2
+
+ \d+ ct1
+ Foreign table "public.ct1"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Check constraints:
+ "pt1chk2" CHECK (ff1 > 10)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ \d+ ct2
+ Foreign table "public.ct2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Check constraints:
+ "pt1chk2" CHECK (ff1 > 10)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (100, 'pt1');
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10) NOT VALID;
+ NOTICE: merging constraint "pt1chk2" with inherited definition
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ ff1 | integer | | plain | |
+ ff2 | text | | extended | |
+ Check constraints:
+ "pt1chk1" CHECK (ff1 > 0) NO INHERIT
+ "pt1chk2" CHECK (ff1 > 10) NOT VALID
+ Child tables: ct1,
+ ct2
+
+ \d+ ct1
+ Foreign table "public.ct1"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Check constraints:
+ "pt1chk2" CHECK (ff1 > 10)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ \d+ ct2
+ Foreign table "public.ct2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ ff1 | integer | | | plain | |
+ ff2 | text | | | extended | |
+ Check constraints:
+ "pt1chk2" CHECK (ff1 > 10)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+ -- cannot add an OID system column
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+ ERROR: cannot add OID column to foreign table "ct1"
+ DROP VIEW view1;
+ DROP TABLE pt1 CASCADE;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to foreign table ct1
+ drop cascades to foreign table ct2
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
ERROR: foreign-data wrapper "foo" has no handler
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
***************
*** 522,527 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
--- 522,590 ----
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (ff1 integer, ff2 text);
+ CREATE VIEW view1 AS SELECT 1;
+ ALTER TABLE view1 INHERIT pt1; -- ERROR
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ ALTER FOREIGN TABLE ct1 NO INHERIT pt1;
+ ALTER FOREIGN TABLE ct1 INHERIT pt1;
+ ALTER FOREIGN TABLE ct2 INHERIT pt1;
+ \d+ pt1
+ \d+ ct1
+ \d+ ct2
+
+ DROP FOREIGN TABLE ct1;
+
+ -- cannot change storage modes for foreign tables
+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTERNAL;
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ \d+ ct1
+ \d+ ct2
+
+ DROP FOREIGN TABLE ct1;
+ DROP FOREIGN TABLE ct2;
+ ALTER TABLE pt1 ALTER COLUMN ff2 SET STORAGE EXTENDED;
+
+ -- connoinherit should be true for NO INHERIT constraint
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (ff1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ -- child does not inherit NO INHERIT constraints
+ CREATE FOREIGN TABLE ct1 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ CREATE FOREIGN TABLE ct2 (ff1 integer, ff2 text)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ct2 INHERIT pt1; -- ERROR
+ ALTER FOREIGN TABLE ct2 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10);
+ ALTER FOREIGN TABLE ct2 INHERIT pt1;
+ \d+ pt1
+ \d+ ct1
+ \d+ ct2
+
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (100, 'pt1');
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (ff1 > 10) NOT VALID;
+ \d+ pt1
+ \d+ ct1
+ \d+ ct2
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+
+ -- cannot add an OID system column
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+
+ DROP VIEW view1;
+ DROP TABLE pt1 CASCADE;
+
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
