Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned

Hi Shihao,

On Wed, Nov 11, 2026 at 11:36 PM shihao zhong <zhong950419(at)gmail(dot)com>
wrote:
> Thanks for both rewrites. v3 follows your suggestion for doc and comment.

v3 works on master (77bba5454f9), REL_19_STABLE (dfffe8da630) and, in
its 18 version, REL_18_STABLE (2ea5222db7c): the 13 divergent
statements of my Oct 26 paths.sql all fail, the controls pass, and so
do make check and, on master and 19, the pg_upgrade TAP tests. The
new pg_upgrade text falls short in two cases on 18.

On Mon, Oct 26, 2026 at 11:10 PM shihao zhong <zhong950419(at)gmail(dot)com>
wrote:
> I don't think the logic is wrong here, but maybe we can make some
> improvement to the error message. v2 changes
>
> on the partitioned table of each column listed in the file
> =>
> on the partitioned table of each column. In version 18 this is not
> allowed on a table with CHECK constraints, so drop them first and
> add them back afterwards. A list of the problem columns is in the
> file

Sorry for not answering this sooner. On REL_18_STABLE, with and
without the patch, I did only what the text says, and with the CHECK
on the partitioned table pg_upgrade --check to master and 19 then
passes. It does not when:

- the CHECK is only on the partition: SET EXPRESSION on the parent
fails ("not supported for virtual generated columns in tables with
check constraints"), and the parent has no CHECK to drop;
- the divergent partition is a leaf under a partitioned partition:
with the 18 patch, SET EXPRESSION on its parent (the middle table)
fails ("cannot alter ... of partition"); its hint, also on master,
says to alter the partitioned table, which it is; the root works.

Rows that break NOT NULL or a CHECK under the parent's expression
stop it too, with clear errors. Maybe:

on the top-level partitioned table of each column. In version 18
this is not allowed if any table in that partition tree has CHECK
constraints, so drop them first and add them back afterwards. Rows
that violate NOT NULL or CHECK with the new expression must be fixed
first. A list of the problem columns is in the file:

>From unpatched REL_19_STABLE the check fires too, and SET EXPRESSION
on the parent is enough. The script and its output are attached.

Regards,
Manu