Ashutosh Bapat pointed out [0] that regression tests are missing for the foreign partition DDL commands. Attached patch takes care of that.
Thanks, Amit [0] https://www.postgresql.org/message-id/CAFjFpRcrdzBRj0cZ%2BJAQmfSa2Tv8wSEcWAeYtDpV-YZnNna2sA%40mail.gmail.com
>From 236c357b94af848663ed3d0ace10dd22167b7d08 Mon Sep 17 00:00:00 2001 From: amit <amitlangot...@gmail.com> Date: Tue, 21 Feb 2017 15:06:04 +0900 Subject: [PATCH] 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 | 195 +++++++++++++++++++++++++++++ src/test/regress/sql/foreign_data.sql | 71 +++++++++++ 2 files changed, 266 insertions(+) diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 3a9fb8f558..a0f969f3e5 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -1751,6 +1751,201 @@ 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 FOREIGN TABLE pt2_1; +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..c13d5ffbe9 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -684,6 +684,77 @@ 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 FOREIGN TABLE pt2_1; +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
