pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
5+ messages / 3 participants
[nested] [flat]

* BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
@ 2026-11-15 04:47 PG Bug reporting form <noreply@postgresql.org>
  2026-11-16 20:11 ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Robert Haas <robertmhaas@gmail.com>
  2026-11-16 22:53 ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Andres Freund <andres@anarazel.de>
  0 siblings, 2 replies; 5+ messages in thread

From: PG Bug reporting form @ 2026-11-15 04:47 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: hackerzheng666@gmail.com

The following bug has been logged on the website:

Bug reference:      19903
Logged by:          Zheng Hacker
Email address:      hackerzheng666@gmail.com
PostgreSQL version: 19rc2
Operating system:   Linux x86_64 (Ubuntu 22.04)
Description:        

Tested on git master (20devel, commit 70c4a57b64d3; the code in question is
unchanged at master 81b2179 as of 2026-11-14). Not tested on 19rc2 or older
branches; the version selected in the form is only the closest listed one.

ALTER COLUMN TYPE on a column of a table that is also used as a composite
type raises an internal (XX000) error if the partition key expression of
another table selects a field of that composite type:

create table attbl(a numeric);
create table atref(b text) partition by range ((('(1)'::attbl).a +
length(b)));
alter table attbl alter column a type bigint;
ERROR:  unexpected object depending on column: table atref

The same dependency through an index expression or a CHECK constraint gives
the expected user error:

  index expression : ERROR:  cannot alter table "attbl" because index
atref_1_numeric_length_b_idx uses its row type
  CHECK constraint : ERROR:  cannot alter table "attbl" because constraint
atref_check on table atref uses its row type

