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

Hi Álvaro,

> This is where I disagree ... IMO this is serious problem whose fix
> should be back-patched.

Fair enough, I agree. Here is a patch for master and 19, and a version
for 18.

With the patch, a virtual generated column of a partition must use the
same expression as the parent. CREATE TABLE PARTITION OF and ATTACH
PARTITION check this, and SET EXPRESSION can only change the whole
partition tree. Stored columns and plain inheritance are unchanged.

On why it works this way today. Before 8bf6ec3ba3a, a child had to use
the parent's expression. That commit relaxed the rule when stored
columns were the only kind, and for them it is fine. Virtual columns
kept the relaxed rule, but they are computed with the expression of the
table named in the query, so the old rule fits them better.

> provide SQL scripts that users can run to find and
> remove those that already exist there.

For existing databases, the attached query lists the partition columns
that differ, and pg_upgrade on master and 19 runs the same check. Users
can fix them with SET EXPRESSION on the parent, unless existing rows
break NOT NULL under the parent's expression.

For 18 there is one cost. A dump of a database that already has such
partitions will fail to attach them when restored into a patched 18.
So the release notes would need to ask users to run the query first.

Thanks,
Shihao