aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/foreign_data.out195
-rw-r--r--src/test/regress/sql/foreign_data.sql71
2 files changed, 266 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 3a9fb8f558f..a0f969f3e52 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 38e1d41a5f7..c13d5ffbe92 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
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 07:02:16 +0000