public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chao Li <[email protected]>
To: Jim Jones <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: Postgres hackers <[email protected]>
Subject: Re: ALTER TABLE: warn when actions do not recurse to partitions
Date: Thu, 22 Jan 2026 13:45:05 +0800
Message-ID: <CAEoWx2mZsWnyS_XLDL5mL+yuU_g65p_tmHNesyfngqow7gtS3A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEoWx2=SLga-xH09Cq_PAvsHhQHrBK+V0vF821JKgzS=Bm0haA@mail.gmail.com>
<CAKFQuwZXt2dAuHFs6MtRCzfMP6YZFzssuzdW2woJBVmco=CDdQ@mail.gmail.com>
<CAEoWx2nic2MaPUEKwb0Me1iwO3LsCf8wUHm55pjqaTRgJEZpcQ@mail.gmail.com>
<CAKFQuwahxpPKT-LXGJ34BO5cVBUxFxzgNPTY8E_VW-cuNU_DkQ@mail.gmail.com>
<CAEoWx2kQ9aSn-0LGPKc=woj+h-HR4uBV9TL20aeM3HzLmFqgFQ@mail.gmail.com>
<[email protected]>
<[email protected]>
On Jan 14, 2026, at 08:52, Chao Li <[email protected]> wrote:
On Jan 13, 2026, at 19:16, Jim Jones <[email protected]> wrote:
Hi Chao
On 13/01/2026 05:02, Chao Li wrote:
PSA v3:
* Rephrased the notice message as David's suggestion.
* Removed partition count from notice message.
* If a partitioned table doesn't have any partition, then suppress the
message.
I've been playing with this patch, and it seems to work as expected -
I'm surprised it didn't break any existing tests :). Do you plan to
extend this patch to other subcommands mentioned in your initial post,
such as SET STATISTICS?
Thanks for the patch
Best, Jim
Hi Jim,
Thanks for your testing. Yes, I plan to add the notice to other
sub-commands as needed. I only updated REPLICA IDENTITY first to call for
feedback. As you see, David has suggested the great wording for the notice
message. Once the change on REPLICA IDENTITY is reviewed, it’s easy to
extend to other sub-commands.
PFA v4.
I’ve extended the NOTICE to cover all sub-commands for which ONLY has no
effect and where actions on a partitioned table do not propagate to its
partitions:
- ALTER COLUMN SET/RESET attribute_option
- ALTER COLUMN SET COMPRESSION
- ENABLE/DISABLE RULE
- ENABLE/DISABLE/FORCE/NO FORCE ROW LEVEL SECURITY
- REPLICA IDENTITY
- OWNER TO
- SET TABLESPACE
- SET SCHEMA
RENAME is intentionally excluded. Using ONLY (or not) has no effect
for RENAME, since relation names are independent by nature and there is no
expectation of recursion.
OF / NOT OF are also excluded. Using ONLY has no effect for these commands,
as they apply only to the partitioned table itself and not to its
partitions.
One thing worth noting: following David’s suggestion, I removed the action
name from the NOTICE message in v2. However, I later realized that we do
need to include the action name, because an ALTER TABLE command may contain
multiple sub-commands, and the NOTICE would otherwise be ambiguous.
In v4, I reuse alter_table_type_to_string() to construct the action name,
consistent with what ATSimplePermissions() does. The NOTICE message itself
also follows the same style as messages emitted by ATSimplePermissions().
For example, when an ALTER TABLE contains multiple sub-commands, the output
now looks like:
```
evantest=# alter table p_test replica identity full, alter column username
set (n_distinct = 0.1);
NOTICE: ALTER action REPLICA IDENTITY on relation "p_test" does not affect
present partitions
HINT: partitions may be modified individually, or specify ONLY to suppress
this message
NOTICE: ALTER action ALTER COLUMN ... SET on relation "p_test" does not
affect present partitions
HINT: partitions may be modified individually, or specify ONLY to suppress
this message
ALTER TABLE
```
Regression tests have been updated, and a few new tests have been added. v4
should now be ready for review.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v4-0001-ALTER-TABLE-emit-NOTICE-when-ONLY-is-omitted-but-.patch (32.9K, 3-v4-0001-ALTER-TABLE-emit-NOTICE-when-ONLY-is-omitted-but-.patch)
download | inline diff:
From 3b89bafbe5101eecec84a79b87344ce5bdc338a2 Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 12 Jan 2026 16:56:58 +0800
Subject: [PATCH v4] ALTER TABLE: emit NOTICE when ONLY is omitted but no
partition recursion occurs
Some ALTER TABLE sub-commands do not recurse to partitions even when ONLY
is not specified on a partitioned table. This can be surprising, since
the default expectation is that commands propagate to all partitions.
Emit a NOTICE in such cases to make the behavior explicit, and advise how
to suppress the message or modify partitions individually.
Affected sub-commands include:
- ALTER COLUMN SET/RESET attribute_option
- ALTER COLUMN SET COMPRESSION
- ENABLE/DISABLE RULE
- ENABLE/DISABLE/FORCE/NO FORCE ROW LEVEL SECURITY
- REPLICA IDENTITY
- OWNER TO
- SET TABLESPACE
- SET SCHEMA
RENAME is intentionally excluded. Using ONLY (or not) has no effect for
RENAME, since relation names are independent by nature and there is no
expectation of recursion.
OF / NOT OF are also excluded. Using ONLY has no effect for these
commands, as they apply only to the partitioned table itself and not to
its partitions.
Regression tests are updated to use ONLY where appropriate.
Author: Chao Li <[email protected]>
Reviewed-by: David G. Johnston <[email protected]>
Reviewed-by: Greg Sabino Mullane <[email protected]>
Discussion: https://postgr.es/m/CAEoWx2=SLga-xH09Cq_PAvsHhQHrBK+V0vF821JKgzS=Bm0haA@mail.gmail.com
---
src/backend/commands/tablecmds.c | 72 +++++++++++++++++--
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/alter_table.out | 68 +++++++++++++++++-
src/test/regress/expected/cluster.out | 2 +-
src/test/regress/expected/merge.out | 4 +-
src/test/regress/expected/partition_merge.out | 2 +-
src/test/regress/expected/partition_split.out | 2 +-
src/test/regress/expected/privileges.out | 2 +-
src/test/regress/expected/rowsecurity.out | 4 +-
src/test/regress/expected/tablespace.out | 4 +-
src/test/regress/expected/update.out | 2 +-
src/test/regress/expected/vacuum.out | 6 +-
src/test/regress/sql/alter_table.sql | 51 ++++++++++++-
src/test/regress/sql/cluster.sql | 2 +-
src/test/regress/sql/merge.sql | 4 +-
src/test/regress/sql/partition_merge.sql | 2 +-
src/test/regress/sql/partition_split.sql | 2 +-
src/test/regress/sql/privileges.sql | 2 +-
src/test/regress/sql/rowsecurity.sql | 4 +-
src/test/regress/sql/tablespace.sql | 4 +-
src/test/regress/sql/update.sql | 2 +-
src/test/regress/sql/vacuum.sql | 6 +-
22 files changed, 212 insertions(+), 36 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..ab8849406da 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -745,6 +745,7 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static void EmitPartitionNoRecurseNotice(AlterTableType cmdtype, Relation rel, bool recurse, bool recursing);
/* ----------------------------------------------------------------
* DefineRelation
@@ -5056,6 +5057,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATT_MATVIEW | ATT_FOREIGN_TABLE);
/* This command never recurses */
pass = AT_PASS_MISC;
+ /* Emit a notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(cmd->subtype, rel,
@@ -5071,6 +5074,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* This command never recurses */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
+ /* Emit a notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
break;
case AT_DropColumn: /* DROP COLUMN */
ATSimplePermissions(cmd->subtype, rel,
@@ -5138,6 +5143,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* This command never recurses */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
+ /* Emit a notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
break;
case AT_ClusterOn: /* CLUSTER ON */
case AT_DropCluster: /* SET WITHOUT CLUSTER */
@@ -5181,6 +5188,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* This command never recurses */
ATPrepSetTableSpace(tab, rel, cmd->name, lockmode);
pass = AT_PASS_MISC; /* doesn't actually matter */
+ /* Emit a notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
break;
case AT_SetRelOptions: /* SET (...) */
case AT_ResetRelOptions: /* RESET (...) */
@@ -5190,6 +5199,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATT_MATVIEW | ATT_INDEX);
/* This command never recurses */
/* No command-specific prep needed */
+ /* It will check for partitioned table at exec time */
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
@@ -5227,8 +5237,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
ATSimplePermissions(cmd->subtype, rel,
ATT_TABLE | ATT_PARTITIONED_TABLE | ATT_MATVIEW);
pass = AT_PASS_MISC;
- /* This command never recurses */
- /* No command-specific prep needed */
+ /* This command doesn't recurse to partitions, so notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
break;
case AT_EnableTrig: /* ENABLE TRIGGER variants */
case AT_EnableAlwaysTrig:
@@ -5249,17 +5259,30 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
- case AT_AddOf: /* OF */
- case AT_DropOf: /* NOT OF */
- case AT_EnableRowSecurity:
+ case AT_EnableRowSecurity: /* ENABLE/DISABLE ROW SECURITY variants */
case AT_DisableRowSecurity:
- case AT_ForceRowSecurity:
+ case AT_ForceRowSecurity: /* FORCE/NO FORCE ROW SECURITY variants */
case AT_NoForceRowSecurity:
ATSimplePermissions(cmd->subtype, rel,
ATT_TABLE | ATT_PARTITIONED_TABLE);
/* These commands never recurse */
/* No command-specific prep needed */
pass = AT_PASS_MISC;
+ /* Emit a notice if needed */
+ EmitPartitionNoRecurseNotice(cmd->subtype, rel, recurse, recursing);
+ break;
+ case AT_AddOf: /* OF */
+ case AT_DropOf: /* NOT OF */
+ ATSimplePermissions(cmd->subtype, rel,
+ ATT_TABLE | ATT_PARTITIONED_TABLE);
+ /* These commands never recurse */
+ /* No command-specific prep needed */
+
+ /*
+ * They only work on partitioned tables but child partitions, thus
+ * no need to emit a notice
+ */
+ pass = AT_PASS_MISC;
break;
case AT_GenericOptions:
ATSimplePermissions(cmd->subtype, rel, ATT_FOREIGN_TABLE);
@@ -6752,6 +6775,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP IDENTITY";
case AT_ReAddStatistics:
return NULL; /* not real grammar */
+ case AT_SetSchema:
+ return "SET SCHEMA";
}
return NULL;
@@ -18996,6 +19021,13 @@ AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema)
rel = relation_open(relid, NoLock);
+ /*
+ * SET SCHEMA doesn't recurse to children, emit a notice if ONLY is not
+ * specified. As this action doesn't go through ATPrepCmd, we have to emit
+ * the notice here.
+ */
+ EmitPartitionNoRecurseNotice(AT_SetSchema, rel, stmt->relation->inh, false);
+
oldNspOid = RelationGetNamespace(rel);
/* If it's an owned sequence, disallow moving it by itself. */
@@ -23340,3 +23372,31 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Restore the userid and security context. */
SetUserIdAndSecContext(save_userid, save_sec_context);
}
+
+/*
+ * When ONLY is not specified with a partitioned table, it is expected that the
+ * command recurses to all partitions. However, some sub-commands do not recurse.
+ * In such cases, emit a NOTICE to make this behavior explicit to the user.
+ */
+static void
+EmitPartitionNoRecurseNotice(AlterTableType cmdtype, Relation rel, bool recurse, bool recursing)
+{
+ /* Only emit the notice at the top level of recursion */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && recurse && !recursing)
+ {
+ PartitionDesc pd = RelationGetPartitionDesc(rel, true);
+ int nparts = pd->nparts;
+ const char *action_str;
+
+ /* Emit a notice only if there are partitions */
+ if (nparts == 0)
+ return;
+
+ action_str = alter_table_type_to_string(cmdtype);
+ ereport(NOTICE,
+ errmsg("ALTER action %s on relation \"%s\" does not affect present partitions",
+ action_str,
+ RelationGetRelationName(rel)),
+ errhint("partitions may be modified individually, or specify ONLY to suppress this message"));
+ }
+}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..c61cc3c0eaf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2509,6 +2509,7 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_SetSchema, /* SET SCHEMA */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index ac1a7345d0f..916761f4d0f 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4572,8 +4572,74 @@ SELECT * FROM list_parted;
---
(0 rows)
+CREATE TABLE list_parted4 (a int, b text) PARTITION BY LIST (a);
+CREATE TABLE list_parted4_1 PARTITION OF list_parted4 FOR VALUES IN (1);
+-- set column attribute on partitioned table should get a notice
+ALTER TABLE list_parted4 ALTER COLUMN b SET (n_distinct = 0.2);
+NOTICE: ALTER action ALTER COLUMN ... SET on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE list_parted4 ALTER COLUMN b RESET (n_distinct);
+NOTICE: ALTER action ALTER COLUMN ... RESET on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b SET (n_distinct = 0.2);
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b RESET (n_distinct);
+-- enable/disable rules on partitioned tables should get a notice
+CREATE RULE list_parted4_rule AS ON INSERT TO list_parted4 DO INSTEAD NOTHING;
+ALTER TABLE list_parted4 DISABLE RULE list_parted4_rule;
+NOTICE: ALTER action DISABLE RULE on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 DISABLE RULE list_parted4_rule;
+ALTER TABLE list_parted4 ENABLE RULE list_parted4_rule;
+NOTICE: ALTER action ENABLE RULE on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 ENABLE RULE list_parted4_rule;
+DROP RULE list_parted4_rule ON list_parted4;
+-- enable/disable row level security on partitioned tables should get a notice
+ALTER TABLE list_parted4 ENABLE ROW LEVEL SECURITY;
+NOTICE: ALTER action ENABLE ROW SECURITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE list_parted4 DISABLE ROW LEVEL SECURITY;
+NOTICE: ALTER action DISABLE ROW SECURITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 DISABLE ROW LEVEL SECURITY;
+-- force/no force row level security on partitioned tables should get a notice
+ALTER TABLE list_parted4 FORCE ROW LEVEL SECURITY;
+NOTICE: ALTER action FORCE ROW SECURITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 FORCE ROW LEVEL SECURITY;
+ALTER TABLE list_parted4 NO FORCE ROW LEVEL SECURITY;
+NOTICE: ALTER action NO FORCE ROW SECURITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 NO FORCE ROW LEVEL SECURITY;
+-- set replica identity on partitioned tables should get a notice
+ALTER TABLE list_parted4 REPLICA IDENTITY FULL;
+NOTICE: ALTER action REPLICA IDENTITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 REPLICA IDENTITY FULL;
+ALTER TABLE list_parted4 REPLICA IDENTITY NOTHING;
+NOTICE: ALTER action REPLICA IDENTITY on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 REPLICA IDENTITY NOTHING;
+-- set compression on partitioned tables should get a notice
+ALTER TABLE list_parted4 ALTER COLUMN b SET COMPRESSION pglz;
+NOTICE: ALTER action ALTER COLUMN ... SET COMPRESSION on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b SET COMPRESSION pglz;
+-- set owner on partitioned tables should get a notice
+ALTER TABLE list_parted4 OWNER TO regress_alter_table_user1;
+NOTICE: ALTER action OWNER TO on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY list_parted4 OWNER TO regress_alter_table_user1;
+-- set schema on partitioned tables should get a notice
+CREATE SCHEMA alter_table_test_schema;
+ALTER TABLE list_parted4 SET SCHEMA alter_table_test_schema;
+NOTICE: ALTER action SET SCHEMA on relation "list_parted4" does not affect present partitions
+HINT: partitions may be modified individually, or specify ONLY to suppress this message
+ALTER TABLE ONLY alter_table_test_schema.list_parted4 SET SCHEMA public;
+DROP SCHEMA alter_table_test_schema CASCADE;
-- cleanup
-DROP TABLE list_parted, list_parted2, range_parted, list_parted3;
+DROP TABLE list_parted, list_parted2, range_parted, list_parted3, list_parted4;
DROP TABLE fail_def_part;
DROP TABLE hash_parted;
-- more tests for certain multi-level partitioning scenarios
diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out
index 4d40a6809ab..6ea8c99d092 100644
--- a/src/test/regress/expected/cluster.out
+++ b/src/test/regress/expected/cluster.out
@@ -507,7 +507,7 @@ SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
ERROR: permission denied for table ptnowner
RESET SESSION AUTHORIZATION;
-ALTER TABLE ptnowner OWNER TO regress_ptnowner;
+ALTER TABLE ONLY ptnowner OWNER TO regress_ptnowner;
CREATE TEMP TABLE ptnowner_oldnodes AS
SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
JOIN pg_class AS c ON c.oid=tree.relid;
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
index 9cb1d87066a..bd6aac72336 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -2298,8 +2298,8 @@ SELECT * FROM pa_target ORDER BY tid, val;
ROLLBACK;
-- test RLS enforcement
BEGIN;
-ALTER TABLE pa_target ENABLE ROW LEVEL SECURITY;
-ALTER TABLE pa_target FORCE ROW LEVEL SECURITY;
+ALTER TABLE ONLY pa_target ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY pa_target FORCE ROW LEVEL SECURITY;
CREATE POLICY pa_target_pol ON pa_target USING (tid != 0);
MERGE INTO pa_target t
USING pa_source s
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 925fe4f570a..f629fc7f3e5 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -810,7 +810,7 @@ SET SESSION AUTHORIZATION regress_partition_merge_bob;
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: must be owner of table t
RESET SESSION AUTHORIZATION;
-ALTER TABLE t OWNER TO regress_partition_merge_bob;
+ALTER TABLE ONLY t OWNER TO regress_partition_merge_bob;
SET SESSION AUTHORIZATION regress_partition_merge_bob;
-- ERROR: must be owner of table tp_0_1
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 4004efe0dac..421fa36ec0f 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1372,7 +1372,7 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
ERROR: must be owner of table t
RESET SESSION AUTHORIZATION;
-ALTER TABLE t OWNER TO regress_partition_split_bob;
+ALTER TABLE ONLY t OWNER TO regress_partition_split_bob;
SET SESSION AUTHORIZATION regress_partition_split_bob;
ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
(PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index daafaa94fde..8687e50fd50 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1882,7 +1882,7 @@ SELECT brin_summarize_range('sro_brin', 0);
DROP TABLE sro_tab;
-- Check with a partitioned table
CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a);
-ALTER TABLE sro_ptab OWNER TO regress_sro_user;
+ALTER TABLE ONLY sro_ptab OWNER TO regress_sro_user;
CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10);
ALTER TABLE sro_part OWNER TO regress_sro_user;
INSERT INTO sro_ptab VALUES (1), (2), (3);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index c958ef4d70a..d751de84920 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1188,7 +1188,7 @@ INSERT INTO part_document VALUES
( 8, 55, 2, 'regress_rls_carol', 'great satire'),
( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'),
(10, 99, 2, 'regress_rls_dave', 'awesome technology book');
-ALTER TABLE part_document ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY part_document ENABLE ROW LEVEL SECURITY;
-- Create policy on parent
-- user's security level must be higher than or equal to document's
CREATE POLICY pp1 ON part_document AS PERMISSIVE
@@ -4821,7 +4821,7 @@ CREATE TABLE rls_ptbl (a int) PARTITION BY RANGE (a);
CREATE TABLE rls_part PARTITION OF rls_ptbl FOR VALUES FROM (-100) TO (100);
INSERT INTO rls_ptbl SELECT x/10 FROM generate_series(1, 100) x;
ANALYZE rls_ptbl, rls_part;
-ALTER TABLE rls_ptbl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY rls_ptbl ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_part ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON rls_ptbl TO regress_rls_alice;
GRANT SELECT ON rls_part TO regress_rls_alice;
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index a90e39e5738..eef9de7d9ca 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -290,13 +290,13 @@ CREATE TABLE testschema.part_2 PARTITION OF testschema.part FOR VALUES IN (2);
SET default_tablespace TO pg_global;
CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
ERROR: only shared relations can be placed in pg_global tablespace
-ALTER TABLE testschema.part SET TABLESPACE regress_tblspace;
+ALTER TABLE ONLY testschema.part SET TABLESPACE regress_tblspace;
CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
CREATE TABLE testschema.part_4 PARTITION OF testschema.part FOR VALUES IN (4)
TABLESPACE pg_default;
CREATE TABLE testschema.part_56 PARTITION OF testschema.part FOR VALUES IN (5, 6)
PARTITION BY LIST (a);
-ALTER TABLE testschema.part SET TABLESPACE pg_default;
+ALTER TABLE ONLY testschema.part SET TABLESPACE pg_default;
CREATE TABLE testschema.part_78 PARTITION OF testschema.part FOR VALUES IN (7, 8)
PARTITION BY LIST (a);
ERROR: only shared relations can be placed in pg_global tablespace
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index eef2bac1cbf..584b40598eb 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -607,7 +607,7 @@ DROP TRIGGER trig_d15_20 ON part_d_15_20;
DROP FUNCTION func_parted_mod_b();
-- RLS policies with update-row-movement
-----------------------------------------
-ALTER TABLE range_parted ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY range_parted ENABLE ROW LEVEL SECURITY;
CREATE USER regress_range_parted_user;
GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
CREATE POLICY seeall ON range_parted AS PERMISSIVE FOR SELECT USING (true);
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index d4696bc3325..f22bb2127e1 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -614,7 +614,7 @@ VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Partitioned table and one partition owned by other user.
-ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE ONLY vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
@@ -634,7 +634,7 @@ VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only one partition owned by other user.
-ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
+ALTER TABLE ONLY vacowned_parted OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
@@ -656,7 +656,7 @@ VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
RESET ROLE;
-- Only partitioned table owned by other user.
-ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE ONLY vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 417202430a5..cea18d27ae3 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2900,8 +2900,57 @@ ALTER TABLE list_parted2 ALTER COLUMN b TYPE text;
ALTER TABLE list_parted DROP COLUMN b;
SELECT * FROM list_parted;
+CREATE TABLE list_parted4 (a int, b text) PARTITION BY LIST (a);
+CREATE TABLE list_parted4_1 PARTITION OF list_parted4 FOR VALUES IN (1);
+
+-- set column attribute on partitioned table should get a notice
+ALTER TABLE list_parted4 ALTER COLUMN b SET (n_distinct = 0.2);
+ALTER TABLE list_parted4 ALTER COLUMN b RESET (n_distinct);
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b SET (n_distinct = 0.2);
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b RESET (n_distinct);
+
+-- enable/disable rules on partitioned tables should get a notice
+CREATE RULE list_parted4_rule AS ON INSERT TO list_parted4 DO INSTEAD NOTHING;
+ALTER TABLE list_parted4 DISABLE RULE list_parted4_rule;
+ALTER TABLE ONLY list_parted4 DISABLE RULE list_parted4_rule;
+ALTER TABLE list_parted4 ENABLE RULE list_parted4_rule;
+ALTER TABLE ONLY list_parted4 ENABLE RULE list_parted4_rule;
+DROP RULE list_parted4_rule ON list_parted4;
+
+-- enable/disable row level security on partitioned tables should get a notice
+ALTER TABLE list_parted4 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY list_parted4 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE list_parted4 DISABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY list_parted4 DISABLE ROW LEVEL SECURITY;
+
+-- force/no force row level security on partitioned tables should get a notice
+ALTER TABLE list_parted4 FORCE ROW LEVEL SECURITY;
+ALTER TABLE ONLY list_parted4 FORCE ROW LEVEL SECURITY;
+ALTER TABLE list_parted4 NO FORCE ROW LEVEL SECURITY;
+ALTER TABLE ONLY list_parted4 NO FORCE ROW LEVEL SECURITY;
+
+-- set replica identity on partitioned tables should get a notice
+ALTER TABLE list_parted4 REPLICA IDENTITY FULL;
+ALTER TABLE ONLY list_parted4 REPLICA IDENTITY FULL;
+ALTER TABLE list_parted4 REPLICA IDENTITY NOTHING;
+ALTER TABLE ONLY list_parted4 REPLICA IDENTITY NOTHING;
+
+-- set compression on partitioned tables should get a notice
+ALTER TABLE list_parted4 ALTER COLUMN b SET COMPRESSION pglz;
+ALTER TABLE ONLY list_parted4 ALTER COLUMN b SET COMPRESSION pglz;
+
+-- set owner on partitioned tables should get a notice
+ALTER TABLE list_parted4 OWNER TO regress_alter_table_user1;
+ALTER TABLE ONLY list_parted4 OWNER TO regress_alter_table_user1;
+
+-- set schema on partitioned tables should get a notice
+CREATE SCHEMA alter_table_test_schema;
+ALTER TABLE list_parted4 SET SCHEMA alter_table_test_schema;
+ALTER TABLE ONLY alter_table_test_schema.list_parted4 SET SCHEMA public;
+DROP SCHEMA alter_table_test_schema CASCADE;
+
-- cleanup
-DROP TABLE list_parted, list_parted2, range_parted, list_parted3;
+DROP TABLE list_parted, list_parted2, range_parted, list_parted3, list_parted4;
DROP TABLE fail_def_part;
DROP TABLE hash_parted;
diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql
index b7115f86104..06cdb311b97 100644
--- a/src/test/regress/sql/cluster.sql
+++ b/src/test/regress/sql/cluster.sql
@@ -241,7 +241,7 @@ ALTER TABLE ptnowner1 OWNER TO regress_ptnowner;
SET SESSION AUTHORIZATION regress_ptnowner;
CLUSTER ptnowner USING ptnowner_i_idx;
RESET SESSION AUTHORIZATION;
-ALTER TABLE ptnowner OWNER TO regress_ptnowner;
+ALTER TABLE ONLY ptnowner OWNER TO regress_ptnowner;
CREATE TEMP TABLE ptnowner_oldnodes AS
SELECT oid, relname, relfilenode FROM pg_partition_tree('ptnowner') AS tree
JOIN pg_class AS c ON c.oid=tree.relid;
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index 2660b19f238..2bffcf7aa98 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -1422,8 +1422,8 @@ ROLLBACK;
-- test RLS enforcement
BEGIN;
-ALTER TABLE pa_target ENABLE ROW LEVEL SECURITY;
-ALTER TABLE pa_target FORCE ROW LEVEL SECURITY;
+ALTER TABLE ONLY pa_target ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY pa_target FORCE ROW LEVEL SECURITY;
CREATE POLICY pa_target_pol ON pa_target USING (tid != 0);
MERGE INTO pa_target t
USING pa_source s
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index a211fee2ad1..73bffb530dd 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -571,7 +571,7 @@ SET SESSION AUTHORIZATION regress_partition_merge_bob;
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
RESET SESSION AUTHORIZATION;
-ALTER TABLE t OWNER TO regress_partition_merge_bob;
+ALTER TABLE ONLY t OWNER TO regress_partition_merge_bob;
SET SESSION AUTHORIZATION regress_partition_merge_bob;
-- ERROR: must be owner of table tp_0_1
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 37c6d730840..f4f9c7886e4 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -960,7 +960,7 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); --error
RESET SESSION AUTHORIZATION;
-ALTER TABLE t OWNER TO regress_partition_split_bob;
+ALTER TABLE ONLY t OWNER TO regress_partition_split_bob;
SET SESSION AUTHORIZATION regress_partition_split_bob;
ALTER TABLE t SPLIT PARTITION tp_0_2 INTO
(PARTITION tp_0_1 FOR VALUES FROM (0) TO (1),
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 96eff1104d2..55f043fd049 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1212,7 +1212,7 @@ SELECT brin_summarize_range('sro_brin', 0);
DROP TABLE sro_tab;
-- Check with a partitioned table
CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a);
-ALTER TABLE sro_ptab OWNER TO regress_sro_user;
+ALTER TABLE ONLY sro_ptab OWNER TO regress_sro_user;
CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10);
ALTER TABLE sro_part OWNER TO regress_sro_user;
INSERT INTO sro_ptab VALUES (1), (2), (3);
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 5d923c5ca3b..0d70d260dfd 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -483,7 +483,7 @@ INSERT INTO part_document VALUES
( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'),
(10, 99, 2, 'regress_rls_dave', 'awesome technology book');
-ALTER TABLE part_document ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY part_document ENABLE ROW LEVEL SECURITY;
-- Create policy on parent
-- user's security level must be higher than or equal to document's
@@ -2358,7 +2358,7 @@ CREATE TABLE rls_part PARTITION OF rls_ptbl FOR VALUES FROM (-100) TO (100);
INSERT INTO rls_ptbl SELECT x/10 FROM generate_series(1, 100) x;
ANALYZE rls_ptbl, rls_part;
-ALTER TABLE rls_ptbl ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY rls_ptbl ENABLE ROW LEVEL SECURITY;
ALTER TABLE rls_part ENABLE ROW LEVEL SECURITY;
GRANT SELECT ON rls_ptbl TO regress_rls_alice;
GRANT SELECT ON rls_part TO regress_rls_alice;
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index dfe3db096e2..c86ec261ed5 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -195,13 +195,13 @@ SET default_tablespace TO regress_tblspace;
CREATE TABLE testschema.part_2 PARTITION OF testschema.part FOR VALUES IN (2);
SET default_tablespace TO pg_global;
CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
-ALTER TABLE testschema.part SET TABLESPACE regress_tblspace;
+ALTER TABLE ONLY testschema.part SET TABLESPACE regress_tblspace;
CREATE TABLE testschema.part_3 PARTITION OF testschema.part FOR VALUES IN (3);
CREATE TABLE testschema.part_4 PARTITION OF testschema.part FOR VALUES IN (4)
TABLESPACE pg_default;
CREATE TABLE testschema.part_56 PARTITION OF testschema.part FOR VALUES IN (5, 6)
PARTITION BY LIST (a);
-ALTER TABLE testschema.part SET TABLESPACE pg_default;
+ALTER TABLE ONLY testschema.part SET TABLESPACE pg_default;
CREATE TABLE testschema.part_78 PARTITION OF testschema.part FOR VALUES IN (7, 8)
PARTITION BY LIST (a);
CREATE TABLE testschema.part_910 PARTITION OF testschema.part FOR VALUES IN (9, 10)
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index 8b4707eb9c3..56b1edb00ce 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -341,7 +341,7 @@ DROP FUNCTION func_parted_mod_b();
-- RLS policies with update-row-movement
-----------------------------------------
-ALTER TABLE range_parted ENABLE ROW LEVEL SECURITY;
+ALTER TABLE ONLY range_parted ENABLE ROW LEVEL SECURITY;
CREATE USER regress_range_parted_user;
GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
CREATE POLICY seeall ON range_parted AS PERMISSIVE FOR SELECT USING (true);
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 247b8e23b23..acff5824ebb 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -452,7 +452,7 @@ VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
RESET ROLE;
-- Partitioned table and one partition owned by other user.
-ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE ONLY vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO regress_vacuum;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
@@ -466,7 +466,7 @@ VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
RESET ROLE;
-- Only one partition owned by other user.
-ALTER TABLE vacowned_parted OWNER TO CURRENT_USER;
+ALTER TABLE ONLY vacowned_parted OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
VACUUM vacowned_part1;
@@ -479,7 +479,7 @@ VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
RESET ROLE;
-- Only partitioned table owned by other user.
-ALTER TABLE vacowned_parted OWNER TO regress_vacuum;
+ALTER TABLE ONLY vacowned_parted OWNER TO regress_vacuum;
ALTER TABLE vacowned_part1 OWNER TO CURRENT_USER;
SET ROLE regress_vacuum;
VACUUM vacowned_parted;
--
2.50.1 (Apple Git-155)
view thread (19+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: ALTER TABLE: warn when actions do not recurse to partitions
In-Reply-To: <CAEoWx2mZsWnyS_XLDL5mL+yuU_g65p_tmHNesyfngqow7gtS3A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox