Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 2d7eeb1

Browse filesBrowse files
sfrostsfrost
committed
Add additional partition tests to pg_dump
This adds a few tests for non-inherited constraints. Author: Amit Langote Discussion: https://postgr.es/m/20181208001735.GT3415%40tamriel.snowman.net
1 parent 96c702c commit 2d7eeb1
Copy full SHA for 2d7eeb1

File tree

Expand file treeCollapse file tree

3 files changed

+57
-21
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+57
-21
lines changed

‎src/bin/pg_dump/t/002_pg_dump.pl

Copy file name to clipboardExpand all lines: src/bin/pg_dump/t/002_pg_dump.pl
+7-3Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2246,12 +2246,16 @@
22462246
create_order => 91,
22472247
create_sql =>
22482248
'CREATE TABLE dump_test_second_schema.measurement_y2006m2
2249-
PARTITION OF dump_test.measurement FOR VALUES
2250-
FROM (\'2006-02-01\') TO (\'2006-03-01\');',
2249+
PARTITION OF dump_test.measurement (
2250+
unitsales DEFAULT 0 CHECK (unitsales >= 0)
2251+
)
2252+
FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');',
22512253
regexp => qr/^
22522254
\Q-- Name: measurement_y2006m2;\E.*\n
22532255
\Q--\E\n\n
2254-
\QCREATE TABLE dump_test_second_schema.measurement_y2006m2 PARTITION OF dump_test.measurement\E\n
2256+
\QCREATE TABLE dump_test_second_schema.measurement_y2006m2 PARTITION OF dump_test.measurement (\E\n
2257+
\s+\QCONSTRAINT measurement_y2006m2_unitsales_check CHECK ((unitsales >= 0))\E\n
2258+
\)\n
22552259
\QFOR VALUES FROM ('2006-02-01') TO ('2006-03-01');\E\n
22562260
/xm,
22572261
like => {

‎src/test/regress/expected/create_table.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/create_table.out
+31-14Lines changed: 31 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -703,16 +703,42 @@ CREATE TABLE part_b PARTITION OF parted (
703703
) FOR VALUES IN ('b');
704704
ERROR: column "b" specified more than once
705705
CREATE TABLE part_b PARTITION OF parted (
706-
b NOT NULL DEFAULT 1 CHECK (b >= 0),
707-
CONSTRAINT check_a CHECK (length(a) > 0)
706+
b NOT NULL DEFAULT 1,
707+
CONSTRAINT check_a CHECK (length(a) > 0),
708+
CONSTRAINT check_b CHECK (b >= 0)
708709
) FOR VALUES IN ('b');
709710
NOTICE: merging constraint "check_a" with inherited definition
710-
-- conislocal should be false for any merged constraints
711-
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a';
711+
-- conislocal should be false for any merged constraints, true otherwise
712+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
712713
conislocal | coninhcount
713714
------------+-------------
714715
f | 1
715-
(1 row)
716+
t | 0
717+
(2 rows)
718+
719+
-- Once check_b is added to the parent, it should be made non-local for part_b
720+
ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
721+
NOTICE: merging constraint "check_b" with inherited definition
722+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
723+
conislocal | coninhcount
724+
------------+-------------
725+
f | 1
726+
f | 1
727+
(2 rows)
728+
729+
-- Neither check_a nor check_b are droppable from part_b
730+
ALTER TABLE part_b DROP CONSTRAINT check_a;
731+
ERROR: cannot drop inherited constraint "check_a" of relation "part_b"
732+
ALTER TABLE part_b DROP CONSTRAINT check_b;
733+
ERROR: cannot drop inherited constraint "check_b" of relation "part_b"
734+
-- And dropping it from parted should leave no trace of them on part_b, unlike
735+
-- traditional inheritance where they will be left behind, because they would
736+
-- be local constraints.
737+
ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
738+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
739+
conislocal | coninhcount
740+
------------+-------------
741+
(0 rows)
716742

717743
-- specify PARTITION BY for a partition
718744
CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);
@@ -757,9 +783,6 @@ drop table parted_collate_must_match;
757783
b | integer | | not null | 1 | plain | |
758784
Partition of: parted FOR VALUES IN ('b')
759785
Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text))
760-
Check constraints:
761-
"check_a" CHECK (length(a) > 0)
762-
"part_b_b_check" CHECK (b >= 0)
763786

764787
-- Both partition bound and partition key in describe output
765788
\d+ part_c
@@ -771,8 +794,6 @@ Check constraints:
771794
Partition of: parted FOR VALUES IN ('c')
772795
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
773796
Partition key: RANGE (b)
774-
Check constraints:
775-
"check_a" CHECK (length(a) > 0)
776797
Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
777798

778799
-- a level-2 partition's constraint will include the parent's expressions
@@ -784,8 +805,6 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
784805
b | integer | | not null | 0 | plain | |
785806
Partition of: part_c FOR VALUES FROM (1) TO (10)
786807
Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
787-
Check constraints:
788-
"check_a" CHECK (length(a) > 0)
789808

790809
-- Show partition count in the parent's describe output
791810
-- Tempted to include \d+ output listing partitions with bound info but
@@ -798,8 +817,6 @@ Check constraints:
798817
a | text | | |
799818
b | integer | | not null | 0
800819
Partition key: LIST (a)
801-
Check constraints:
802-
"check_a" CHECK (length(a) > 0)
803820
Number of partitions: 3 (Use \d+ to list them.)
804821

805822
\d hash_parted

‎src/test/regress/sql/create_table.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/create_table.sql
+19-4Lines changed: 19 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -639,11 +639,26 @@ CREATE TABLE part_b PARTITION OF parted (
639639
) FOR VALUES IN ('b');
640640

641641
CREATE TABLE part_b PARTITION OF parted (
642-
b NOT NULL DEFAULT 1 CHECK (b >= 0),
643-
CONSTRAINT check_a CHECK (length(a) > 0)
642+
b NOT NULL DEFAULT 1,
643+
CONSTRAINT check_a CHECK (length(a) > 0),
644+
CONSTRAINT check_b CHECK (b >= 0)
644645
) FOR VALUES IN ('b');
645-
-- conislocal should be false for any merged constraints
646-
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a';
646+
-- conislocal should be false for any merged constraints, true otherwise
647+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount;
648+
649+
-- Once check_b is added to the parent, it should be made non-local for part_b
650+
ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
651+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
652+
653+
-- Neither check_a nor check_b are droppable from part_b
654+
ALTER TABLE part_b DROP CONSTRAINT check_a;
655+
ALTER TABLE part_b DROP CONSTRAINT check_b;
656+
657+
-- And dropping it from parted should leave no trace of them on part_b, unlike
658+
-- traditional inheritance where they will be left behind, because they would
659+
-- be local constraints.
660+
ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
661+
SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass;
647662

648663
-- specify PARTITION BY for a partition
649664
CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.