Hi Ashutosh,
Thanks for taking a look at the patch.
On 2017/02/20 21:49, Ashutosh Bapat wrote:
> Thanks for working on all the follow on work for partitioning feature.
>
> May be you should add all those patches in the next commitfest, so
> that we don't forget those.
I think adding these as one of the PostgreSQL 10 Open Items [0] might be
better. I've done that.
> On Mon, Feb 20, 2017 at 7:46 AM, Amit Langote wrote:
>> So I count more than a few votes saying that we should be able to DROP
>> partitioned tables without specifying CASCADE.
>>
>> I tried to implement that using the attached patch by having
>> StoreCatalogInheritance1() create DEPENDENCY_AUTO dependency between
>> parent and child if the child is a partition, instead of DEPENDENCY_NORMAL
>> that would otherwise be created. Now it seems that that is one way of
>> making sure that partitions are dropped when the root partitioned table is
>> dropped, not sure if the best; why create the pg_depend entries at all one
>> might ask. I chose it for now because that's the one with fewest lines of
>> change. Adjusted regression tests as well, since we recently tweaked
>> tests [1] to work around the irregularities of test output when using
>> CASCADE.
>
> The patch applies cleanly and regression does not show any failures.
>
> Here are some comments
>
> For the sake of readability you may want reverse the if and else order.
> - recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL);
> + if (!child_is_partition)
> + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL);
> + else
> + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_AUTO);
> like
> + if (child_is_partition)
> + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_AUTO);
> + else
> + recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL);
Sure, done.
> It's weird that somebody can perform DROP TABLE on the partition without
> referring to its parent. That may be a useful feature as it allows one to
> detach the partition as well as remove the table in one command. But it looks
> wierd for someone familiar with partitioning features of other DBMSes. But
> then
> our partition creation command is CREATE TABLE .... So may be this is expected
> difference.
There is a line on the CREATE TABLE page in the description of PARTITION
OF clause:
"Note that dropping a partition with DROP TABLE requires taking an ACCESS
EXCLUSIVE lock on the parent table."
In earlier proposals I had included the ALTER TABLE parent ADD/DROP
PARTITION commands, but CRAETE TABLE PARTITION OF / DROP TABLE prevailed.
> --- cleanup: avoid using CASCADE
> -DROP TABLE list_parted, part_1;
> -DROP TABLE list_parted2, part_2, part_5, part_5_a;
> -DROP TABLE range_parted, part1, part2;
> +-- cleanup
> +DROP TABLE list_parted, list_parted2, range_parted;
> Testcases usually drop one table at a time, I guess, to reduce the differences
> when we add or remove tables from testcases. All such blocks should probably
> follow same policy.
Hmm, I see this in src/test/regress/sql/inherit.sql:141
DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
> drop table list_parted cascade;
> -NOTICE: drop cascades to 3 other objects
> -DETAIL: drop cascades to table part_ab_cd
> probably we should remove cascade from there, unless you are testing CASCADE
> functionality. Similarly for other blocks like
> drop table range_parted cascade;
>
> BTW, I noticed that although we are allowing foreign tables to be
> partitions, there are no tests in foreign_data.sql for testing it. If
> there would have been we would tests DROP TABLE on a partitioned table
> with foreign partitions as well. That file has testcases for testing
> foreign table inheritance, and should have tests for foreign table
> partitions.
That makes sense. Patch 0002 is for that (I'm afraid this should be
posted separately though). I didn't add/repeat all the tests that were
added by the foreign table inheritance patch again for foreign partitions
(common inheritance rules apply to both cases), only added those for the
new partitioning commands and certain new rules.
Thanks,
Amit
[0] https://wiki.postgresql.org/wiki/PostgreSQL_10_Open_Items
>From c7cff17ab7861dbd0a4fb329115b3f99fd800325 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Thu, 16 Feb 2017 15:56:44 +0900
Subject: [PATCH 1/2] Allow dropping partitioned table without CASCADE
Currently, a normal dependency is created between a inheritance
parent and child when creating the child. That means one must
specify CASCADE to drop the parent table if a child table exists.
When creating partitions as inheritance children, create auto
dependency instead, so that partitions are dropped automatically
when the parent is dropped i.e., without specifying CASCADE.
---
src/backend/commands/tablecmds.c | 26 ++++++++++++++++++--------
src/test/regress/expected/alter_table.out | 10 ++++------
src/test/regress/expected/create_table.out | 9 ++-------
src/test/regress/expected/inherit.out | 18 ------------------
src/test/regress/expected/insert.out | 7 ++-----
src/test/regress/expected/update.out | 5 -----
src/test/regress/sql/alter_table.sql | 10 ++++------
src/test/regress/sql/create_table.sql | 9 ++-------
src/test/regress/sql/insert.sql | 7 ++-----
9 files changed, 34 insertions(+), 67 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3cea220421..31b50ad77f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -289,9 +289,11 @@ static List *MergeAttributes(List *schema, List *supers, char relpersistence,
static bool MergeCheckConstraint(List *constraints, char *name, Node *expr);
static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel);
static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel);
-static void StoreCatalogInheritance(Oid relationId, List *supers);
+static void StoreCatalogInheritance(Oid relationId, List *supers,
+ bool child_is_partition);
static void StoreCatalogInheritance1(Oid relationId, Oid parentOid,
- int16 seqNumber, Relation inhRelation);
+ int16 seqNumber, Relation inhRelation,
+ bool child_is_partition);
static int findAttrByName(const char *attributeName, List *schema);
static void AlterIndexNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid, ObjectAddresses *objsMoved);
@@ -725,7 +727,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
typaddress);
/* Store inheritance information for new rel. */
- StoreCatalogInheritance(relationId, inheritOids);
+ StoreCatalogInheritance(relationId, inheritOids, stmt->partbound != NULL);
/*
* We must bump the command counter to make the newly-created relation
@@ -2240,7 +2242,8 @@ MergeCheckConstraint(List *constraints, char *name, Node *expr)
* supers is a list of the OIDs of the new relation's direct ancestors.
*/
static void
-StoreCatalogInheritance(Oid relationId, List *supers)
+StoreCatalogInheritance(Oid relationId, List *supers,
+ bool child_is_partition)
{
Relation relation;
int16 seqNumber;
@@ -2270,7 +2273,8 @@ StoreCatalogInheritance(Oid relationId, List *supers)
{
Oid parentOid = lfirst_oid(entry);
- StoreCatalogInheritance1(relationId, parentOid, seqNumber, relation);
+ StoreCatalogInheritance1(relationId, parentOid, seqNumber, relation,
+ child_is_partition);
seqNumber++;
}
@@ -2283,7 +2287,8 @@ StoreCatalogInheritance(Oid relationId, List *supers)
*/
static void
StoreCatalogInheritance1(Oid relationId, Oid parentOid,
- int16 seqNumber, Relation inhRelation)
+ int16 seqNumber, Relation inhRelation,
+ bool child_is_partition)
{
TupleDesc desc = RelationGetDescr(inhRelation);
Datum values[Natts_pg_inherits];
@@ -2317,7 +2322,10 @@ StoreCatalogInheritance1(Oid relationId, Oid parentOid,
childobject.objectId = relationId;
childobject.objectSubId = 0;
- recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL);
+ if (!child_is_partition)
+ recordDependencyOn(&childobject, &parentobject, DEPENDENCY_NORMAL);
+ else
+ recordDependencyOn(&childobject, &parentobject, DEPENDENCY_AUTO);
/*
* Post creation hook of this inheritance. Since object_access_hook
@@ -10744,7 +10752,9 @@ CreateInheritance(Relation child_rel, Relation parent_rel)
StoreCatalogInheritance1(RelationGetRelid(child_rel),
RelationGetRelid(parent_rel),
inhseqno + 1,
- catalogRelation);
+ catalogRelation,
+ parent_rel->rd_rel->relkind ==
+ RELKIND_PARTITIONED_TABLE);
/* Now we're done with pg_inherits */
heap_close(catalogRelation, RowExclusiveLock);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index e84af67fb2..ca66158ee3 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3339,10 +3339,8 @@ ALTER TABLE list_parted2 DROP COLUMN b;
ERROR: cannot drop column named in partition key
ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
ERROR: cannot alter type of column named in partition key
--- cleanup: avoid using CASCADE
-DROP TABLE list_parted, part_1;
-DROP TABLE list_parted2, part_2, part_5, part_5_a;
-DROP TABLE range_parted, part1, part2;
+-- cleanup
+DROP TABLE list_parted, list_parted2, range_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
create table p1 (b int, a int not null) partition by range (b);
@@ -3371,5 +3369,5 @@ insert into p1 (a, b) values (2, 3);
-- check that partition validation scan correctly detects violating rows
alter table p attach partition p1 for values from (1, 2) to (1, 10);
ERROR: partition constraint is violated by some row
--- cleanup: avoid using CASCADE
-drop table p, p1, p11;
+-- cleanup
+drop table p;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 20eb3d35f9..c07a474b3d 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -667,10 +667,5 @@ Check constraints:
"check_a" CHECK (length(a) > 0)
Number of partitions: 3 (Use \d+ to list them.)
--- cleanup: avoid using CASCADE
-DROP TABLE parted, part_a, part_b, part_c, part_c_1_10;
-DROP TABLE list_parted, part_1, part_2, part_null;
-DROP TABLE range_parted;
-DROP TABLE list_parted2, part_ab, part_null_z;
-DROP TABLE range_parted2, part0, part1, part2, part3;
-DROP TABLE range_parted3, part00, part10, part11, part12;
+-- cleanup
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index a8c8b28a75..623aa1db93 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1844,22 +1844,4 @@ explain (costs off) select * from range_list_parted where a >= 30;
(11 rows)
drop table list_parted cascade;
-NOTICE: drop cascades to 3 other objects
-DETAIL: drop cascades to table part_ab_cd
-drop cascades to table part_ef_gh
-drop cascades to table part_null_xy
drop table range_list_parted cascade;
-NOTICE: drop cascades to 13 other objects
-DETAIL: drop cascades to table part_1_10
-drop cascades to table part_1_10_ab
-drop cascades to table part_1_10_cd
-drop cascades to table part_10_20
-drop cascades to table part_10_20_ab
-drop cascades to table part_10_20_cd
-drop cascades to table part_21_30
-drop cascades to table part_21_30_ab
-drop cascades to table part_21_30_cd
-drop cascades to table part_40_inf
-drop cascades to table part_40_inf_ab
-drop cascades to table part_40_inf_cd
-drop cascades to table part_40_inf_null
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 81af3ef497..31cfa4e76e 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -314,10 +314,7 @@ select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_p
(9 rows)
-- cleanup
-drop table part1, part2, part3, part4, range_parted;
-drop table part_ee_ff3_1, part_ee_ff3_2, part_ee_ff1, part_ee_ff2, part_ee_ff3;
-drop table part_ee_ff, part_gg2_2, part_gg2_1, part_gg2, part_gg1, part_gg;
-drop table part_aa_bb, part_cc_dd, part_null, list_parted;
+drop table range_parted, list_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
create table p1 (b int not null, a int not null) partition by range ((b+0));
@@ -387,4 +384,4 @@ with ins (a, b, c) as
(5 rows)
-- cleanup
-drop table p, p1, p11, p12, p2, p3, p4;
+drop table p;
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index a1e9255450..af0d5bfffe 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -220,8 +220,3 @@ DETAIL: Failing row contains (b, 9).
update range_parted set b = b + 1 where b = 10;
-- cleanup
drop table range_parted cascade;
-NOTICE: drop cascades to 4 other objects
-DETAIL: drop cascades to table part_a_1_a_10
-drop cascades to table part_a_10_a_20
-drop cascades to table part_b_1_b_10
-drop cascades to table part_b_10_b_20
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index a403fd8cb4..fbcc739f41 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2199,10 +2199,8 @@ ALTER TABLE part_2 INHERIT inh_test;
ALTER TABLE list_parted2 DROP COLUMN b;
ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
--- cleanup: avoid using CASCADE
-DROP TABLE list_parted, part_1;
-DROP TABLE list_parted2, part_2, part_5, part_5_a;
-DROP TABLE range_parted, part1, part2;
+-- cleanup
+DROP TABLE list_parted, list_parted2, range_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
@@ -2227,5 +2225,5 @@ insert into p1 (a, b) values (2, 3);
-- check that partition validation scan correctly detects violating rows
alter table p attach partition p1 for values from (1, 2) to (1, 10);
--- cleanup: avoid using CASCADE
-drop table p, p1, p11;
+-- cleanup
+drop table p;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index f41dd71475..1f0fa8e16d 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -595,10 +595,5 @@ CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
-- returned.
\d parted
--- cleanup: avoid using CASCADE
-DROP TABLE parted, part_a, part_b, part_c, part_c_1_10;
-DROP TABLE list_parted, part_1, part_2, part_null;
-DROP TABLE range_parted;
-DROP TABLE list_parted2, part_ab, part_null_z;
-DROP TABLE range_parted2, part0, part1, part2, part3;
-DROP TABLE range_parted3, part00, part10, part11, part12;
+-- cleanup
+DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 454e1ce2e7..dfdc24eba8 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -186,10 +186,7 @@ insert into list_parted (b) values (1);
select tableoid::regclass::text, a, min(b) as min_b, max(b) as max_b from list_parted group by 1, 2 order by 1;
-- cleanup
-drop table part1, part2, part3, part4, range_parted;
-drop table part_ee_ff3_1, part_ee_ff3_2, part_ee_ff1, part_ee_ff2, part_ee_ff3;
-drop table part_ee_ff, part_gg2_2, part_gg2_1, part_gg2, part_gg1, part_gg;
-drop table part_aa_bb, part_cc_dd, part_null, list_parted;
+drop table range_parted, list_parted;
-- more tests for certain multi-level partitioning scenarios
create table p (a int, b int) partition by range (a, b);
@@ -241,4 +238,4 @@ with ins (a, b, c) as
select a, b, min(c), max(c) from ins group by a, b order by 1;
-- cleanup
-drop table p, p1, p11, p12, p2, p3, p4;
+drop table p;
--
2.11.0
>From 688ef3c1d7584adadef25e78dd679972b817c8d6 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 21 Feb 2017 15:06:04 +0900
Subject: [PATCH 2/2] Add regression tests foreign partition DDL
Commands like CREATE FOREIGN TABLE .. PARTITION OF, ATTACH PARTITION,
DETACH PARTITION foreign_table didn't get any tests so far. Per
suggestion from Ashutosh Bapat.
---
src/test/regress/expected/foreign_data.out | 194 +++++++++++++++++++++++++++++
src/test/regress/sql/foreign_data.sql | 70 +++++++++++
2 files changed, 264 insertions(+)
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 3a9fb8f558..541f2eefe1 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1751,6 +1751,200 @@ DETAIL: user mapping for regress_test_role on server s5 depends on server s5
HINT: Use DROP ... CASCADE to drop the dependent objects too.
DROP OWNED BY regress_test_role2 CASCADE;
NOTICE: drop cascades to user mapping for regress_test_role on server s5
+-- Foreign partition DDL stuff
+CREATE TABLE pt2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) PARTITION BY LIST (c1);
+CREATE FOREIGN TABLE pt2_1 PARTITION OF pt2 FOR VALUES IN (1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+Partitions: pt2_1 FOR VALUES IN (1)
+
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+Partition of: pt2 FOR VALUES IN (1)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+-- partition cannot have additional columns
+DROP FOREIGN TABLE pt2_1;
+CREATE FOREIGN TABLE pt2_1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date,
+ c4 char
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+--------------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+ c4 | character(1) | | | | | extended | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+ERROR: table "pt2_1" contains column "c4" not found in parent "pt2"
+DETAIL: New partition should contain only the columns present in parent.
+DROP FOREIGN TABLE pt2_1;
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+
+CREATE FOREIGN TABLE pt2_1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | 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')
+
+-- no attach partition validation occurs for foreign tables
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+Partitions: pt2_1 FOR VALUES IN (1)
+
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | | | | plain | |
+Partition of: pt2 FOR VALUES IN (1)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+-- cannot add column to a partition
+ALTER TABLE pt2_1 ADD c4 char;
+ERROR: cannot add column to a partition
+-- ok to have a partition's own constraints though
+ALTER TABLE pt2_1 ALTER c3 SET NOT NULL;
+ALTER TABLE pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+Partitions: pt2_1 FOR VALUES IN (1)
+
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | not null | | | plain | |
+Partition of: pt2 FOR VALUES IN (1)
+Check constraints:
+ "p21chk" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+-- cannot drop inherited NOT NULL constraint from a partition
+ALTER TABLE pt2_1 ALTER c1 DROP NOT NULL;
+ERROR: column "c1" is marked NOT NULL in parent table
+-- partition must have parent's constraints
+ALTER TABLE pt2 DETACH PARTITION pt2_1;
+ALTER TABLE pt2 ALTER c2 SET NOT NULL;
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | not null | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | | | | extended | |
+ c3 | date | | not null | | | plain | |
+Check constraints:
+ "p21chk" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+ERROR: column "c2" in child table must be marked NOT NULL
+ALTER FOREIGN TABLE pt2_1 ALTER c2 SET NOT NULL;
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+ALTER TABLE pt2 DETACH PARTITION pt2_1;
+ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
+\d+ pt2
+ Table "public.pt2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c1 | integer | | not null | | plain | |
+ c2 | text | | not null | | extended | |
+ c3 | date | | | | plain | |
+Partition key: LIST (c1)
+Check constraints:
+ "pt2chk1" CHECK (c1 > 0)
+
+\d+ pt2_1
+ Foreign table "public.pt2_1"
+ Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
+ c1 | integer | | not null | | | plain | |
+ c2 | text | | not null | | | extended | |
+ c3 | date | | not null | | | plain | |
+Check constraints:
+ "p21chk" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+ERROR: child table is missing constraint "pt2chk1"
+ALTER FOREIGN TABLE pt2_1 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+-- TRUNCATE doesn't work on foreign tables, either directly or recursively
+TRUNCATE pt2_1; -- ERROR
+ERROR: "pt2_1" is not a table
+TRUNCATE pt2; -- ERROR
+ERROR: "pt2_1" is not a table
+DROP TABLE pt2;
-- Cleanup
DROP SCHEMA foreign_schema CASCADE;
DROP ROLE regress_test_role; -- ERROR
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index 38e1d41a5f..57e38515db 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -684,6 +684,76 @@ REASSIGN OWNED BY regress_test_role TO regress_test_role2;
DROP OWNED BY regress_test_role2;
DROP OWNED BY regress_test_role2 CASCADE;
+-- Foreign partition DDL stuff
+CREATE TABLE pt2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) PARTITION BY LIST (c1);
+CREATE FOREIGN TABLE pt2_1 PARTITION OF pt2 FOR VALUES IN (1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2
+\d+ pt2_1
+
+-- partition cannot have additional columns
+DROP FOREIGN TABLE pt2_1;
+CREATE FOREIGN TABLE pt2_1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date,
+ c4 char
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2_1
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+
+DROP FOREIGN TABLE pt2_1;
+\d+ pt2
+CREATE FOREIGN TABLE pt2_1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt2_1
+-- no attach partition validation occurs for foreign tables
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+\d+ pt2
+\d+ pt2_1
+
+-- cannot add column to a partition
+ALTER TABLE pt2_1 ADD c4 char;
+
+-- ok to have a partition's own constraints though
+ALTER TABLE pt2_1 ALTER c3 SET NOT NULL;
+ALTER TABLE pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
+\d+ pt2
+\d+ pt2_1
+
+-- cannot drop inherited NOT NULL constraint from a partition
+ALTER TABLE pt2_1 ALTER c1 DROP NOT NULL;
+
+-- partition must have parent's constraints
+ALTER TABLE pt2 DETACH PARTITION pt2_1;
+ALTER TABLE pt2 ALTER c2 SET NOT NULL;
+\d+ pt2
+\d+ pt2_1
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+ALTER FOREIGN TABLE pt2_1 ALTER c2 SET NOT NULL;
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+
+ALTER TABLE pt2 DETACH PARTITION pt2_1;
+ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
+\d+ pt2
+\d+ pt2_1
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
+ALTER FOREIGN TABLE pt2_1 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
+ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
+
+-- TRUNCATE doesn't work on foreign tables, either directly or recursively
+TRUNCATE pt2_1; -- ERROR
+TRUNCATE pt2; -- ERROR
+
+DROP TABLE pt2;
+
-- Cleanup
DROP SCHEMA foreign_schema CASCADE;
DROP ROLE regress_test_role; -- ERROR
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
