Re: ALTER TABLE: warn when actions do not recurse to partitions - Mailing list pgsql-hackers

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

== tests ==

CREATE TABLE m (a int NOT NULL, b int) PARTITION BY RANGE (a);
CREATE TABLE m_p1 PARTITION OF m FOR VALUES FROM (1) TO (10);
CREATE TABLE m_p2 PARTITION OF m FOR VALUES FROM (10) TO (20);

CREATE UNIQUE INDEX m_idx ON m(a);
CREATE UNIQUE INDEX m_p1_idx ON m_p1(a);
CREATE UNIQUE INDEX m_p2_idx ON m_p2(a);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX

-- issue a NOTICE (m has partitions)
ALTER TABLE m REPLICA IDENTITY NOTHING;
ALTER TABLE m REPLICA IDENTITY FULL;
ALTER TABLE m REPLICA IDENTITY DEFAULT;
ALTER TABLE m REPLICA IDENTITY USING INDEX m_idx;
NOTICE:  present partitions not affected
HINT:  partitions may be modified individually using separate commands
ALTER TABLE
NOTICE:  present partitions not affected
HINT:  partitions may be modified individually using separate commands
ALTER TABLE
NOTICE:  present partitions not affected
HINT:  partitions may be modified individually using separate commands
ALTER TABLE
NOTICE:  present partitions not affected
HINT:  partitions may be modified individually using separate commands
ALTER TABLE

-- does not issue a NOTICE (with ONLY: no recursion into partitions)
ALTER TABLE ONLY m REPLICA IDENTITY NOTHING;
ALTER TABLE ONLY m REPLICA IDENTITY FULL;
ALTER TABLE ONLY m REPLICA IDENTITY DEFAULT;
ALTER TABLE ONLY m REPLICA IDENTITY USING INDEX m_idx;
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

-- does not issue a NOTICE (p1 has no partitions)
ALTER TABLE m_p1 REPLICA IDENTITY NOTHING;
ALTER TABLE m_p1 REPLICA IDENTITY FULL;
ALTER TABLE m_p1 REPLICA IDENTITY DEFAULT;
ALTER TABLE m_p1 REPLICA IDENTITY USING INDEX m_p1_idx;
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

-- does not issue a NOTICE (m no longer has partitions)
DROP TABLE m_p1, m_p2;
DROP TABLE

ALTER TABLE m REPLICA IDENTITY NOTHING;
ALTER TABLE m REPLICA IDENTITY FULL;
ALTER TABLE m REPLICA IDENTITY DEFAULT;
ALTER TABLE m REPLICA IDENTITY USING INDEX m_idx;
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE