Skip to content

Commit 9139aa1

Browse files
sfrostsfrost
committed
Allow ALTER TABLE ONLY on partitioned tables
There is no need to forbid ALTER TABLE ONLY on partitioned tables, when no partitions exist yet. This can be handy for users who are building up their partitioned table independently and will create actual partitions later. In addition, this is how pg_dump likes to operate in certain instances. Author: Amit Langote, with some error message word-smithing by me
1 parent 5f2b48d commit 9139aa1

File tree

6 files changed

+108
-48
lines changed

6 files changed

+108
-48
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2944,17 +2944,23 @@ VALUES ('Albany', NULL, NULL, 'NY');
29442944
Both <literal>CHECK</literal> and <literal>NOT NULL</literal>
29452945
constraints of a partitioned table are always inherited by all its
29462946
partitions. <literal>CHECK</literal> constraints that are marked
2947-
<literal>NO INHERIT</literal> are not allowed.
2947+
<literal>NO INHERIT</literal> are not allowed to be created on
2948+
partitioned tables.
29482949
</para>
29492950
</listitem>
29502951

29512952
<listitem>
29522953
<para>
2953-
The <literal>ONLY</literal> notation used to exclude child tables
2954-
will cause an error for partitioned tables in the case of
2955-
schema-modifying commands such as most <literal>ALTER TABLE</literal>
2956-
commands. For example, dropping a column from only the parent does
2957-
not make sense for partitioned tables.
2954+
Using <literal>ONLY</literal> to add or drop a constraint on only the
2955+
partitioned table is supported when there are no partitions. Once
2956+
partitions exist, using <literal>ONLY</literal> will result in an error
2957+
as adding or dropping constraints on only the partitioned table, when
2958+
partitions exist, is not supported. Instead, constraints can be added
2959+
or dropped, when they are not present in the parent table, directly on
2960+
the partitions. As a partitioned table does not have any data
2961+
directly, attempts to use <command>TRUNCATE</command>
2962+
<literal>ONLY</literal> on a partitioned table will always return an
2963+
error.
29582964
</para>
29592965
</listitem>
29602966

‎src/backend/commands/tablecmds.c

Lines changed: 41 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1259,7 +1259,8 @@ ExecuteTruncate(TruncateStmt *stmt)
12591259
else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
12601260
ereport(ERROR,
12611261
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1262-
errmsg("must truncate child tables too")));
1262+
errmsg("cannot truncate only a partitioned table"),
1263+
errhint("Do not specify the ONLY keyword, or use truncate only on the partitions directly.")));
12631264
}
12641265

12651266
/*
@@ -5578,14 +5579,20 @@ static void
55785579
ATPrepDropNotNull(Relation rel, bool recurse, bool recursing)
55795580
{
55805581
/*
5581-
* If the parent is a partitioned table, like check constraints, NOT NULL
5582-
* constraints must be dropped from child tables.
5582+
* If the parent is a partitioned table, like check constraints, we do
5583+
* not support removing the NOT NULL while partitions exist.
55835584
*/
5584-
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
5585-
!recurse && !recursing)
5586-
ereport(ERROR,
5587-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
5588-
errmsg("constraint must be dropped from child tables too")));
5585+
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
5586+
{
5587+
PartitionDesc partdesc = RelationGetPartitionDesc(rel);
5588+
5589+
Assert(partdesc != NULL);
5590+
if (partdesc->nparts > 0 && !recurse && !recursing)
5591+
ereport(ERROR,
5592+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
5593+
errmsg("cannot remove constraint from only the partitioned table when partitions exist"),
5594+
errhint("Do not specify the ONLY keyword.")));
5595+
}
55895596
}
55905597
static ObjectAddress
55915598
ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
@@ -5746,13 +5753,19 @@ ATPrepSetNotNull(Relation rel, bool recurse, bool recursing)
57465753
{
57475754
/*
57485755
* If the parent is a partitioned table, like check constraints, NOT NULL
5749-
* constraints must be added to the child tables.
5756+
* constraints must be added to the child tables. Complain if requested
5757+
* otherwise and partitions exist.
57505758
*/
5751-
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
5752-
!recurse && !recursing)
5753-
ereport(ERROR,
5754-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
5755-
errmsg("constraint must be added to child tables too")));
5759+
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
5760+
{
5761+
PartitionDesc partdesc = RelationGetPartitionDesc(rel);
5762+
5763+
if (partdesc && partdesc->nparts > 0 && !recurse && !recursing)
5764+
ereport(ERROR,
5765+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
5766+
errmsg("cannot add constraint to only the partitioned table when partitions exist"),
5767+
errhint("Do not specify the ONLY keyword.")));
5768+
}
57565769
}
57575770

57585771
static ObjectAddress
@@ -6547,7 +6560,8 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
65476560
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && !recurse)
65486561
ereport(ERROR,
65496562
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6550-
errmsg("column must be dropped from child tables too")));
6563+
errmsg("cannot drop column from only the partitioned table when partitions exist"),
6564+
errhint("Do not specify the ONLY keyword.")));
65516565

65526566
attr_rel = heap_open(AttributeRelationId, RowExclusiveLock);
65536567
foreach(child, children)
@@ -8561,16 +8575,6 @@ ATExecDropConstraint(Relation rel, const char *constrName,
85618575
}
85628576
}
85638577

8564-
/*
8565-
* In case of a partitioned table, the constraint must be dropped from the
8566-
* partitions too. There is no such thing as NO INHERIT constraints in
8567-
* case of partitioned tables.
8568-
*/
8569-
if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
8570-
ereport(ERROR,
8571-
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
8572-
errmsg("constraint must be dropped from child tables too")));
8573-
85748578
/*
85758579
* Propagate to children as appropriate. Unlike most other ALTER
85768580
* routines, we have to do this one level of recursion at a time; we can't
@@ -8581,6 +8585,18 @@ ATExecDropConstraint(Relation rel, const char *constrName,
85818585
else
85828586
children = NIL;
85838587

8588+
/*
8589+
* For a partitioned table, if partitions exist and we are told not to
8590+
* recurse, it's a user error. It doesn't make sense to have a constraint
8591+
* be defined only on the parent, especially if it's a partitioned table.
8592+
*/
8593+
if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&
8594+
children != NIL && !recurse)
8595+
ereport(ERROR,
8596+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
8597+
errmsg("cannot remove constraint from only the partitioned table when partitions exist"),
8598+
errhint("Do not specify the ONLY keyword.")));
8599+
85848600
foreach(child, children)
85858601
{
85868602
Oid childrelid = lfirst_oid(child);

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

Lines changed: 25 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -3295,7 +3295,8 @@ DROP TABLE part_3_4;
32953295
ALTER TABLE ONLY list_parted2 ADD COLUMN c int;
32963296
ERROR: column must be added to child tables too
32973297
ALTER TABLE ONLY list_parted2 DROP COLUMN b;
3298-
ERROR: column must be dropped from child tables too
3298+
ERROR: cannot drop column from only the partitioned table when partitions exist
3299+
HINT: Do not specify the ONLY keyword.
32993300
-- cannot add a column to partition or drop an inherited one
33003301
ALTER TABLE part_2 ADD COLUMN c text;
33013302
ERROR: cannot add column to a partition
@@ -3306,24 +3307,37 @@ ALTER TABLE part_2 RENAME COLUMN b to c;
33063307
ERROR: cannot rename inherited column "b"
33073308
ALTER TABLE part_2 ALTER COLUMN b TYPE text;
33083309
ERROR: cannot alter inherited column "b"
3309-
-- cannot add NOT NULL or check constraints to *only* the parent (ie, non-inherited)
3310+
-- cannot add/drop NOT NULL or check constraints to *only* the parent, when
3311+
-- partitions exist
33103312
ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
3313+
ERROR: cannot add constraint to only the partitioned table when partitions exist
3314+
HINT: Do not specify the ONLY keyword.
3315+
ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
33113316
ERROR: constraint must be added to child tables too
3312-
ALTER TABLE ONLY list_parted2 add constraint check_b check (b <> 'zz');
3313-
ERROR: constraint must be added to child tables too
3314-
ALTER TABLE list_parted2 add constraint check_b check (b <> 'zz') NO INHERIT;
3315-
ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2"
3317+
ALTER TABLE list_parted2 ALTER b SET NOT NULL;
3318+
ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
3319+
ERROR: cannot remove constraint from only the partitioned table when partitions exist
3320+
HINT: Do not specify the ONLY keyword.
3321+
ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
3322+
ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
3323+
ERROR: cannot remove constraint from only the partitioned table when partitions exist
3324+
HINT: Do not specify the ONLY keyword.
3325+
-- It's alright though, if no partitions are yet created
3326+
CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
3327+
ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
3328+
ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
3329+
ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL;
3330+
ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a;
3331+
DROP TABLE parted_no_parts;
33163332
-- cannot drop inherited NOT NULL or check constraints from partition
33173333
ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
33183334
ALTER TABLE part_2 ALTER b DROP NOT NULL;
33193335
ERROR: column "b" is marked NOT NULL in parent table
33203336
ALTER TABLE part_2 DROP CONSTRAINT check_a2;
33213337
ERROR: cannot drop inherited constraint "check_a2" of relation "part_2"
3322-
-- cannot drop NOT NULL or check constraints from *only* the parent
3323-
ALTER TABLE ONLY list_parted2 ALTER a DROP NOT NULL;
3324-
ERROR: constraint must be dropped from child tables too
3325-
ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_a2;
3326-
ERROR: constraint must be dropped from child tables too
3338+
-- Doesn't make sense to add NO INHERIT constraints on partitioned tables
3339+
ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
3340+
ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2"
33273341
-- check that a partition cannot participate in regular inheritance
33283342
CREATE TABLE inh_test () INHERITS (part_2);
33293343
ERROR: cannot inherit from partition "part_2"

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -452,7 +452,15 @@ LINE 1: SELECT nextval('truncate_a_id1');
452452
^
453453
-- partitioned table
454454
CREATE TABLE truncparted (a int, b char) PARTITION BY LIST (a);
455+
-- error, can't truncate a partitioned table
456+
TRUNCATE ONLY truncparted;
457+
ERROR: cannot truncate only a partitioned table
458+
HINT: Do not specify the ONLY keyword, or use truncate only on the partitions directly.
455459
CREATE TABLE truncparted1 PARTITION OF truncparted FOR VALUES IN (1);
456460
INSERT INTO truncparted VALUES (1, 'a');
461+
-- error, must truncate partitions
462+
TRUNCATE ONLY truncparted;
463+
ERROR: cannot truncate only a partitioned table
464+
HINT: Do not specify the ONLY keyword, or use truncate only on the partitions directly.
457465
TRUNCATE truncparted;
458466
DROP TABLE truncparted;

‎src/test/regress/sql/alter_table.sql

Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -2173,19 +2173,31 @@ ALTER TABLE part_2 DROP COLUMN b;
21732173
ALTER TABLE part_2 RENAME COLUMN b to c;
21742174
ALTER TABLE part_2 ALTER COLUMN b TYPE text;
21752175

2176-
-- cannot add NOT NULL or check constraints to *only* the parent (ie, non-inherited)
2176+
-- cannot add/drop NOT NULL or check constraints to *only* the parent, when
2177+
-- partitions exist
21772178
ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL;
2178-
ALTER TABLE ONLY list_parted2 add constraint check_b check (b <> 'zz');
2179-
ALTER TABLE list_parted2 add constraint check_b check (b <> 'zz') NO INHERIT;
2179+
ALTER TABLE ONLY list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2180+
2181+
ALTER TABLE list_parted2 ALTER b SET NOT NULL;
2182+
ALTER TABLE ONLY list_parted2 ALTER b DROP NOT NULL;
2183+
ALTER TABLE list_parted2 ADD CONSTRAINT check_b CHECK (b <> 'zz');
2184+
ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_b;
2185+
2186+
-- It's alright though, if no partitions are yet created
2187+
CREATE TABLE parted_no_parts (a int) PARTITION BY LIST (a);
2188+
ALTER TABLE ONLY parted_no_parts ALTER a SET NOT NULL;
2189+
ALTER TABLE ONLY parted_no_parts ADD CONSTRAINT check_a CHECK (a > 0);
2190+
ALTER TABLE ONLY parted_no_parts ALTER a DROP NOT NULL;
2191+
ALTER TABLE ONLY parted_no_parts DROP CONSTRAINT check_a;
2192+
DROP TABLE parted_no_parts;
21802193

21812194
-- cannot drop inherited NOT NULL or check constraints from partition
21822195
ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0);
21832196
ALTER TABLE part_2 ALTER b DROP NOT NULL;
21842197
ALTER TABLE part_2 DROP CONSTRAINT check_a2;
21852198

2186-
-- cannot drop NOT NULL or check constraints from *only* the parent
2187-
ALTER TABLE ONLY list_parted2 ALTER a DROP NOT NULL;
2188-
ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_a2;
2199+
-- Doesn't make sense to add NO INHERIT constraints on partitioned tables
2200+
ALTER TABLE list_parted2 add constraint check_b2 check (b <> 'zz') NO INHERIT;
21892201

21902202
-- check that a partition cannot participate in regular inheritance
21912203
CREATE TABLE inh_test () INHERITS (part_2);

‎src/test/regress/sql/truncate.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -236,7 +236,11 @@ SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
236236

237237
-- partitioned table
238238
CREATE TABLE truncparted (a int, b char) PARTITION BY LIST (a);
239+
-- error, can't truncate a partitioned table
240+
TRUNCATE ONLY truncparted;
239241
CREATE TABLE truncparted1 PARTITION OF truncparted FOR VALUES IN (1);
240242
INSERT INTO truncparted VALUES (1, 'a');
243+
-- error, must truncate partitions
244+
TRUNCATE ONLY truncparted;
241245
TRUNCATE truncparted;
242246
DROP TABLE truncparted;

0 commit comments

Comments
 (0)