ALTER TABLE DROP/ADD COLUMN leaves rows in wrong partition on whole-row key

From: Michael Malis <malis(at)pgrust(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: ALTER TABLE DROP/ADD COLUMN leaves rows in wrong partition on whole-row key
Date: 2026-11-16 20:28:14
Message-ID: CANVD+MDpSX0dRahyAaanAHXNuHFYj8DTUvkk1UQ01Aobs1xF0Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Reproduced on PostgreSQL 18.7 (REL_18_7, commit f5c41f6681c9) and verified
in the source on REL_18_STABLE (commit 2f613719fa4e) and master (commit
7fc2f0423320). When a partitioned table's PARTITION BY expression contains a
whole-row reference such as (items IS NULL) or (full_items IS NOT NULL),
ALTER TABLE ... DROP COLUMN and ALTER TABLE ... ADD COLUMN succeed without
error and leave existing rows in partitions whose partition constraint they
no longer satisfy.

When an individual column is named in the PARTITION BY expression,
PostgreSQL rejects DROP COLUMN on that column so stored tuples cannot
fall outside their partition bounds. With a whole-row Var in the
partition key, dropping a non-null column or adding a column (either with
a non-null DEFAULT or with the implicit NULL default under IS NOT NULL)
changes what the partition key evaluates to for existing rows without
moving them to the matching partition. Queries with
enable_partition_pruning = on (the default) then prune the partition
holding those rows according to the catalog bounds and silently return
fewer rows than the same query with enable_partition_pruning = off.

Steps to reproduce (psql -X on a freshly initialized cluster):

-- Control: naming a column directly in the key expression blocks DROP
COLUMN
CREATE TABLE keyed (id int, note text) PARTITION BY LIST ((note IS NULL));
ALTER TABLE keyed DROP COLUMN note;
-- ERROR: cannot drop column "note" because it is part of the partition key
of relation "keyed"

-- 1. DROP COLUMN changes (items IS NULL) from false to true on ('draft')
row
CREATE TABLE items (id int, note text) PARTITION BY LIST ((items IS NULL));
CREATE TABLE items_null PARTITION OF items FOR VALUES IN (true);
CREATE TABLE items_nonnull PARTITION OF items FOR VALUES IN (false);
INSERT INTO items VALUES (NULL, NULL), (NULL, 'draft');

ALTER TABLE items DROP COLUMN note;
SELECT tableoid::regclass AS part, id, (items IS NULL) AS is_null
FROM items ORDER BY tableoid::regclass::text;
-- part | id | is_null
-- ---------------+----+---------
-- items_nonnull | | t
-- items_null | | t
-- (2 rows)

SELECT count(*) FROM items WHERE items IS NULL;
-- count
-- -------
-- 1
-- (1 row)

SET enable_partition_pruning = off;
SELECT count(*) FROM items WHERE items IS NULL;
-- count
-- -------
-- 2
-- (1 row)
RESET enable_partition_pruning;

-- 2. ADD COLUMN ... DEFAULT changes (items IS NULL) from true to false
ALTER TABLE items ADD COLUMN priority int DEFAULT 1;
SELECT count(*) FROM items WHERE NOT (items IS NULL);
-- count
-- -------
-- 1
-- (1 row)

SET enable_partition_pruning = off;
SELECT count(*) FROM items WHERE NOT (items IS NULL);
-- count
-- -------
-- 2
-- (1 row)
RESET enable_partition_pruning;

-- 3. Plain ADD COLUMN (implicit NULL) changes (full_items IS NOT NULL)
CREATE TABLE full_items (id int, note text)
PARTITION BY LIST ((full_items IS NOT NULL));
CREATE TABLE full_items_yes PARTITION OF full_items FOR VALUES IN (true);
CREATE TABLE full_items_no PARTITION OF full_items FOR VALUES IN (false);
INSERT INTO full_items VALUES (1, 'ok');
ALTER TABLE full_items ADD COLUMN extra text;

SELECT count(*) FROM full_items WHERE NOT (full_items IS NOT NULL);
-- count
-- -------
-- 0
-- (1 row)

SET enable_partition_pruning = off;
SELECT count(*) FROM full_items WHERE NOT (full_items IS NOT NULL);
-- count
-- -------
-- 1
-- (1 row)
RESET enable_partition_pruning;

Observations from the source:

1. In StorePartitionKey() (src/backend/catalog/heap.c), dependencies for
the partexprs list are recorded via recordDependencyOnSingleRelExpr()
(src/backend/catalog/dependency.c), which collects referenced objects
through find_expr_references_walker() and turns self-column entries
into reversed DEPENDENCY_INTERNAL dependencies. When var->varattno ==
InvalidAttrNumber, find_expr_references_walker() returns early without
adding any column or TypeRelationId entry to context.addrs. Commits
5db4823d00 (REL_18_STABLE, in 18.7) and d98d99ba17 (master) add a
TypeRelationId address for each partition expression's result type
(bool here, which is pinned), and commits d48d28c4b6 (REL_18_STABLE,
in 18.7) and 6e578eeb6d (master) reject ALTER COLUMN ... TYPE when
exprType(partexpr) == typeOid (such as PARTITION BY RANGE ((t))),
while CheckTypeDependencyInSingleRelExpr() shares the same walker and
likewise ignores whole-row Vars inside scalar-valued key expressions.

2. ATExecDropColumn() and ATPrepAlterColumnType()
(src/backend/commands/tablecmds.c) guard partition keys via
has_partition_attrs() (src/backend/catalog/partition.c), which calls
pull_varattnos(expr, 1, &expr_attrs) and checks
bms_overlap(attnums, expr_attrs). For a whole-row Var, pull_varattnos()
only adds InvalidAttrNumber (offset by FirstLowInvalidHeapAttributeNumber)
to expr_attrs. Unlike ComputePartitionAttrs() in
src/backend/commands/tablecmds.c, which expands that whole-row entry
across 1 through RelationGetNumberOfAttributes(rel) during CREATE TABLE,
has_partition_attrs() does not check for it and returns false for every
user column attnum >= 1. On a scalar-valued whole-row partition key,
ALTER TABLE items ALTER COLUMN note TYPE text USING NULL also passes
both has_partition_attrs() in ATPrepAlterColumnType() and
find_composite_type_dependencies() (from Observation 1), leaving rows
in the wrong partition.

3. ATExecAddColumn() (src/backend/commands/tablecmds.c) does not inspect
RelationGetPartitionKey(rel) or call has_partition_attrs(). On each
leaf partition, a non-volatile non-null DEFAULT is stored via
StoreAttrMissingVal() without moving tuples (and a volatile DEFAULT
rewrites each leaf partition in ATRewriteTable() with
tab->partition_constraint == NULL). Adding a nullable column with no
DEFAULT leaves tab->newvals == NIL and tab->rewrite == 0 while
changing ExecEvalRowNullInt() (src/backend/executor/execExprInterp.c)
for IS NOT NULL, which requires every non-dropped column to be
non-null. ATExecAddColumn() also accepts ADD COLUMN ... GENERATED
ALWAYS AS (...) STORED or VIRTUAL even though ComputePartitionAttrs()
rejects generated columns at CREATE TABLE time when a whole-row
reference is present in PARTITION BY.

4. On pgsql-hackers, CommitFest patch #5988 proposes teaching
has_partition_attrs() to check for the whole-row bitmapset member:
https://postgr.es/m/CACJufxG5wLiATocRTaC=z+kw4mUaasC-50+q9K=fOdAr3=OGRw@mail.gmail.com
Andrey Borodin also noted in review of CommitFest patch #7071 that ADD
COLUMN can mispartition whole-row partition keys (left out of scope of
that index-rebuild patch):
https://postgr.es/m/BEE8820C-50BD-467A-BDBC-F95827D737EB@yandex-team.ru
Updating has_partition_attrs() alone covers ATExecDropColumn() and
ATPrepAlterColumnType(), whereas ATExecAddColumn() would still need a
corresponding whole-row partition key check.

Cheers,
Michael

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2026-11-16 20:40:28 Re: BUG #19908: pg_upgrade --check crashes instance if running in a different PID Namespace then the old cluster
Previous Message Michael Malis 2026-11-16 20:28:12 GiST: SERIALIZABLE misses conflict when scan prunes at internal page