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
nocfbot-find-mismatched-virtual-exprs.sql
Description: Binary data
v1-0001-Require-partitions-to-use-the-parent-s-virtual-ge.patch
Description: Binary data
nocfbot-v1-REL_18-0001-Require-partitions-to-use-the-parent-s-virtual-gen.patch
Description: Binary data