In ATExecAlterColumnType() (tablecmds.c), the scan over the column's
pg_depend entries has a "case RelationRelationId" branch that handles
indexes and sequences and raises elog(ERROR, "unexpected object depending on
column: %s") for every other relation kind. A partitioned table whose
partition key expression selects a field of the altered composite type
depends on that column through its pg_depend entry for the relation itself,
so it ends up in that branch.

Expected: a user-facing error in the same style as the index and constraint
cases, for example "cannot alter table "attbl" because partition key of
table "atref" uses its row type".

(Found with a fuzzer; reduced by hand. Checked on current master only. A
bare partition key (('(1)'::attbl).a) is rejected earlier with "cannot use
constant expression as partition key", hence the "+ length(b)".)

Related: BUG #19559 reports the same "unexpected object depending on column"
elog from the same dependency scan for a property graph; this report is the
partition-key-expression case, which falls in the RelationRelationId branch.








^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
  2026-11-15 04:47 BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-16 20:11 ` Robert Haas <robertmhaas@gmail.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Robert Haas @ 2026-11-16 20:11 UTC (permalink / raw)
  To: hackerzheng666@gmail.com; pgsql-bugs@lists.postgresql.org

On Mon, Nov 16, 2026 at 10:55 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
> ALTER COLUMN TYPE on a column of a table that is also used as a composite
> type raises an internal (XX000) error if the partition key expression of
> another table selects a field of that composite type:
>
> create table attbl(a numeric);
> create table atref(b text) partition by range ((('(1)'::attbl).a +
> length(b)));
> alter table attbl alter column a type bigint;
> ERROR:  unexpected object depending on column: table atref

Yeah, that's not great. Here's what my LLM thinks:

RememberAllDependentForRebuilding() expects the only relations that
depend directly on a column to be indexes and sequences. A partition
key can depend on one too, because selecting a field from a composite
value records a dependency on that column. That's been so since 2017,
so all supported branches have this.

Just ignoring partitioned tables there won't do, though. In the
reporter's example the rowtype check that produces the index and CHECK
messages runs later and would still reject the ALTER. But a key that
gets at the field through a function result doesn't use the rowtype at
all:

    create function mk(text) returns attbl immutable language sql
      as $$ select row(length($1))::public.attbl $$;
    create table atref(b text) partition by range (((mk(b)).a));
    alter table attbl alter column a type bigint;

With partitioned tables skipped, that ALTER succeeds, and afterwards an
INSERT into atref from a new session fails with "attribute 1 has wrong
type". We can't rebuild a partition key, so the column dependency is
all that stands in the way, and the error belongs here.

The attached reports "cannot alter type of a column used in a partition
key expression", with a detail naming the partitioned table. I prefer
that to the "uses its row type" wording suggested in the report because
it's also accurate for the function case. As 42b041243c (Tom Lane,
2024-04-28) did for SQL function bodies, it lets ALTER COLUMN SET
EXPRESSION through, since that can't change the column's type.

Since this only changes cases that fail today, I think it should go
back to 14. Branches before 17 have no SET EXPRESSION, so that part
drops out there.

-- 
Robert Haas
Databricks

Attachments:

0001-Throw-a-more-on-point-error-for-partition-keys-depen.patch (application/octet-stream, 6.5K) download From 5f213c9867cc04d5c09f5bf4749d63113113dafa Mon Sep 17 00:00:00 2001 From: Robert Haas <rhaas@postgresql.org> Date: Mon, 16 Nov 2026 14:54:36 -0500 Subject: [PATCH] Throw a more on-point error for partition keys depending on columns. A partition key expression that selects a field of another table's rowtype, such as (('(1)'::othertab).a + length(b)), depends on that column, but ALTER COLUMN TYPE didn't expect any partitioned table to depend on a column and failed with "unexpected object depending on column". Such an ALTER has to be refused, because the partition key can't be rebuilt, so throw a user-facing error instead. ALTER COLUMN SET EXPRESSION tolerates the dependency, since it doesn't change the column's type, as commit 42b041243c00fb20023c983357e7f1ffd3710fff arranged for SQL function bodies. Reported-by: Zheng Hacker <hackerzheng666@gmail.com> Discussion: https://postgr.es/m/19903-06341b8535c78fc1@postgresql.org Backpatch-through: 14 --- src/backend/commands/tablecmds.c | 19 +++++++++++++++++++ .../regress/expected/alter_column_type.out | 16 +++++++++++++++- .../regress/expected/generated_stored.out | 1 - .../regress/expected/generated_virtual.out | 1 - src/test/regress/sql/alter_column_type.sql | 12 ++++++++++++ 5 files changed, 46 insertions(+), 3 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b670092945..f872558137 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16266,6 +16266,25 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype, */ Assert(foundObject.objectSubId == 0); } + else if (relKind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partition key expression can depend on a column + * of another table by selecting a field of that + * table's rowtype. We have no way to rebuild a + * partition key, so changing the column's type has to + * be rejected. Changing its generation expression is + * harmless, though. + */ + Assert(foundObject.objectSubId == 0); + if (subtype == AT_AlterColumnType) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter type of a column used in a partition key expression"), + errdetail("Partition key of %s depends on column \"%s\".", + getObjectDescription(&foundObject, false), + colName))); + } else { /* Not expecting any other direct dependencies... */ diff --git a/src/test/regress/expected/alter_column_type.out b/src/test/regress/expected/alter_column_type.out index 2388cd0859..6a2c3be37d 100644 --- a/src/test/regress/expected/alter_column_type.out +++ b/src/test/regress/expected/alter_column_type.out @@ -101,8 +101,22 @@ create table atref(b text) partition by range ((('(1)'::attbl).a + length(b))); create table atref_part(b text); alter table atref attach partition atref_part for values from (1) to (2); alter table attbl alter column a type bigint; -- someday this should work -ERROR: unexpected object depending on column: table atref +ERROR: cannot alter type of a column used in a partition key expression +DETAIL: Partition key of table atref depends on column "a". drop table atref, attbl; +-- in PARTITION BY clause, selecting a field of a function's result. The +-- expression doesn't depend on the rowtype, only on the function and the +-- column. +create table attbl(a numeric); +create function atref_func(text) returns attbl immutable language sql + as $$ select row(length($1))::public.attbl $$; +create table atref(b text) partition by range (((atref_func(b)).a)); +alter table attbl alter column a type bigint; -- fails +ERROR: cannot alter type of a column used in a partition key expression +DETAIL: Partition key of table atref depends on column "a". +drop table atref; +drop function atref_func; +drop table attbl; -- in PARTITION BY clause, when the type is the expression's return type, but -- the exprssion doesn't otherwise depend on it CREATE TYPE atcomp AS (a bytea); diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index d7a18e478f..ca6d7a635c 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -1515,7 +1515,6 @@ CREATE TABLE gtest31_2 (b text) PARTITION BY RANGE ((('(1)'::gtest31_1).a + leng ALTER TABLE gtest31_1 ALTER COLUMN a TYPE bigint; ERROR: cannot alter table "gtest31_1" because table gtest31_2 uses its row type ALTER TABLE gtest31_1 ALTER COLUMN a SET EXPRESSION AS (124); -ERROR: unexpected object depending on column: table gtest31_2 DROP TABLE gtest31_1, gtest31_2; -- triggers CREATE TABLE gtest26 ( diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index b14e10fb5a..0cbbac944b 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1409,7 +1409,6 @@ CREATE TABLE gtest31_2 (b text) PARTITION BY RANGE ((('(1)'::gtest31_1).a + leng ALTER TABLE gtest31_1 ALTER COLUMN a TYPE bigint; ERROR: cannot alter table "gtest31_1" because table gtest31_2 uses its row type ALTER TABLE gtest31_1 ALTER COLUMN a SET EXPRESSION AS (124); -ERROR: unexpected object depending on column: table gtest31_2 DROP TABLE gtest31_1, gtest31_2; -- triggers CREATE TABLE gtest26 ( diff --git a/src/test/regress/sql/alter_column_type.sql b/src/test/regress/sql/alter_column_type.sql index d821126a2b..e00cc8a190 100644 --- a/src/test/regress/sql/alter_column_type.sql +++ b/src/test/regress/sql/alter_column_type.sql @@ -92,6 +92,18 @@ alter table atref attach partition atref_part for values from (1) to (2); alter table attbl alter column a type bigint; -- someday this should work drop table atref, attbl; +-- in PARTITION BY clause, selecting a field of a function's result. The +-- expression doesn't depend on the rowtype, only on the function and the +-- column. +create table attbl(a numeric); +create function atref_func(text) returns attbl immutable language sql + as $$ select row(length($1))::public.attbl $$; +create table atref(b text) partition by range (((atref_func(b)).a)); +alter table attbl alter column a type bigint; -- fails +drop table atref; +drop function atref_func; +drop table attbl; + -- in PARTITION BY clause, when the type is the expression's return type, but -- the exprssion doesn't otherwise depend on it CREATE TYPE atcomp AS (a bytea); -- 2.53.0
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
  2026-11-15 04:47 BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-16 22:53 ` Andres Freund <andres@anarazel.de>
  2026-11-16 23:01   ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Robert Haas <robertmhaas@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Andres Freund @ 2026-11-16 22:53 UTC (permalink / raw)
  To: hackerzheng666@gmail.com, pgsql-bugs@lists.postgresql.org, Heikki Linnakangas <hlinnaka@iki.fi>

Hi,

On 2026-11-15 04:47:59 +0000, PG Bug reporting form wrote:
> ALTER COLUMN TYPE on a column of a table that is also used as a composite
> type raises an internal (XX000) error if the partition key expression of
> another table selects a field of that composite type:

> create table attbl(a numeric);
> create table atref(b text) partition by range ((('(1)'::attbl).a +
> length(b)));
> alter table attbl alter column a type bigint;
> ERROR:  unexpected object depending on column: table atref
>
> (Found with a fuzzer; reduced by hand. Checked on current master only. A
> bare partition key (('(1)'::attbl).a) is rejected earlier with "cannot use
> constant expression as partition key", hence the "+ length(b)".)

Maybe I'm just tired, but that's not something anybody actually cares about
right?  I guess we can dress this up to have a nicer error message, but also,
who cares?

I think we want to do something here, it might be better to focus on rejecting
such absurd partition by clauses...

Greetings,

Andres Freund






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
  2026-11-15 04:47 BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key PG Bug reporting form <noreply@postgresql.org>
  2026-11-16 22:53 ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Andres Freund <andres@anarazel.de>
@ 2026-11-16 23:01   ` Robert Haas <robertmhaas@gmail.com>
  2026-11-16 23:57     ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Andres Freund <andres@anarazel.de>
  0 siblings, 1 reply; 5+ messages in thread

From: Robert Haas @ 2026-11-16 23:01 UTC (permalink / raw)
  To: Andres Freund <andres@anarazel.de>; +Cc: hackerzheng666@gmail.com, pgsql-bugs@lists.postgresql.org, Heikki Linnakangas <hlinnaka@iki.fi>

On Mon, Nov 16, 2026 at 5:53 PM Andres Freund <andres@anarazel.de> wrote:
> Maybe I'm just tired, but that's not something anybody actually cares about
> right?  I guess we can dress this up to have a nicer error message, but also,
> who cares?
>
> I think we want to do something here, it might be better to focus on rejecting
> such absurd partition by clauses...

The LLM-generated patch I attached before gives a nice error message
for ALTER COLUMN TYPE, which I agree is nice but not critical, but it
also makes ALTER COLUMN .. SET EXPRESSION succeed instead of failing.
If that case is safe, we probably want to allow it.

-- 
Robert Haas
Databricks






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key
  2026-11-15 04:47 BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key PG Bug reporting form <noreply@postgresql.org>
  2026-11-16 22:53 ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Andres Freund <andres@anarazel.de>
  2026-11-16 23:01   ` Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key Robert Haas <robertmhaas@gmail.com>
@ 2026-11-16 23:57     ` Andres Freund <andres@anarazel.de>
  0 siblings, 0 replies; 5+ messages in thread

From: Andres Freund @ 2026-11-16 23:57 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: hackerzheng666@gmail.com, pgsql-bugs@lists.postgresql.org, Heikki Linnakangas <hlinnaka@iki.fi>

Hi,

On 2026-11-16 18:01:01 -0500, Robert Haas wrote:
> On Mon, Nov 16, 2026 at 5:53 PM Andres Freund <andres@anarazel.de> wrote:
> > Maybe I'm just tired, but that's not something anybody actually cares about
> > right?  I guess we can dress this up to have a nicer error message, but also,
> > who cares?
> >
> > I think we want to do something here, it might be better to focus on rejecting
> > such absurd partition by clauses...
>
> The LLM-generated patch I attached before gives a nice error message
> for ALTER COLUMN TYPE, which I agree is nice but not critical, but it
> also makes ALTER COLUMN .. SET EXPRESSION succeed instead of failing.
> If that case is safe, we probably want to allow it.

I am very hesitant to expand what is allowed around any of this without very
careful analysis. It's not at all hard to imagine that allowing such a change
would add security issues. In this case it also might be breaking
partitioning, afaict such a SET EXPRESSION, were it allowed, could change what
partition a row belongs in, no?

Even if it is correct, I would vote strongly against allowing more in the back
branches.


Generally around all of this allowing more IMO is an anti-goal.  The amount of
useless corner-case crap that we allow has cost me way way too much time over
the last months.

Nobody can tell me that it's useful to have partitioning expressions of this
kind:

-- Check if the rowtype is used in PARTITION BY expression of another table.
CREATE TABLE gtest31_1 (a int GENERATED ALWAYS AS (123) VIRTUAL);
CREATE TABLE gtest31_2 (b text) PARTITION BY RANGE ((('(1)'::gtest31_1).a + length(b)));


Greetings,

Andres






^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2026-11-16 23:57 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-15 04:47 BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key PG Bug reporting form <noreply@postgresql.org>
2026-11-16 20:11 ` Robert Haas <robertmhaas@gmail.com>
2026-11-16 22:53 ` Andres Freund <andres@anarazel.de>
2026-11-16 23:01   ` Robert Haas <robertmhaas@gmail.com>
2026-11-16 23:57     ` Andres Freund <andres@anarazel.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox