8000 Add tests for foreign partitions. · postgrespro/postgres_cluster@0d130c7 · GitHub
[go: up one dir, main page]
Skip to content
8000

Commit 0d130c7

Browse files
robertmhaasrobertmhaas
committed
Add tests for foreign partitions.
Amit Langote, reviewed by Ashutosh Bapat Discussion: http://postgr.es/m/475dd52c-be4a-9b32-6d54-3044a00c93d9@lab.ntt.co.jp
1 parent fcec6ca commit 0d130c7

File tree

2 files changed

+266
-0
lines changed

2 files changed

+266
-0
lines changed

‎src/test/regress/expected/foreign_data.out< 10000 !-- -->‎

Lines changed: 195 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1751,6 +1751,201 @@ DETAIL: user mapping for regress_test_role on server s5 depends on server s5
17511751
HINT: Use DROP ... CASCADE to drop the dependent objects too.
17521752
DROP OWNED BY regress_test_role2 CASCADE;
17531753
NOTICE: drop cascades to user mapping for regress_test_role on server s5
1754+
-- Foreign partition DDL stuff
1755+
CREATE TABLE pt2 (
1756+
c1 integer NOT NULL,
1757+
c2 text,
1758+
c3 date
1759+
) PARTITION BY LIST (c1);
1760+
CREATE FOREIGN TABLE pt2_1 PARTITION OF pt2 FOR VALUES IN (1)
1761+
SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1762+
\d+ pt2
1763+
Table "public.pt2"
1764+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1765+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1766+
c1 | integer | | not null | | plain | |
1767+
c2 | text | | | | extended | |
1768+
c3 | date | | | | plain | |
1769+
Partition key: LIST (c1)
1770+
Partitions: pt2_1 FOR VALUES IN (1)
1771+
1772+
\d+ pt2_1
1773+
Foreign table "public.pt2_1"
1774+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1775+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1776+
c1 | integer | | not null | | | plain | |
1777+
c2 | text | | | | | extended | |
1778+
c3 | date | | | | | plain | |
1779+
Partition of: pt2 FOR VALUES IN (1)
1780+
Server: s0
1781+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1782+
1783+
-- partition cannot have additional columns
1784+
DROP FOREIGN TABLE pt2_1;
1785+
CREATE FOREIGN TABLE pt2_1 (
1786+
c1 integer NOT NULL,
1787+
c2 text,
1788+
c3 date,
1789+
c4 char
1790+
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1791+
\d+ pt2_1
1792+
Foreign table "public.pt2_1"
1793+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1794+
--------+--------------+-----------+----------+---------+-------------+----------+--------------+-------------
1795+
c1 | integer | | not null | | | plain | |
1796+
c2 | text | | | | | extended | |
1797+
c3 | date | | | | | plain | |
1798+
c4 | character(1) | | | | | extended | |
1799+
Server: s0
1800+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1801+
1802+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
1803+
ERROR: table "pt2_1" contains column "c4" not found in parent "pt2"
1804+
DETAIL: New partition should contain only the columns present in parent.
1805+
DROP FOREIGN TABLE pt2_1;
1806+
\d+ pt2
1807+
Table "public.pt2"
1808+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1809+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1810+
c1 | integer | | not null | | plain | |
1811+
c2 | text | | | | extended | |
1812+
c3 | date | | | | plain | |
1813+
Partition key: LIST (c1)
1814+
1815+
CREATE FOREIGN TABLE pt2_1 (
1816+
c1 integer NOT NULL,
1817+
c2 text,
1818+
c3 date
1819+
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
1820+
\d+ pt2_1
1821+
Foreign table "public.pt2_1"
1822+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1823+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1824+
c1 | integer | | not null | | | plain | |
1825+
c2 | text | | | | | extended | |
1826+
c3 | date | | | | | plain | |
1827+
Server: s0
1828+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1829+
1830+
-- no attach partition validation occurs for foreign tables
1831+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
1832+
\d+ pt2
1833+
Table "public.pt2"
1834+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1835+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1836+
c1 | integer | | not null | | plain | |
1837+
c2 | text | | | | extended | |
1838+
c3 | date | | | | plain | |
1839+
Partition key: LIST (c1)
1840+
Partitions: pt2_1 FOR VALUES IN (1)
1841+
1842+
\d+ pt2_1
1843+
Foreign table "public.pt2_1"
1844+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1845+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1846+
c1 | integer | | not null | | | plain | |
1847+
c2 | text | | | | | extended | |
1848+
c3 | date | | | | | plain | |
1849+
Partition of: pt2 FOR VALUES IN (1)
1850+
Server: s0
1851+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1852+
1853+
-- cannot add column to a partition
1854+
ALTER TABLE pt2_1 ADD c4 char;
1855+
ERROR: cannot add column to a partition
1856+
-- ok to have a partition's own constraints though
1857+
ALTER TABLE pt2_1 ALTER c3 SET NOT NULL;
1858+
ALTER TABLE pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
1859+
\d+ pt2
1860+
Table "public.pt2"
1861+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1862+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1863+
c1 | integer | | not null | | plain | |
1864+
c2 | text | | | | extended | |
1865+
c3 | date | | | | plain | |
1866+
Partition key: LIST (c1)
1867+
Partitions: pt2_1 FOR VALUES IN (1)
1868+
1869+
\d+ pt2_1
1870+
Foreign table "public.pt2_1"
1871+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1872+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1873+
c1 | integer | | not null | | | plain | |
1874+
c2 | text | | | | | extended | |
1875+
c3 | date | | not null | | | plain | |
1876+
Partition of: pt2 FOR VALUES IN (1)
1877+
Check constraints:
1878+
"p21chk" CHECK (c2 <> ''::text)
1879+
Server: s0
1880+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1881+
1882+
-- cannot drop inherited NOT NULL constraint from a partition
1883+
ALTER TABLE pt2_1 ALTER c1 DROP NOT NULL;
1884+
ERROR: column "c1" is marked NOT NULL in parent table
1885+
-- partition must have parent's constraints
1886+
ALTER TABLE pt2 DETACH PARTITION pt2_1;
1887+
ALTER TABLE pt2 ALTER c2 SET NOT NULL;
1888+
\d+ pt2
1889+
Table "public.pt2"
1890+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1891+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1892+
c1 | integer | | not null | | plain | |
1893+
c2 | text | | not null | | extended | |
1894+
c3 | date | | | | plain | |
1895+
Partition key: LIST (c1)
1896+
1897+
\d+ pt2_1
1898+
Foreign table "public.pt2_1"
1899+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1900+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1901+
c1 | integer | | not null | | | plain | |
1902+
c2 | text | | | | | extended | |
1903+
c3 | date | | not null | | | plain | |
1904+
Check constraints:
1905+
"p21chk" CHECK (c2 <> ''::text)
1906+
Server: s0
1907+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1908+
1909+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
1910+
ERROR: column "c2" in child table must be marked NOT NULL
1911+
ALTER FOREIGN TABLE pt2_1 ALTER c2 SET NOT NULL;
1912+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
1913+
ALTER TABLE pt2 DETACH PARTITION pt2_1;
1914+
ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
1915+
\d+ pt2
1916+
Table "public.pt2"
1917+
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1918+
--------+---------+-----------+----------+---------+----------+--------------+-------------
1919+
c1 | integer | | not null | | plain | |
1920+
c2 | text | | not null | | extended | |
1921+
c3 | date | | | | plain | |
1922+
Partition key: LIST (c1)
1923+
Check constraints:
1924+
"pt2chk1" CHECK (c1 > 0)
1925+
1926+
\d+ pt2_1
1927+
Foreign table "public.pt2_1"
1928+
Column | Type | Collation | Nullable | Default | FDW Options | Storage | Stats target | Description
1929+
--------+---------+-----------+----------+---------+-------------+----------+--------------+-------------
1930+
c1 | integer | | not null | | | plain | |
1931+
c2 | text | | not null | | | extended | |
1932+
c3 | date | | not null | | | plain | |
1933+
Check constraints:
1934+
"p21chk" CHECK (c2 <> ''::text)
1935+
Server: s0
1936+
FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
1937+
1938+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
1939+
ERROR: child table is missing constraint "pt2chk1"
1940+
ALTER FOREIGN TABLE pt2_1 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
1941+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
1942+
-- TRUNCATE doesn't work on foreign tables, either directly or recursively
1943+
TRUNCATE pt2_1; -- ERROR
1944+
ERROR: "pt2_1" is not a table
1945+
TRUNCATE pt2; -- ERROR
1946+
ERROR: "pt2_1" is not a table
1947+
DROP FOREIGN TABLE pt2_1;
1948+
DROP TABLE pt2;
17541949
-- Cleanup
17551950
DROP SCHEMA foreign_schema CASCADE;
17561951
DROP ROLE regress_test_role; -- ERROR

‎src/test/regress/sql/foreign_data.sql‎

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -684,6 +684,77 @@ REASSIGN OWNED BY regress_test_role TO regress_test_role2;
684684
DROP OWNED BY regress_test_role2;
685685
DROP OWNED BY regress_test_role2 CASCADE;
686686

687+
-- Foreign partition DDL stuff
688+
CREATE TABLE pt2 (
689+
c1 integer NOT NULL,
690+
c2 text,
691+
c3 date
692+
) PARTITION BY LIST (c1);
693+
CREATE FOREIGN TABLE pt2_1 PARTITION OF pt2 FOR VALUES IN (1)
694+
SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
695+
\d+ pt2
696+
\d+ pt2_1
697+
698+
-- partition cannot have additional columns
699+
DROP FOREIGN TABLE pt2_1;
700+
CREATE FOREIGN TABLE pt2_1 (
701+
c1 integer NOT NULL,
702+
c2 text,
703+
c3 date,
704+
c4 char
705+
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
706+
\d+ pt2_1
707+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
708+
709+
DROP FOREIGN TABLE pt2_1;
710+
\d+ pt2
711+
CREATE FOREIGN TABLE pt2_1 (
712+
c1 integer NOT NULL,
713+
c2 text,
714+
c3 date
715+
) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
716+
\d+ pt2_1
717+
-- no attach partition validation occurs for foreign tables
718+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
719+
\d+ pt2
720+
\d+ pt2_1
721+
722+
-- cannot add column to a partition
723+
ALTER TABLE pt2_1 ADD c4 char;
724+
725+
-- ok to have a partition's own constraints though
726+
ALTER TABLE pt2_1 ALTER c3 SET NOT NULL;
727+
ALTER TABLE pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> '');
728+
\d+ pt2
729+
\d+ pt2_1
730+
731+
-- cannot drop inherited NOT NULL constraint from a partition
732+
ALTER TABLE pt2_1 ALTER c1 DROP NOT NULL;
733+
734+
-- partition must have parent's constraints
735+
ALTER TABLE pt2 DETACH PARTITION pt2_1;
736+
ALTER TABLE pt2 ALTER c2 SET NOT NULL;
737+
\d+ pt2
738+
\d+ pt2_1
739+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
740+
ALTER FOREIGN TABLE pt2_1 ALTER c2 SET NOT NULL;
741+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
742+
743+
ALTER TABLE pt2 DETACH PARTITION pt2_1;
744+
ALTER TABLE pt2 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
745+
\d+ pt2
746+
\d+ pt2_1
747+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1); -- ERROR
748+
ALTER FOREIGN TABLE pt2_1 ADD CONSTRAINT pt2chk1 CHECK (c1 > 0);
749+
ALTER TABLE pt2 ATTACH PARTITION pt2_1 FOR VALUES IN (1);
750+
751+
-- TRUNCATE doesn't work on foreign tables, either directly or recursively
752+
TRUNCATE pt2_1; -- ERROR
753+
TRUNCATE pt2; -- ERROR
754+
755+
DROP FOREIGN TABLE pt2_1;
756+
DROP TABLE pt2;
757+
687758
-- Cleanup
688759
DROP SCHEMA foreign_schema CASCADE;
689760
DROP ROLE regress_test_role; -- ERROR

0 commit comments

Comments
 (0)
0