From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: Noah Misch <noah@leadboat.com>
Cc: Shigeru Hanada <shigeru.hanada@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: inherit support for foreign tables
Date: Wed, 10 Dec 2014 12:18:53 +0900
Message-ID: <5487BB9D.7070605@lab.ntt.co.jp> (raw)
In-Reply-To: <CAFjFpRd6PR3Seg8D3t-XcXGxXyb+p2WDsk_wBGge7b=PyzP_Qw@mail.gmail.com>
References: <543D0261.6020803@lab.ntt.co.jp>
<54461BEB.9000707@lab.ntt.co.jp>
<5449EE98.1040704@lab.ntt.co.jp>
<20141107.145713.169337282.horiguchi.kyotaro@lab.ntt.co.jp>
<545CB4A2.1030303@lab.ntt.co.jp>
<CAFjFpRcVLtU6jE8h_N1Rc+Y-pDFbmR7YvBvxOXg6aTFrbZ1Z8Q@mail.gmail.com>
<54645499.7050709@lab.ntt.co.jp>
<CAFjFpRcBFVO0YP0nmfygpJOFfWKMkAN1Z8JmrkwyEWXDyMt5Hw@mail.gmail.com>
<CAFjFpRcd=F8Jqx+9yioHKVUA71LXBxO41ia61acjzyzQ4MZqVQ@mail.gmail.com>
<5476FB4B.9060705@lab.ntt.co.jp>
<CAFjFpRd6PR3Seg8D3t-XcXGxXyb+p2WDsk_wBGge7b=PyzP_Qw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.etsuro@lab.ntt.co.jp <mailto:fujita.etsuro@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:
[text/x-patch] fdw-chk-5.patch (34.4K, ../5487BB9D.7070605@lab.ntt.co.jp/2-fdw-chk-5.patch)
download | inline diff:
*** 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 '@');
[text/x-patch] fdw-inh-5.patch (97.2K, ../5487BB9D.7070605@lab.ntt.co.jp/3-fdw-inh-5.patch)
download | inline diff:
*** 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, ashutosh.bapat@enterprisedb.com, horiguchi.kyotaro@lab.ntt.co.jp, hlinnakangas@vmware.com, noah@leadboat.com, shigeru.hanada@gmail.com
Subject: Re: inherit support for foreign tables
In-Reply-To: <5487BB9D.7070605@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox