Hi Ashutosh,
Thanks for the review!
(2014/11/28 18:14), Ashutosh Bapat wrote:
On Thu, Nov 27, 2014 at 3:52 PM, Etsuro Fujita
<fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>> wrote:
(2014/11/17 17:55), Ashutosh Bapat wrote:
Here are my review comments for patch fdw-inh-3.patch.
Tests
-------
1. It seems like you have copied from testcase inherit.sql to
postgres_fdw testcase. That's a good thing, but it makes the
test quite
long. May be we should have two tests in postgres_fdw contrib
module,
one for simple cases, and other for inheritance. What do you say?
IMO, the test is not so time-consuming, so it doesn't seem worth
splitting it into two.
I am not worried about the timing but I am worried about the length of
the file and hence ease of debugging in case we find any issues there.
We will leave that for the commiter to decide.
OK
Documentation
--------------------
1. The change in ddl.sgml
- We will refer to the child tables as partitions, though
they
- are in every way normal <productname>PostgreSQL</> tables.
+ We will refer to the child tables as partitions, though
we assume
+ that they are normal <productname>PostgreSQL</> tables.
adds phrase "we assume that", which confuses the intention
behind the
sentence. The original text is intended to highlight the equivalence
between "partition" and "normal table", where as the addition
esp. the
word "assume" weakens that equivalence. Instead now we have to
highlight
the equivalence between "partition" and "normal or foreign
table". The
wording similar to "though they are either normal or foreign tables"
should be used there.
You are right, but I feel that there is something out of place in
saying that there (5.10.2. Implementing Partitioning) because the
procedure there has been written based on normal tables only. Put
another way, if we say that, I think we'd need to add more docs,
describing the syntax and/or the corresponding examples for
foreign-table cases. But I'd like to leave that for another patch.
So, how about the wording "we assume *here* that", instead of "we
assume that", as I added the following notice in the previous
section (5.10.1. Overview)?
@@ -2650,7 +2669,10 @@ 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.
+ 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.)
This looks ok, though, I would like to see final version of the
document. But I think, we will leave that for committer to handle.
OK
2. The wording "some kind of optimization" gives vague picture.
May be
it can be worded as "Since the constraints are assumed to be
true, they
are used in constraint-based query optimization like constraint
exclusion for partitioned tables.".
+ Those constraints are used in some kind of query
optimization such
+ as constraint exclusion for partitioned tables (see
+ <xref linkend="ddl-partitioning">).
Will follow your revision.
Done.
Code
-------
1. In the following change
+/*
* 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.
+ * We fail and return zero if there are no inheritance children or
there are
+ * inheritance children that foreign tables.
The addition should be "there are inheritance children that *are all
*foreign tables. Note the addition "are all".
Sorry, I incorrectly wrote the comment. What I tried to write is
"We fail and return zero if there are no inheritance children or if
we are not in VAC_MODE_SINGLE case and inheritance tree contains at
least one foreign table.".
You might want to use "English" description of VAC_MODE_SINGLE instead
of that macro in the comment, so that reader doesn't have to look up
VAC_MODE_SINGLE. But I think, we will leave this for the committer.
I corrected the comments and translated the macro into the English
description.
2. The function has_foreign() be better named
has_foreign_child()? This
How about "has_foreign_table"?
has_foreign_child() would be better, since these are "children" in the
inheritance hierarchy and not mere "table"s.
Done. But I renamed it to has_foreign_children() because it sounds more
natural at least to me.
function loops through all the tableoids passed even after it
has found
a foreign table. Why can't we return true immediately after
finding the
first foreign table, unless the side effects of heap_open() on
all the
table are required. But I don't see that to be the case, since these
tables are locked already through a previous call to
heap_open(). In the
Good catch! Will fix.
same function instead of argument name parentrelId may be we
should use
name parent_oid, so that we use same notation for parent and
child table
OIDs.
Will fix.
Done.
3. Regarding enum VacuumMode - it's being used only in case of
acquire_inherited_sample_rows(__) and that too, to check only a
single
value of the three defined there. May be we should just infer
that value
inside acquire_inherited_sample_rows(__) or pass a boolean true
or false
from do_analyse_rel() based on the VacuumStmt. I do not see need
for a
separate three value enum of which only one value is used and
also to
pass it down from vacuum() by changing signatures of the minion
functions.
I introduced that for possible future use. See the discussion in [1].
Will leave it for the commiter to decide.
I noticed that the signatures need not to be modified, as you said.
Thanks for pointing that out! So, I revised the patch not to change the
signatures, though I left the enum, renaming it to AnalyzeMode. Let's
have the committer's review.
4. In postgresGetForeignPlan(), the code added by this patch is
required
to handle the case when the row mark is placed on a parent table and
hence is required to be applied on the child table. We need a
comment
explaining this. Otherwise, the three step process to get the
row mark
information isn't clear for a reader.
Will add the comment.
Done.
5. In expand_inherited_rtentry() why do you need a separate variable
hasForeign? Instead of using that variable, you can actually
set/reset
rte->hasForeign since existence of even a single foreign child would
mark that member as true. - After typing this, I got the answer
when I
looked at the function code. Every child's RTE is initially a
copy of
parent's RTE and hence hasForeign status would be inherited by every
child after the first foreign child. I think, this reasoning
should be
added as comment before assignment to rte->hasForeign at the end
of the
function.
As you mentioned, I think we could set rte->hasForeign directly
during the scan for the inheritance set, without the separate
variable, hasForeign. But ISTM that it'd improve code readability
to set rte->hasForeign using the separate variable at the end of the
function because rte->hasForeign has its meaning only when rte->inh
is true and because we know whether rte->inh is true, at the end of
the function.
Fine. Please use "hasForeignChild" instead of just "hasForeign" without
a clue as to what is "foreign" here.
Done. But I renamed it to "hasForeignChildren".
6. The tests in foreign_data.sql are pretty extensive. Thanks
for that.
I think, we should also check the effect of each of the following
command using \d on appropriate tables.
+CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted"
'value');
+ALTER FOREIGN TABLE ft2 NO INHERIT pt1;
+DROP FOREIGN TABLE ft2;
+CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted"
'value');
+ALTER FOREIGN TABLE ft2 INHERIT pt1;
Will fix.
Done.
Apart from the above, I noticed that the patch doesn't consider to
call ExplainForeignModify during EXPLAIN for an inherited
UPDATE/DELETE, as shown below (note that there are no UPDATE remote
queries displayed):
Since there seems to be no objections, I updated the patch as proposed
upthread. Here is an example.
postgres=# explain (format text, verbose) update parent as p set a = a *
2 returning *;
QUERY PLAN
--------------------------------------------------------------------------------
Update on public.parent p (cost=0.00..202.33 rows=11 width=10)
Output: p.a
For public.ft1 p_1
Remote SQL: UPDATE public.mytable_1 SET a = $2 WHERE ctid = $1
RETURNING a
For public.ft2 p_2
Remote SQL: UPDATE public.mytable_2 SET a = $2 WHERE ctid = $1
RETURNING a
-> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=10)
Output: (p.a * 2), p.ctid
-> Foreign Scan on public.ft1 p_1 (cost=100.00..101.16 rows=5
width=10)
Output: (p_1.a * 2), p_1.ctid
Remote SQL: SELECT a, ctid FROM public.mytable_1 FOR UPDATE
-> Foreign Scan on public.ft2 p_2 (cost=100.00..101.16 rows=5
width=10)
Output: (p_2.a * 2), p_2.ctid
Remote SQL: SELECT a, ctid FROM public.mytable_2 FOR UPDATE
(14 rows)
Other changes:
* revised regression tests for contrib/file_fdw to refer to tableoid.
* revised docs a bit further.
Attached are updated patches. Patch fdw-inh-5.patch has been created on
top of patch fdw-chk-5.patch. Patch fdw-chk-5.patch is basically the
same as the previous one fdw-chk-4.patch, but I slightly modified that
one. The changes are the following.
* added to foreign_data.sql more tests for your comments.
* revised docs on ALTER FOREIGN TABLE a bit further.
Thanks,
Best regards,
Etsuro Fujita
*** a/contrib/file_fdw/input/file_fdw.source
--- b/contrib/file_fdw/input/file_fdw.source
***************
*** 62,68 **** CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (format 'csv', null '
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
CREATE FOREIGN TABLE agg_text (
! a int2,
b float4
) SERVER file_server
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N');
--- 62,68 ----
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
CREATE FOREIGN TABLE agg_text (
! a int2 CHECK (a >= 0),
b float4
) SERVER file_server
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N');
***************
*** 72,82 **** CREATE FOREIGN TABLE agg_csv (
--- 72,84 ----
b float4
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
+ ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
CREATE FOREIGN TABLE agg_bad (
a int2,
b float4
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
+ ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
-- per-column options tests
CREATE FOREIGN TABLE text_csv (
***************
*** 134,139 **** DELETE FROM agg_csv WHERE a = 100;
--- 136,153 ----
-- but this should be ignored
SELECT * FROM agg_csv FOR UPDATE;
+ -- constraint exclusion tests
+ \t on
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
+ \t off
+ SELECT * FROM agg_csv WHERE a < 0;
+ SET constraint_exclusion = 'on';
+ \t on
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
+ \t off
+ SELECT * FROM agg_csv WHERE a < 0;
+ SET constraint_exclusion = 'partition';
+
-- 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
***************
*** 78,84 **** ERROR: COPY null representation cannot use newline or carriage return
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
ERROR: filename is required for file_fdw foreign tables
CREATE FOREIGN TABLE agg_text (
! a int2,
b float4
) SERVER file_server
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N');
--- 78,84 ----
CREATE FOREIGN TABLE tbl () SERVER file_server; -- ERROR
ERROR: filename is required for file_fdw foreign tables
CREATE FOREIGN TABLE agg_text (
! a int2 CHECK (a >= 0),
b float4
) SERVER file_server
OPTIONS (format 'text', filename '@abs_srcdir@/data/agg.data', delimiter ' ', null '\N');
***************
*** 88,98 **** CREATE FOREIGN TABLE agg_csv (
--- 88,100 ----
b float4
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.csv', header 'true', delimiter ';', quote '@', escape '"', null '');
+ ALTER FOREIGN TABLE agg_csv ADD CHECK (a >= 0);
CREATE FOREIGN TABLE agg_bad (
a int2,
b float4
) SERVER file_server
OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', delimiter ';', quote '@', escape '"', null '');
+ ALTER FOREIGN TABLE agg_bad ADD CHECK (a >= 0);
-- per-column options tests
CREATE FOREIGN TABLE text_csv (
word1 text OPTIONS (force_not_null 'true'),
***************
*** 219,224 **** SELECT * FROM agg_csv FOR UPDATE;
--- 221,254 ----
42 | 324.78
(3 rows)
+ -- constraint exclusion tests
+ \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
+
+ \t off
+ SELECT * FROM agg_csv WHERE a < 0;
+ a | b
+ ---+---
+ (0 rows)
+
+ SET constraint_exclusion = 'on';
+ \t on
+ EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
+ Result
+ Output: a, b
+ One-Time Filter: false
+
+ \t off
+ SELECT * FROM agg_csv WHERE a < 0;
+ a | b
+ ---+---
+ (0 rows)
+
+ SET constraint_exclusion = 'partition';
-- 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
***************
*** 2556,2561 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
--- 2556,2646 ----
(13 rows)
-- ===================================================================
+ -- test check constraints
+ -- ===================================================================
+ -- Consistent check constraints provide consistent results
+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
+ QUERY PLAN
+ -------------------------------------------------------------------
+ Aggregate
+ Output: count(*)
+ -> Foreign Scan on public.ft1
+ Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 < 0))
+ (4 rows)
+
+ SELECT count(*) FROM ft1 WHERE c2 < 0;
+ count
+ -------
+ 0
+ (1 row)
+
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
+ QUERY PLAN
+ --------------------------------
+ Aggregate
+ Output: count(*)
+ -> Result
+ One-Time Filter: false
+ (4 rows)
+
+ SELECT count(*) FROM ft1 WHERE c2 < 0;
+ count
+ -------
+ 0
+ (1 row)
+
+ SET constraint_exclusion = 'partition';
+ -- Can throw errors on remote side during update
+ INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
+ ERROR: new row for relation "T 1" violates check constraint "c2positive"
+ DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null).
+ CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
+ UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
+ ERROR: new row for relation "T 1" violates check constraint "c2positive"
+ DETAIL: Failing row contains (1, -1, 00001_trig_update, 1970-01-02 08:00:00+00, 1970-01-02 00:00:00, 1, 1 , foo).
+ CONTEXT: Remote SQL command: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
+ -- But inconsistent check constraints provide inconsistent results
+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ QUERY PLAN
+ --------------------------------------------------------------------
+ Aggregate
+ Output: count(*)
+ -> Foreign Scan on public.ft1
+ Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 >= 0))
+ (4 rows)
+
+ SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ count
+ -------
+ 821
+ (1 row)
+
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ QUERY PLAN
+ --------------------------------
+ Aggregate
+ Output: count(*)
+ -> Result
+ One-Time Filter: false
+ (4 rows)
+
+ SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ count
+ -------
+ 0
+ (1 row)
+
+ SET constraint_exclusion = 'partition';
+ -- Can't throw errors on remote side during update
+ INSERT INTO ft1(c1, c2) VALUES(1111, 2);
+ UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
+ -- ===================================================================
-- 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
***************
*** 401,406 **** select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
--- 401,436 ----
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
-- ===================================================================
+ -- test check constraints
+ -- ===================================================================
+
+ -- Consistent check constraints provide consistent results
+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
+ SELECT count(*) FROM ft1 WHERE c2 < 0;
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 < 0;
+ SELECT count(*) FROM ft1 WHERE c2 < 0;
+ SET constraint_exclusion = 'partition';
+ -- Can throw errors on remote side during update
+ INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
+ UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
+
+ -- But inconsistent check constraints provide inconsistent results
+ ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ SET constraint_exclusion = 'on';
+ EXPLAIN (VERBOSE, COSTS false) SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ SELECT count(*) FROM ft1 WHERE c2 >= 0;
+ SET constraint_exclusion = 'partition';
+ -- Can't throw errors on remote side during update
+ INSERT INTO ft1(c1, c2) VALUES(1111, 2);
+ UPDATE ft1 SET c2 = c2 + 1 WHERE c1 = 1;
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2negative;
+
+ -- ===================================================================
-- 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>
***************
*** 153,158 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 155,189 ----
</varlistentry>
<varlistentry>
+ <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form adds a new constraint to a foreign 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>
+
+ <para>
+ Note that constraints on foreign tables cannot be marked
+ <literal>NOT VALID</> since those constraints are simply declarative.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
+ <listitem>
+ <para>
+ This form drops the specified constraint on a foreign table.
+ If <literal>IF EXISTS</literal> is specified and the constraint
+ does not exist, no error is thrown. In this case a notice is issued instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
<listitem>
<para>
***************
*** 285,295 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
</varlistentry>
<varlistentry>
<term><literal>CASCADE</literal></term>
<listitem>
<para>
Automatically drop objects that depend on the dropped column
! (for example, views referencing the column).
</para>
</listitem>
</varlistentry>
--- 316,344 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">table_constraint</replaceable></term>
+ <listitem>
+ <para>
+ New table constraint for the foreign table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
+ <listitem>
+ <para>
+ Name of an existing constraint to drop.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>CASCADE</literal></term>
<listitem>
<para>
Automatically drop objects that depend on the dropped column
! or constraint (for example, views referencing the column).
</para>
</listitem>
</varlistentry>
***************
*** 298,304 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
! Refuse to drop the column if there are any dependent
objects. This is the default behavior.
</para>
</listitem>
--- 347,353 ----
<term><literal>RESTRICT</literal></term>
<listitem>
<para>
! Refuse to drop the column or constraint if there are any dependent
objects. This is the default behavior.
</para>
</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>
--- 414,423 ----
<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</> 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,176 ----
</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 in the foreign table must satisfy.
+ 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>
+
+ <para>
+ Note that check constraints on foreign tables cannot be marked
+ <literal>NO INHERIT</> since those tables are not allowd to be
+ inherited.
+ </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
--- 220,236 ----
</refsect1>
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Constraints on foreign tables are not enforced on insert or update.
+ The definition of a constraint on a foreign table simply declares
+ the constraint holds for all rows in the foreign table. It is
+ the user's responsibility to ensure that the constraint definition
+ matches 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
***************
*** 2222,2227 **** AddRelationNewConstraints(Relation rel,
--- 2222,2241 ----
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("CHECK constraints on foreign tables cannot be marked NOT VALID")));
+
if (cdef->raw_expr != NULL)
{
Assert(cdef->cooked_expr == NULL);
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 479,488 **** 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,
--- 479,484 ----
***************
*** 3154,3160 **** 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)
--- 3150,3156 ----
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)
***************
*** 3168,3174 **** 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)
--- 3164,3170 ----
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("unique or primary key 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("unique, primary key, 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
***************
*** 669,677 **** ERROR: syntax error at or near "WITH OIDS"
--- 669,711 ----
LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS;
^
CREATE FOREIGN TABLE ft1 (
+ c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY,
+ c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ERROR: unique or primary key constraints are not supported on foreign tables
+ LINE 2: c1 integer OPTIONS ("param 1" 'val1') PRIMARY KEY,
+ ^
+ CREATE TABLE ref_table (id integer PRIMARY KEY);
+ CREATE FOREIGN TABLE ft1 (
+ c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id),
+ c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ERROR: foreign key constraints are not supported on foreign tables
+ LINE 2: c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table ...
+ ^
+ DROP TABLE ref_table;
+ CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date,
+ UNIQUE (c3)
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ERROR: unique, primary key, exclusion, or foreign key constraints are not supported on foreign tables
+ LINE 5: UNIQUE (c3)
+ ^
+ 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,
+ CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date)
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
***************
*** 682,687 **** COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
--- 716,724 ----
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)
+ "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
Server: s0
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
***************
*** 740,745 **** ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
--- 777,785 ----
c8 | text | | (p2 'V2') | extended | |
c9 | integer | | | plain | |
c10 | integer | | (p1 'v1') | plain | |
+ Check constraints:
+ "ft1_c2_check" CHECK (c2 <> ''::text)
+ "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
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;
--- 788,809 ----
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 PRIMARY KEY (c7); -- ERROR
! ERROR: unique, primary key, exclusion, or foreign key constraints are not supported on foreign tables
! LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7);
^
! 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: CHECK constraints on foreign tables cannot be marked NOT VALID
! ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0);
! ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
ERROR: "ft1" is not a table
+ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
+ 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 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;
--- 831,839 ----
c7 | integer | | (p1 'v1', p2 'v2')
c8 | text | | (p2 'V2')
c10 | integer | | (p1 'v1')
+ Check constraints:
+ "ft1_c2_check" CHECK (c2 <> ''::text)
+ "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date)
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
***************
*** 269,277 **** CREATE FOREIGN TABLE ft1 (); -- ERROR
--- 269,301 ----
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') PRIMARY KEY,
+ c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ CREATE TABLE ref_table (id integer PRIMARY KEY);
+ CREATE FOREIGN TABLE ft1 (
+ c1 integer OPTIONS ("param 1" 'val1') REFERENCES ref_table (id),
+ c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ DROP TABLE ref_table;
+ CREATE FOREIGN TABLE ft1 (
c1 integer OPTIONS ("param 1" 'val1') NOT NULL,
c2 text OPTIONS (param2 'val2', param3 'val3'),
+ c3 date,
+ UNIQUE (c3)
+ ) 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 <> '') 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,
+ CHECK (c3 BETWEEN '1994-01-01'::date AND '1994-01-31'::date)
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
COMMENT ON FOREIGN TABLE ft1 IS 'ft1';
COMMENT ON COLUMN ft1.c1 IS 'ft1.c1';
***************
*** 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 '@');
--- 338,351 ----
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 PRIMARY KEY (c7); -- ERROR
! 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 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
! ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
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
***************
*** 148,153 **** EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
--- 148,167 ----
SELECT * FROM agg_csv WHERE a < 0;
SET constraint_exclusion = 'partition';
+ -- table inheritance tests
+ CREATE TABLE agg (a int2, b float4);
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ SELECT tableoid::regclass, * FROM agg;
+ SELECT tableoid::regclass, * FROM agg_csv;
+ SELECT tableoid::regclass, * FROM ONLY agg;
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ DELETE FROM agg WHERE a = 100;
+ -- but this should be ignored
+ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
+ ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
+ DROP TABLE agg;
+
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/file_fdw/output/file_fdw.source
--- b/contrib/file_fdw/output/file_fdw.source
***************
*** 249,254 **** SELECT * FROM agg_csv WHERE a < 0;
--- 249,294 ----
(0 rows)
SET constraint_exclusion = 'partition';
+ -- table inheritance tests
+ CREATE TABLE agg (a int2, b float4);
+ ALTER FOREIGN TABLE agg_csv INHERIT agg;
+ SELECT tableoid::regclass, * FROM agg;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ SELECT tableoid::regclass, * FROM agg_csv;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ SELECT tableoid::regclass, * FROM ONLY agg;
+ tableoid | a | b
+ ----------+---+---
+ (0 rows)
+
+ -- updates aren't supported
+ UPDATE agg SET a = 1;
+ ERROR: cannot update foreign table "agg_csv"
+ DELETE FROM agg WHERE a = 100;
+ ERROR: cannot delete from foreign table "agg_csv"
+ -- but this should be ignored
+ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
+ tableoid | a | b
+ ----------+-----+---------
+ agg_csv | 100 | 99.097
+ agg_csv | 0 | 0.09561
+ agg_csv | 42 | 324.78
+ (3 rows)
+
+ ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
+ DROP TABLE agg;
-- privilege tests
SET ROLE file_fdw_superuser;
SELECT * FROM agg_text ORDER BY a;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 2994,2999 **** NOTICE: NEW: (13,"test triggered !")
--- 2994,3511 ----
(1 row)
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | aaa
+ a | aaaa
+ a | aaaaa
+ a | aaaaaa
+ a | aaaaaaa
+ a | aaaaaaaa
+ (6 rows)
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | bbb
+ b | bbbb
+ b | bbbbb
+ b | bbbbbb
+ b | bbbbbbb
+ b | bbbbbbbb
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----------+----
+ b | bbb |
+ b | bbbb |
+ b | bbbbb |
+ b | bbbbbb |
+ b | bbbbbbb |
+ b | bbbbbbbb |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE b SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+--------
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ a | zzzzzz
+ (6 rows)
+
+ UPDATE a SET aa='new';
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ b | new
+ (12 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+-----+----
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ b | new |
+ (6 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+-----
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ a | new
+ (6 rows)
+
+ DELETE FROM a;
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ relname | aa | bb
+ ---------+----+----
+ (0 rows)
+
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+ relname | aa
+ ---------+----
+ (0 rows)
+
+ DROP TABLE a CASCADE;
+ NOTICE: drop cascades to foreign table b
+ DROP TABLE loct;
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for update;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------
+ LockRows
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ -> Hash Join
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Append
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid, bar.tableoid, bar.*
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.ctid, bar2.tableoid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (22 rows)
+
+ select * from bar where f1 in (select f1 from foo) for share;
+ f1 | f2
+ ----+----
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ (6 rows)
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------
+ Update on public.bar
+ For public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar.f1 = foo.f1)
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ -> Hash Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.tableoid, foo.*
+ Hash Cond: (bar2.f1 = foo.f1)
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Hash
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> HashAggregate
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ Group Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: foo.ctid, foo.tableoid, foo.*, foo.f1
+ -> Foreign Scan on public.foo2
+ Output: foo2.ctid, foo2.tableoid, foo2.*, foo2.f1
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
+ (36 rows)
+
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 101
+ bar | 2 | 102
+ bar | 3 | 103
+ bar | 4 | 4
+ bar2 | 1 | 101
+ bar2 | 2 | 102
+ bar2 | 3 | 103
+ bar2 | 4 | 4
+ (8 rows)
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------
+ Update on public.bar
+ For public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1
+ -> Hash Join
+ Output: bar.f1, (bar.f2 + 100), bar.ctid, (ROW(foo.f1))
+ Hash Cond: (foo.f1 = bar.f1)
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Hash
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Seq Scan on public.bar
+ Output: bar.f1, bar.f2, bar.ctid
+ -> Merge Join
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))
+ Merge Cond: (bar2.f1 = foo.f1)
+ -> Sort
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Sort Key: bar2.f1
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ -> Sort
+ Output: (ROW(foo.f1)), foo.f1
+ Sort Key: foo.f1
+ -> Append
+ -> Seq Scan on public.foo
+ Output: ROW(foo.f1), foo.f1
+ -> Foreign Scan on public.foo2
+ Output: ROW(foo2.f1), foo2.f1
+ Remote SQL: SELECT f1 FROM public.loct1
+ -> Seq Scan on public.foo foo_1
+ Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)
+ -> Foreign Scan on public.foo2 foo2_1
+ Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)
+ Remote SQL: SELECT f1 FROM public.loct1
+ (44 rows)
+
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+ relname | f1 | f2
+ ---------+----+-----
+ bar | 1 | 201
+ bar | 2 | 202
+ bar | 3 | 203
+ bar | 4 | 104
+ bar2 | 1 | 201
+ bar2 | 2 | 202
+ bar2 | 3 | 203
+ bar2 | 4 | 104
+ (8 rows)
+
+ drop table foo cascade;
+ NOTICE: drop cascades to foreign table foo2
+ drop table bar cascade;
+ NOTICE: drop cascades to foreign table bar2
+ drop table loct1;
+ drop table loct2;
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ ?column?
+ ----------
+ 1
+ (1 row)
+
+ update ptbl set b = null where current of c;
+ ERROR: WHERE CURRENT OF is not supported for this table type
+ rollback;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 1 | foo
+ 2 | bar
+ (2 rows)
+
+ -- Test TRUNCATE
+ truncate ptbl;
+ select * from ptbl;
+ a | b
+ ---+-----
+ 2 | bar
+ (1 row)
+
+ select * from only ptbl;
+ a | b
+ ---+---
+ (0 rows)
+
+ select * from locc;
+ a | b
+ ---+---
+ (0 rows)
+
+ select * from remc;
+ a | b
+ ---+-----
+ 2 | bar
+ (1 row)
+
+ truncate remc; -- ERROR
+ ERROR: "remc" is not a table
+ drop table ptbl cascade;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to table locc
+ drop cascades to foreign table remc
+ drop table ltbl;
+ -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
CREATE SCHEMA import_source;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 824,834 **** postgresGetForeignPlan(PlannerInfo *root,
{
RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid);
if (rc)
{
/*
! * Relation is specified as a FOR UPDATE/SHARE target, so handle
! * that.
*
* For now, just ignore any [NO] KEY specification, since (a) it's
* not clear what that means for a remote table that we don't have
--- 824,847 ----
{
RowMarkClause *rc = get_parse_rowmark(root->parse, baserel->relid);
+ /*
+ * It's possible that relation is contained in an inheritance set and
+ * that parent relation is selected FOR UPDATE/SHARE. If so, get the
+ * RowMarkClause for parent relation.
+ */
+ if (rc == NULL)
+ {
+ PlanRowMark *prm = get_plan_rowmark(root->rowMarks, baserel->relid);
+
+ if (prm && prm->rti != prm->prti)
+ rc = get_parse_rowmark(root->parse, prm->prti);
+ }
+
if (rc)
{
/*
! * Relation or parent relation is specified as a FOR UPDATE/SHARE
! * target, so handle that.
*
* For now, just ignore any [NO] KEY specification, since (a) it's
* not clear what that means for a remote table that we don't have
***************
*** 1680,1690 **** postgresExplainForeignModify(ModifyTableState *mtstate,
--- 1693,1717 ----
int subplan_index,
ExplainState *es)
{
+ ModifyTable *plan = (ModifyTable *) mtstate->ps.plan;
+
if (es->verbose)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
+ /*
+ * If we are in an inherited UPDATE/DELETE case, show the SQL statement
+ * in a group of the target relation information.
+ */
+ if (subplan_index > 0)
+ {
+ ExplainOpenForeignModifyGroup(plan, subplan_index, es);
+ ExplainPropertyText("Remote SQL", sql, es);
+ ExplainCloseForeignModifyGroup(plan, subplan_index, es);
+ return;
+ }
+
ExplainPropertyText("Remote SQL", sql, es);
}
}
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 661,666 **** UPDATE rem1 SET f2 = 'testo';
--- 661,817 ----
INSERT INTO rem1(f2) VALUES ('test') RETURNING ctid;
-- ===================================================================
+ -- test inheritance features
+ -- ===================================================================
+
+ CREATE TABLE a (aa TEXT);
+ CREATE TABLE loct (aa TEXT, bb TEXT);
+ CREATE FOREIGN TABLE b (bb TEXT) INHERITS (a)
+ SERVER loopback OPTIONS (table_name 'loct');
+
+ INSERT INTO a(aa) VALUES('aaa');
+ INSERT INTO a(aa) VALUES('aaaa');
+ INSERT INTO a(aa) VALUES('aaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaa');
+ INSERT INTO a(aa) VALUES('aaaaaaaa');
+
+ INSERT INTO b(aa) VALUES('bbb');
+ INSERT INTO b(aa) VALUES('bbbb');
+ INSERT INTO b(aa) VALUES('bbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbb');
+ INSERT INTO b(aa) VALUES('bbbbbbbb');
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE b SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ UPDATE a SET aa='new';
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DELETE FROM a;
+
+ SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
+ SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
+ SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
+
+ DROP TABLE a CASCADE;
+ DROP TABLE loct;
+
+ -- Check SELECT FOR UPDATE/SHARE with an inherited source table
+ create table loct1 (f1 int, f2 int, f3 int);
+ create table loct2 (f1 int, f2 int, f3 int);
+
+ create table foo (f1 int, f2 int);
+ create foreign table foo2 (f3 int) inherits (foo)
+ server loopback options (table_name 'loct1');
+ create table bar (f1 int, f2 int);
+ create foreign table bar2 (f3 int) inherits (bar)
+ server loopback options (table_name 'loct2');
+
+ insert into foo values(1,1);
+ insert into foo values(3,3);
+ insert into foo2 values(2,2,2);
+ insert into foo2 values(3,3,3);
+ insert into bar values(1,1);
+ insert into bar values(2,2);
+ insert into bar values(3,3);
+ insert into bar values(4,4);
+ insert into bar2 values(1,1,1);
+ insert into bar2 values(2,2,2);
+ insert into bar2 values(3,3,3);
+ insert into bar2 values(4,4,4);
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for update;
+ select * from bar where f1 in (select f1 from foo) for update;
+
+ explain (verbose, costs off)
+ select * from bar where f1 in (select f1 from foo) for share;
+ select * from bar where f1 in (select f1 from foo) for share;
+
+ -- Check UPDATE with inherited target and an inherited source table
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ -- Check UPDATE with inherited target and an appendrel subquery
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+ update bar set f2 = f2 + 100
+ from
+ ( select f1 from foo union all select f1+3 from foo ) ss
+ where bar.f1 = ss.f1;
+
+ select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
+
+ drop table foo cascade;
+ drop table bar cascade;
+ drop table loct1;
+ drop table loct2;
+
+ -- Test that WHERE CURRENT OF is not supported
+ create table ltbl (a int, b text);
+
+ create table ptbl (a int, b text);
+ create table locc () inherits (ptbl);
+ insert into locc values(1, 'foo');
+
+ create foreign table remc () inherits (ptbl)
+ server loopback options (table_name 'ltbl');
+ insert into remc values(2, 'bar');
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'foo';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ begin;
+ declare c cursor for select 1 from ptbl where b = 'bar';
+ fetch from c;
+ update ptbl set b = null where current of c;
+ rollback;
+
+ select * from ptbl;
+
+ -- Test TRUNCATE
+ truncate ptbl;
+ select * from ptbl;
+ select * from only ptbl;
+ select * from locc;
+ select * from remc;
+ truncate remc; -- ERROR
+
+ drop table ptbl cascade;
+ drop table ltbl;
+
+ -- ===================================================================
-- test IMPORT FOREIGN SCHEMA
-- ===================================================================
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
***************
*** 2503,2508 **** VALUES ('Albany', NULL, NULL, 'NY');
--- 2503,2526 ----
further privileges to be granted.
</para>
+ <para>
+ Note that a foreign table can also inherit from more than one parent
+ table (see <xref linkend="ddl-foreign-data"> for an overview of
+ foreign tables).
+ Like normal tables, table inheritance is typically established when
+ the foreign table is created, using the <literal>INHERITS</> clause
+ of the <xref linkend="sql-createforeigntable"> statement.
+ Alternatively, a foreign table which is already defined
+ in a compatible way can have a new parent relationship added, using
+ the <literal>INHERIT</literal> variant of
+ <xref linkend="sql-alterforeigntable">.
+ Similarly an inheritance link can be removed from a child using the
+ <literal>NO INHERIT</literal> variant of <command>ALTER FOREIGN TABLE</>.
+ <command>CREATE FOREIGN TABLE</> and <command>ALTER FOREIGN TABLE</>
+ follow the same rules for duplicate column merging and rejection as
+ <command>CREATE TABLE</> and <command>ALTER TABLE</>, respectively.
+ </para>
+
<sect2 id="ddl-inherit-caveats">
<title>Caveats</title>
***************
*** 2650,2656 **** VALUES ('Albany', NULL, NULL, 'NY');
table of a single parent table. The parent table itself is normally
empty; it exists just to represent the entire data set. You should be
familiar with inheritance (see <xref linkend="ddl-inherit">) before
! attempting to set up partitioning.
</para>
<para>
--- 2668,2677 ----
table of a single parent table. The parent table itself is normally
empty; it exists just to represent the entire data set. You should be
familiar with inheritance (see <xref linkend="ddl-inherit">) before
! attempting to set up partitioning. (The setup and management of
! partitioned tables illustrated in this section assume that each
! partition is a normal table. However, you can do that in a similar way
! for cases where some or all partitions are foreign tables.)
</para>
<para>
***************
*** 2713,2720 **** VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
! We will refer to the child tables as partitions, though they
! are in every way normal <productname>PostgreSQL</> tables.
</para>
</listitem>
--- 2734,2741 ----
</para>
<para>
! We will refer to the child tables as partitions, though we assume
! here that they are normal <productname>PostgreSQL</> tables.
</para>
</listitem>
*** a/doc/src/sgml/ref/alter_foreign_table.sgml
--- b/doc/src/sgml/ref/alter_foreign_table.sgml
***************
*** 48,53 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 48,55 ----
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
+ INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
+ NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ])
</synopsis>
***************
*** 195,200 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 197,222 ----
</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>
***************
*** 385,390 **** ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceab
--- 407,422 ----
</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,1028 ----
</para>
<para>
+ A recursive <literal>SET STORAGE</literal> operation will make
+ the storage mode unchanged for any column of descendant tables
+ that are foreign.
+ </para>
+
+ <para>
+ A recursive <literal>SET WITH OIDS</literal> operation will be
+ rejected if any of descendant tables is foreign, since it is
+ not permitted to add an <literal>oid</literal> system column to
+ foreign tables.
+ </para>
+
+ <para>
The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
and <literal>TABLESPACE</> actions never recurse to descendant tables;
that is, they always act as though <literal>ONLY</> were specified.
***************
*** 1018,1023 **** ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
--- 1031,1044 ----
</para>
<para>
+ When adding a <literal>CHECK</> constraint with the <literal>NOT VALID
+ </literal> option recursively, the inherited constraint on a descendant
+ table that is foreign will be marked valid without checking consistency
+ with the foreign server. It is the user's resposibility to ensure that
+ the constraint definition matches the remote side.
+ </para>
+
+ <para>
Changing any part of a system catalog table is not permitted.
</para>
*** a/doc/src/sgml/ref/analyze.sgml
--- b/doc/src/sgml/ref/analyze.sgml
***************
*** 200,205 **** ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> [
--- 200,212 ----
</para>
<para>
+ The inheritance statistics for a parent table that contains one or more
+ children that are foreign tables are collected only when explicitly
+ selected. If any of the foreign table's wrapper does not support
+ <command>ANALYZE</command>, the command prints a warning and does nothing.
+ </para>
+
+ <para>
If the table being analyzed is completely empty, <command>ANALYZE</command>
will not record new statistics for that table. Any existing statistics
will be retained.
*** a/doc/src/sgml/ref/create_foreign_table.sgml
--- b/doc/src/sgml/ref/create_foreign_table.sgml
***************
*** 23,28 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 23,29 ----
| <replaceable>table_constraint</replaceable> }
[, ... ]
] )
+ [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
SERVER <replaceable class="parameter">server_name</replaceable>
[ OPTIONS ( <replaceable class="PARAMETER">option</replaceable> '<replaceable class="PARAMETER">value</replaceable>' [, ... ] ) ]
***************
*** 121,126 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 122,146 ----
</varlistentry>
<varlistentry>
+ <term><literal>INHERITS ( <replaceable>parent_table</replaceable> [, ... ] )</literal></term>
+ <listitem>
+ <para>
+ The optional <literal>INHERITS</> clause specifies a list of
+ tables from which the new foreign table automatically inherits
+ all columns. See the similar form of
+ <xref linkend="sql-createtable"> for more details.
+ </para>
+
+ <para>
+ Note that unlike <command>CREATE TABLE</>, column
+ <literal>STORAGE</> settings are not copied from parent tables,
+ resulting in the copied columns in the new foreign table having
+ type-specific default settings.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>NOT NULL</></term>
<listitem>
<para>
***************
*** 192,197 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
--- 212,229 ----
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">parent_table</replaceable></term>
+ <listitem>
+ <para>
+ The name of an existing table from which the new foreign table
+ automatically inherits all columns. The specified parent table
+ must be an ordinary table. See <xref linkend="ddl-inherit"> for
+ more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">server_name</replaceable></term>
<listitem>
<para>
***************
*** 228,234 **** CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="PARAMETER">table_name
The definition of a constraint on a foreign table simply declares
the constraint holds for all rows in the foreign table. It is
the user's responsibility to ensure that the constraint definition
! matches the remote side.
</para>
</refsect1>
--- 260,275 ----
The definition of a constraint on a foreign table simply declares
the constraint holds for all rows in the foreign table. It is
the user's responsibility to ensure that the constraint definition
! matches the remote side. Since the constraints are assumed to be
! true, they are used in constraint-based query optimization like
! 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 of parent tables
! has an <literal>oid</> system column.
</para>
</refsect1>
*** a/doc/src/sgml/ref/truncate.sgml
--- b/doc/src/sgml/ref/truncate.sgml
***************
*** 179,184 **** TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [
--- 179,189 ----
This is similar to the usual behavior of <function>currval()</> after
a failed transaction.
</para>
+
+ <para>
+ A recursive <command>TRUNCATE</> ignores any descendant tables that are
+ foreign, and applies the operation to descendant tables that are normal.
+ </para>
</refsect1>
<refsect1>
*** a/src/backend/commands/analyze.c
--- b/src/backend/commands/analyze.c
***************
*** 56,61 ****
--- 56,69 ----
#include "utils/tqual.h"
+ /* Possible modes for ANALYZE */
+ typedef enum
+ {
+ ANL_MODE_ALL, /* Analyze all relations */
+ ANL_MODE_SINGLE, /* Analyze a specific relation */
+ ANL_MODE_AUTOVACUUM /* Autovacuum worker */
+ } AnalyzeMode;
+
/* Data structure for Algorithm S from Knuth 3.4.2 */
typedef struct
{
***************
*** 81,86 **** typedef struct AnlIndexData
--- 89,95 ----
int default_statistics_target = 100;
/* A few variables that don't seem worth passing around as parameters */
+ static AnalyzeMode anl_mode;
static MemoryContext anl_context = NULL;
static BufferAccessStrategy vac_strategy;
***************
*** 102,107 **** static int acquire_sample_rows(Relation onerel, int elevel,
--- 111,117 ----
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
static int compare_rows(const void *a, const void *b);
+ static bool has_foreign_children(Oid parentOID, List *tableOIDs);
static int acquire_inherited_sample_rows(Relation onerel, int elevel,
HeapTuple *rows, int targrows,
double *totalrows, double *totaldeadrows);
***************
*** 130,135 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
--- 140,154 ----
elevel = DEBUG2;
/* Set up static variables */
+ if (IsAutoVacuumWorkerProcess())
+ anl_mode = ANL_MODE_AUTOVACUUM;
+ else
+ {
+ if (!vacstmt->relation)
+ anl_mode = ANL_MODE_ALL;
+ else
+ anl_mode = ANL_MODE_SINGLE;
+ }
vac_strategy = bstrategy;
/*
***************
*** 297,305 **** analyze_rel(Oid relid, VacuumStmt *vacstmt,
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * If we supported foreign tables in inheritance trees,
! * acquire_inherited_sample_rows would need to determine the appropriate
! * acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
--- 316,323 ----
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
! * For the inherited case, acquire_inherited_sample_rows determines the
! * appropriate acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, VacuumStmt *vacstmt,
***************
*** 1444,1454 **** compare_rows(const void *a, const void *b)
/*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1462,1512 ----
/*
+ * Detect wether the inheritance tree contains any foreign tables
+ */
+ static bool
+ has_foreign_children(Oid parentOID, List *tableOIDs)
+ {
+ bool found;
+ ListCell *lc;
+
+ /* There are no children */
+ if (list_length(tableOIDs) < 2)
+ return false;
+
+ found = false;
+ foreach(lc, tableOIDs)
+ {
+ Oid childOID = lfirst_oid(lc);
+ Relation childrel;
+
+ /* Parent should not be foreign */
+ if (childOID == parentOID)
+ continue;
+
+ /* We already got the needed lock */
+ childrel = heap_open(childOID, NoLock);
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /* Found it */
+ found = true;
+ }
+ heap_close(childrel, NoLock);
+
+ if (found)
+ return true;
+ }
+ return false;
+ }
+
+ /*
* acquire_inherited_sample_rows -- acquire sample rows from inheritance tree
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
! * We fail and return zero if there are no inheritance children, or if
! * inheritance tree contains any foreign tables and we are doing an
! * ANALYZE-with-no-parameter or in an autovacuum process.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
***************
*** 1457,1462 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1515,1521 ----
{
List *tableOIDs;
Relation *rels;
+ AcquireSampleRowsFunc *acquirefunc;
double *relblocks;
double totalblocks;
int numrows,
***************
*** 1491,1500 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1550,1580 ----
}
/*
+ * If we are doing an ANALYZE-with-no-parameter or in an autovacuum process
+ * and inheritance tree contains any foreign tables, then fail.
+ */
+ if (anl_mode != ANL_MODE_SINGLE)
+ {
+ if (has_foreign_children(RelationGetRelid(onerel), tableOIDs))
+ {
+ ereport(elevel,
+ (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains foreign tables",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel)),
+ errhint("Try ANALYZE \"%s.%s\" for the inheritance statistics.",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel))));
+ return 0;
+ }
+ }
+
+ /*
* Count the blocks in all the relations. The result could overflow
* BlockNumber, so we use double arithmetic.
*/
rels = (Relation *) palloc(list_length(tableOIDs) * sizeof(Relation));
+ acquirefunc = (AcquireSampleRowsFunc *) palloc(list_length(tableOIDs)
+ * sizeof(AcquireSampleRowsFunc));
relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double));
totalblocks = 0;
nrels = 0;
***************
*** 1516,1522 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
}
rels[nrels] = childrel;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
totalblocks += relblocks[nrels];
nrels++;
}
--- 1596,1631 ----
}
rels[nrels] = childrel;
!
! if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
! {
! FdwRoutine *fdwroutine;
! BlockNumber relpages = 0;
! bool ok = false;
!
! /* Check whether the FDW supports analysis */
! fdwroutine = GetFdwRoutineForRelation(childrel, false);
! if (fdwroutine->AnalyzeForeignTable != NULL)
! ok = fdwroutine->AnalyzeForeignTable(childrel,
! &acquirefunc[nrels],
! &relpages);
! if (!ok)
! {
! /* Give up if the FDW doesn't support analysis */
! ereport(WARNING,
! (errmsg("skipping analyze of inheritance tree \"%s\" --- cannot analyze foreign table \"%s\"",
! RelationGetRelationName(onerel),
! RelationGetRelationName(childrel))));
! return 0;
! }
! relblocks[nrels] = (double) relpages;
! }
! else
! {
! acquirefunc[nrels] = acquire_sample_rows;
! relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
! }
!
totalblocks += relblocks[nrels];
nrels++;
}
***************
*** 1534,1539 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
--- 1643,1649 ----
{
Relation childrel = rels[i];
double childblocks = relblocks[i];
+ AcquireSampleRowsFunc childacquirefunc = acquirefunc[i];
if (childblocks > 0)
{
***************
*** 1549,1560 **** acquire_inherited_sample_rows(Relation onerel, int elevel,
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = acquire_sample_rows(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
--- 1659,1670 ----
tdrows;
/* Fetch a random sample of the child's rows */
! childrows = childacquirefunc(childrel,
! elevel,
! rows + numrows,
! childtargrows,
! &trows,
! &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
***************
*** 2230,2259 **** ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
}
/*
* Show extra information for a ModifyTable node
*/
static void
show_modifytable_info(ModifyTableState *mtstate, ExplainState *es)
{
! FdwRoutine *fdwroutine = mtstate->resultRelInfo->ri_FdwRoutine;
! /*
! * If the first target relation is a foreign table, call its FDW to
! * display whatever additional fields it wants to. For now, we ignore the
! * possibility of other targets being foreign tables, although the API for
! * ExplainForeignModify is designed to allow them to be processed.
! */
! if (fdwroutine != NULL &&
! fdwroutine->ExplainForeignModify != NULL)
{
! ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
! List *fdw_private = (List *) linitial(node->fdwPrivLists);
! fdwroutine->ExplainForeignModify(mtstate,
! mtstate->resultRelInfo,
! fdw_private,
! 0,
! es);
}
}
--- 2230,2327 ----
}
/*
+ * Open a group for foreign modification
+ */
+ void
+ ExplainOpenForeignModifyGroup(ModifyTable *plan, int subplan_index,
+ ExplainState *es)
+ {
+ char *objectname;
+ char *namespace;
+ char *refname;
+ Index rti;
+ RangeTblEntry *rte;
+
+ Assert(subplan_index > 0);
+
+ /* Open a group */
+ ExplainOpenGroup("Foreign Modification", "Foreign Modification", true, es);
+
+ /* Show the target relation information */
+ Assert(plan->resultRelations != NIL);
+ rti = list_nth_int(plan->resultRelations, subplan_index);
+ rte = rt_fetch(rti, es->rtable);
+ /* Assert it's on a real relation */
+ Assert(rte->rtekind == RTE_RELATION);
+ objectname = get_rel_name(rte->relid);
+ namespace = get_namespace_name(get_rel_namespace(rte->relid));
+ refname = (char *) list_nth(es->rtable_names, rti - 1);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfoString(es->str, "For");
+ appendStringInfo(es->str, " %s.%s", quote_identifier(namespace),
+ quote_identifier(objectname));
+ if (strcmp(refname, objectname) != 0)
+ appendStringInfo(es->str, " %s", quote_identifier(refname));
+ appendStringInfoChar(es->str, '\n');
+ es->indent++;
+ }
+ else
+ {
+ ExplainPropertyText("Relation Name", objectname, es);
+ ExplainPropertyText("Schema", namespace, es);
+ ExplainPropertyText("Alias", refname, es);
+ }
+ }
+
+ /*
+ * Close a group for foreign modification
+ */
+ void
+ ExplainCloseForeignModifyGroup(ModifyTable *plan, int subplan_index,
+ ExplainState *es)
+ {
+ Assert(subplan_index > 0);
+
+ /* Undo the indentation we added in text format */
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ es->indent--;
+
+ /* Close a group */
+ ExplainCloseGroup("Foreign Modification", "Foreign Modification", true, es);
+ }
+
+ /*
* Show extra information for a ModifyTable node
*/
static void
show_modifytable_info(ModifyTableState *mtstate, ExplainState *es)
{
! ModifyTable *plan = (ModifyTable *) mtstate->ps.plan;
! int j;
! for (j = 0; j < mtstate->mt_nplans; j++)
{
! ResultRelInfo *resultRelInfo = mtstate->resultRelInfo + j;
! FdwRoutine *fdwroutine = resultRelInfo->ri_FdwRoutine;
! /*
! * If the target relation is a foreign table, call its FDW to display
! * whatever additional fields it wants to.
! */
! if (fdwroutine != NULL &&
! fdwroutine->ExplainForeignModify != NULL)
! {
! List *fdw_private = (List *) list_nth(plan->fdwPrivLists, j);
!
! fdwroutine->ExplainForeignModify(mtstate,
! resultRelInfo,
! fdw_private,
! j,
! es);
! }
}
}
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 318,324 **** static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel);
static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode);
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
LOCKMODE lockmode);
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
--- 318,325 ----
static void ATSimplePermissions(Relation rel, int allowed_targets);
static void ATWrongRelkindError(Relation rel, int allowed_targets);
static void ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse,
! bool include_foreign, LOCKMODE lockmode);
static void ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
LOCKMODE lockmode);
static List *find_typed_table_dependencies(Oid typeOid, const char *typeName,
***************
*** 568,573 **** DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
--- 569,598 ----
stmt->relation->relpersistence,
&inheritOids, &old_constraints, &parentOidCount);
+ if (relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /*
+ * Don't allow a foreign table to inherit from parents that have OID
+ * system columns.
+ */
+ if (parentOidCount > 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot inherit from relation with OIDs")));
+
+ /*
+ * Reset the storage parameter for inherited attributes that have
+ * non-default values.
+ */
+ foreach(listptr, schema)
+ {
+ ColumnDef *colDef = lfirst(listptr);
+
+ if (colDef->storage != 0)
+ colDef->storage = 0;
+ }
+ }
+
/*
* Create a tuple descriptor from the relation schema. Note that this
* deals with column names, types, and NOT NULL constraints, but not
***************
*** 1025,1030 **** ExecuteTruncate(TruncateStmt *stmt)
--- 1050,1061 ----
/* find_all_inheritors already got lock */
rel = heap_open(childrelid, NoLock);
+ /* ignore if the rel is a foreign table */
+ if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ heap_close(rel, NoLock);
+ continue;
+ }
truncate_check_rel(rel);
rels = lappend(rels, rel);
relids = lappend_oid(relids, childrelid);
***************
*** 3102,3125 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
* rules.
*/
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_DROP;
break;
case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* Performs own permission checks */
ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode);
pass = AT_PASS_MISC;
--- 3133,3160 ----
* rules.
*/
ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_DROP;
break;
case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_ADD_CONSTR;
break;
case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
/* Performs own permission checks */
ATPrepSetStatistics(rel, cmd->name, cmd->def, lockmode);
pass = AT_PASS_MISC;
***************
*** 3132,3138 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
--- 3167,3174 ----
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
! /* Don't recurse to child tables that are foreign */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, false, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
break;
***************
*** 3254,3264 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
! ATSimplePermissions(rel, ATT_TABLE);
/* This command never recurses */
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
--- 3290,3306 ----
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* This command never recurses */
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
+ case AT_DropInherit: /* NO INHERIT */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
***************
*** 3292,3298 **** ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
- case AT_DropInherit: /* NO INHERIT */
case AT_AddOf: /* OF */
case AT_DropOf: /* NOT OF */
case AT_EnableRowSecurity:
--- 3334,3339 ----
***************
*** 4292,4298 **** ATWrongRelkindError(Relation rel, int allowed_targets)
*/
static void
ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse, LOCKMODE lockmode)
{
/*
* Propagate to children if desired. Non-table relations never have
--- 4333,4340 ----
*/
static void
ATSimpleRecursion(List **wqueue, Relation rel,
! AlterTableCmd *cmd, bool recurse,
! bool include_foreign, LOCKMODE lockmode)
{
/*
* Propagate to children if desired. Non-table relations never have
***************
*** 4320,4327 **** ATSimpleRecursion(List **wqueue, Relation rel,
continue;
/* find_all_inheritors already got lock */
childrel = relation_open(childrelid, NoLock);
! CheckTableNotInUse(childrel, "ALTER TABLE");
! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode);
relation_close(childrel, NoLock);
}
}
--- 4362,4373 ----
continue;
/* find_all_inheritors already got lock */
childrel = relation_open(childrelid, NoLock);
! if (childrel->rd_rel->relkind != RELKIND_FOREIGN_TABLE
! || include_foreign)
! {
! CheckTableNotInUse(childrel, "ALTER TABLE");
! ATPrepCmd(wqueue, childrel, cmd, false, true, lockmode);
! }
relation_close(childrel, NoLock);
}
}
***************
*** 4611,4617 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
--- 4657,4663 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
***************
*** 4907,4912 **** ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 4953,4963 ----
/* find_inheritance_children already got lock */
childrel = heap_open(childrelid, NoLock);
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE && isOid)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot add OID column to foreign table \"%s\"",
+ RelationGetRelationName(childrel))));
CheckTableNotInUse(childrel, "ALTER TABLE");
/* Find or create work queue entry for this table */
***************
*** 5507,5513 **** ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
/*
* get the number of the attribute
--- 5558,5564 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* get the number of the attribute
***************
*** 5899,5905 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
/*
* Call AddRelationNewConstraints to do the work, making sure it works on
--- 5950,5956 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* Call AddRelationNewConstraints to do the work, making sure it works on
***************
*** 5910,5918 **** ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* omitted from the returned list, which is what we want: we do not need
* to do any validation work. That can only happen at child tables,
* though, since we disallow merging at the top level.
*/
newcons = AddRelationNewConstraints(rel, NIL,
! list_make1(copyObject(constr)),
recursing, /* allow_merge */
!recursing, /* is_local */
is_readd); /* is_internal */
--- 5961,5977 ----
* omitted from the returned list, which is what we want: we do not need
* to do any validation work. That can only happen at child tables,
* though, since we disallow merging at the top level.
+ *
+ * When propagating a NOT VALID option to children that are foreign tables,
+ * we quietly ignore the option. Note that this is safe because foreign
+ * tables don't have any children.
*/
+ constr = (Constraint *) copyObject(constr);
+ if (tab->relkind == RELKIND_FOREIGN_TABLE &&
+ constr->skip_validation && recursing)
+ constr->skip_validation = false;
newcons = AddRelationNewConstraints(rel, NIL,
! list_make1(constr),
recursing, /* allow_merge */
!recursing, /* is_local */
is_readd); /* is_internal */
***************
*** 7399,7405 **** ATExecDropConstraint(Relation rel, const char *constrName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE);
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
--- 7458,7464 ----
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
! ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
***************
*** 7734,7740 **** ATPrepAlterColumnType(List **wqueue,
* alter would put them out of step.
*/
if (recurse)
! ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
else if (!recursing &&
find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
ereport(ERROR,
--- 7793,7802 ----
* alter would put them out of step.
*/
if (recurse)
! {
! /* Recurse to child tables that are foreign, too */
! ATSimpleRecursion(wqueue, rel, cmd, recurse, true, lockmode);
! }
else if (!recursing &&
find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
ereport(ERROR,
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 2307,2312 **** EvalPlanQualFetchRowMarks(EPQState *epqstate)
--- 2307,2332 ----
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
***************
*** 2024,2029 **** _copyRangeTblEntry(const RangeTblEntry *from)
--- 2024,2030 ----
COPY_NODE_FIELD(eref);
COPY_SCALAR_FIELD(lateral);
COPY_SCALAR_FIELD(inh);
+ COPY_SCALAR_FIELD(hasForeignChildren);
COPY_SCALAR_FIELD(inFromCl);
COPY_SCALAR_FIELD(requiredPerms);
COPY_SCALAR_FIELD(checkAsUser);
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 2342,2347 **** _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
--- 2342,2348 ----
COMPARE_NODE_FIELD(eref);
COMPARE_SCALAR_FIELD(lateral);
COMPARE_SCALAR_FIELD(inh);
+ COMPARE_SCALAR_FIELD(hasForeignChildren);
COMPARE_SCALAR_FIELD(inFromCl);
COMPARE_SCALAR_FIELD(requiredPerms);
COMPARE_SCALAR_FIELD(checkAsUser);
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2452,2457 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
--- 2452,2458 ----
WRITE_BOOL_FIELD(lateral);
WRITE_BOOL_FIELD(inh);
+ WRITE_BOOL_FIELD(hasForeignChildren);
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(hasForeignChildren);
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 containing foreign tables, fetch the whole row too */
+ if (rte->hasForeignChildren)
+ {
+ /* 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 hasForeignChildren;
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;
+ hasForeignChildren = 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)
+ hasForeignChildren = 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,1433 ----
/* Otherwise, OK to add to root->append_rel_list */
root->append_rel_list = list_concat(root->append_rel_list, appinfos);
+
+ /* And set rte->hasForeignChildren */
+ rte->hasForeignChildren = hasForeignChildren;
}
/*
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 4335,4366 **** 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;
}
;
--- 4335,4366 ----
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/explain.h
--- b/src/include/commands/explain.h
***************
*** 91,94 **** extern void ExplainPropertyLong(const char *qlabel, long value,
--- 91,99 ----
extern void ExplainPropertyFloat(const char *qlabel, double value, int ndigits,
ExplainState *es);
+ extern void ExplainOpenForeignModifyGroup(ModifyTable *plan, int subplan_index,
+ ExplainState *es);
+ extern void ExplainCloseForeignModifyGroup(ModifyTable *plan, int subplan_index,
+ ExplainState *es);
+
#endif /* EXPLAIN_H */
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 812,817 **** typedef struct RangeTblEntry
--- 812,819 ----
Alias *eref; /* expanded reference names */
bool lateral; /* subquery, function, or values is LATERAL? */
bool inh; /* inheritance requested? */
+ bool hasForeignChildren; /* does inheritance set include
+ * any 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
***************
*** 850,855 **** typedef enum RowMarkType
--- 850,857 ----
* The tableoid column is only present for an inheritance hierarchy.
* When markType == ROW_MARK_COPY, there is instead a single column named
* wholerow%u whole-row value of relation
+ * The wholerow column is also present for an inheritance hierarchy that
+ * contains foreign tables.
* In all three cases, %u represents the rowmark ID number (rowmarkId).
* This number is unique within a plan tree, except that child relation
* entries copy their parent's rowmarkId. (Assigning unique numbers
*** a/src/test/regress/expected/foreign_data.out
--- b/src/test/regress/expected/foreign_data.out
***************
*** 1242,1247 **** DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
--- 1242,1652 ----
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ );
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date;
+ ALTER TABLE v1 INHERIT pt1; -- ERROR
+ ERROR: "v1" is not a table or foreign table
+ DROP VIEW v1;
+ -- add attributes recursively
+ ALTER TABLE pt1 ADD COLUMN c4 integer;
+ ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ ALTER TABLE pt1 ADD COLUMN c6 integer;
+ ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ ALTER TABLE pt1 ADD COLUMN c8 integer;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | | plain | |
+ c5 | integer | default 0 | plain | |
+ c6 | integer | | plain | |
+ c7 | integer | not null | plain | |
+ c8 | integer | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | | | plain | |
+ c5 | integer | default 0 | | plain | |
+ c6 | integer | | | plain | |
+ c7 | integer | not null | | plain | |
+ c8 | integer | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- alter attributes recursively
+ ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ ERROR: "ft2" is not a table
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | default 0 | plain | |
+ c5 | integer | | plain | |
+ c6 | integer | not null | plain | |
+ c7 | integer | | plain | |
+ c8 | text | | extended | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | default 0 | | plain | |
+ c5 | integer | | | plain | |
+ c6 | integer | not null | | plain | |
+ c7 | integer | | | plain | |
+ c8 | text | | | extended | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- drop attributes recursively
+ ALTER TABLE pt1 DROP COLUMN c4;
+ ALTER TABLE pt1 DROP COLUMN c5;
+ ALTER TABLE pt1 DROP COLUMN c6;
+ ALTER TABLE pt1 DROP COLUMN c7;
+ ALTER TABLE pt1 DROP COLUMN c8;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- cannot change storage mode for an attribute of foreign tables
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | external | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | external | |
+ c3 | date | | plain | |
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED;
+ -- add constraints recursively
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ -- connoinherit should be true for NO INHERIT constraint
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ relname | conname | contype | conislocal | coninhcount | connoinherit
+ ---------+---------+---------+------------+-------------+--------------
+ pt1 | pt1chk1 | c | t | 0 | t
+ pt1 | pt1chk2 | c | t | 0 | f
+ (2 rows)
+
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ ERROR: child table is missing constraint "pt1chk2"
+ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- drop constraints recursively
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID;
+ NOTICE: merging constraint "pt1chk2" with inherited definition
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text) NOT VALID
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- cannot add an OID system column to foreign tables
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+ ERROR: cannot add OID column to foreign table "ft2"
+ DROP FOREIGN TABLE ft2;
+ ALTER TABLE pt1 SET WITH OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ERROR: cannot inherit from relation with OIDs
+ ALTER TABLE pt1 SET WITHOUT OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- changes name of an attribute recursively
+ ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+ -- changes name of a constraint recursively
+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check;
+ \d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+ --------+---------+-----------+----------+--------------+-------------
+ f1 | integer | not null | plain | 10000 |
+ f2 | text | | extended | |
+ f3 | date | | plain | |
+ Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+ Child tables: ft2
+
+ \d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+ --------+---------+-----------+-------------+----------+--------------+-------------
+ f1 | integer | not null | | plain | |
+ f2 | text | | | extended | |
+ f3 | date | | | plain | |
+ Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+ Server: s0
+ FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+ Inherits: pt1
+
+ -- TRUNCATE should work by ignoring foreign tables
+ TRUNCATE pt1;
+ TRUNCATE ft2; -- ERROR
+ ERROR: "ft2" is not a table
+ DROP TABLE pt1 CASCADE;
+ NOTICE: drop cascades to foreign table ft2
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
ERROR: foreign-data wrapper "foo" has no handler
*** a/src/test/regress/sql/foreign_data.sql
--- b/src/test/regress/sql/foreign_data.sql
***************
*** 542,547 **** DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
--- 542,676 ----
DROP FUNCTION dummy_trigger();
+ -- Table inheritance
+ CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ );
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ \d+ pt1
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ ft2
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ \d+ pt1
+ \d+ ft2
+ CREATE VIEW v1 (c1, c2, c3) AS SELECT 1, 'v1'::text, '1994-01-01'::date;
+ ALTER TABLE v1 INHERIT pt1; -- ERROR
+ DROP VIEW v1;
+
+ -- add attributes recursively
+ ALTER TABLE pt1 ADD COLUMN c4 integer;
+ ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ ALTER TABLE pt1 ADD COLUMN c6 integer;
+ ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ ALTER TABLE pt1 ADD COLUMN c8 integer;
+ \d+ pt1
+ \d+ ft2
+
+ -- alter attributes recursively
+ ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ \d+ pt1
+ \d+ ft2
+
+ -- drop attributes recursively
+ ALTER TABLE pt1 DROP COLUMN c4;
+ ALTER TABLE pt1 DROP COLUMN c5;
+ ALTER TABLE pt1 DROP COLUMN c6;
+ ALTER TABLE pt1 DROP COLUMN c7;
+ ALTER TABLE pt1 DROP COLUMN c8;
+ \d+ pt1
+ \d+ ft2
+
+ -- cannot change storage mode for an attribute of foreign tables
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTERNAL;
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ \d+ pt1
+ \d+ ft2
+ ALTER TABLE pt1 ALTER COLUMN c2 SET STORAGE EXTENDED;
+
+ -- add constraints recursively
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ -- connoinherit should be true for NO INHERIT constraint
+ SELECT pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit FROM pg_class AS pc INNER JOIN pg_constraint AS pgc ON (pgc.conrelid = pc.oid) WHERE pc.relname = 'pt1' order by 1,2;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ \d+ ft2
+ DROP FOREIGN TABLE ft2;
+ CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+ ) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+ -- child must have parent's INHERIT constraints
+ ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ ALTER FOREIGN TABLE ft2 INHERIT pt1;
+ -- child does not inherit NO INHERIT constraints
+ \d+ pt1
+ \d+ ft2
+
+ -- drop constraints recursively
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+
+ -- NOT VALID should be ignored for foreign tables
+ INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '') NOT VALID;
+ \d+ pt1
+ \d+ ft2
+ -- VALIDATE CONSTRAINT should work by ignoring foreign tables
+ ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk2;
+ \d+ pt1
+ \d+ ft2
+
+ -- cannot add an OID system column to foreign tables
+ ALTER TABLE pt1 SET WITH OIDS; -- ERROR
+ DROP FOREIGN TABLE ft2;
+ ALTER TABLE pt1 SET WITH OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value'); -- ERROR
+ ALTER TABLE pt1 SET WITHOUT OIDS;
+ CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+
+ -- changes name of an attribute recursively
+ ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+ -- changes name of a constraint recursively
+ ALTER TABLE pt1 RENAME CONSTRAINT pt1chk2 TO f2_check;
+ \d+ pt1
+ \d+ ft2
+
+ -- TRUNCATE should work by ignoring foreign tables
+ TRUNCATE pt1;
+ TRUNCATE ft2; -- ERROR
+
+ DROP TABLE pt1 CASCADE;
+
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
