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

On 2026-Oct-10, Richard Guo wrote:

> Don't trust NOT NULL on virtual generated columns of partitioned tables
[...]
> That is not safe for a partitioned table. A partition can have a
> generation expression different from its parent's, and it enforces the
> NOT NULL constraint against its own expression, whereas a query on the
> parent computes the column using the parent's expression. So the
> column can be NULL when read through the parent despite the
> constraint, and the anti-join would then produce wrong results, as it
> returns rows that NOT IN should have rejected.

I find this very surprising. Are we sure that it makes sense for a
partition to have a generating expression that differs from the one in
the parent table?

Copying from the test case you added,

CREATE TABLE gtestnn_parent2 (a int NOT NULL, b int GENERATED ALWAYS AS (nullif(a, 0)) VIRTUAL NOT NULL) PARTITION BY LIST (a);
CREATE TABLE gtestnn_child2 PARTITION OF gtestnn_parent2 (b GENERATED ALWAYS AS (a + 1) VIRTUAL) FOR VALUES IN (0);
INSERT INTO gtestnn_parent2 VALUES (0);

we read a NULL value from a column that's marked as not-null, which
shouldn't happen.

alvherre=# select * from gtestnn_parent2 ;
a | b
---+--------
0 | (null)
(1 row)

alvherre=# \d gtestnn_parent2
Partitioned table "public.gtestnn_parent2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | not null |
b | integer | | not null | generated always as (NULLIF(a, 0))
Partition key: LIST (a)
Number of partitions: 1 (Use \d+ to list them.)

The row is of course different if I read it directly from the partition.

alvherre=# select * from gtestnn_child2 ;
a | b
---+---
0 | 1
(1 row)

I didn't try but I imagine that allowing partitions to have a different
generating expression than the parent can break all kinds of constraint,
not just not-null ones, if generating expressions are "broken" enough.
Maybe we should disallow expressions to be different, and force users to
use STORED virtual columns if they want to do that. This seems
dangerous.

Alternatively, you could argue that at read time the row should get its
virtual column values using the expressions from the children rather
than for the parent.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Las navajas y los monos deben estar siempre distantes" (Germán Poo)