| From: | Manu <manuelreyesbravo(at)gmail(dot)com> |
|---|---|
| To: | shihao zhong <zhong950419(at)gmail(dot)com> |
| Cc: | Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>, Richard Guo <guofenglinux(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
| Subject: | Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned |
| Date: | 2026-11-19 19:08:57 |
| Message-ID: | 179511533762.1055718.4280891924320654081@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-committers pgsql-hackers |
Hi Shihao,
On Thu, Nov 19, 2026 at 12:01 AM shihao zhong <zhong950419(at)gmail(dot)com>
wrote:
> Thanks for catching both, v6 attached.
v6 passes everything we had, on master (b443bc7406f), REL_19_STABLE
(b1bc8f8ea5d) and, in its 18 version, REL_18_STABLE (ecfe3a44dab).
Two new cases, both on trees that an unpatched 18 or 19 left
divergent, after the minor update.
1. A child with two parents. The pg_upgrade text says to run SET
EXPRESSION on the top-level parent, which works on unpatched 18 but
not with the patch:
-- unpatched 18
CREATE TABLE ip (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL);
CREATE TABLE iq (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL);
CREATE TABLE ic (v int GENERATED ALWAYS AS (x * 3) VIRTUAL)
INHERITS (ip, iq);
-- 18 with the patch; the same on 19 with the master patch
ALTER TABLE ip ALTER COLUMN v SET EXPRESSION AS (x * 2);
ERROR: cannot alter inherited column "v" of relation "ic"
The same for iq. If the parents' expressions differ (x * 2 and
x * 3, the child giving its own, as 18's ddl.sgml asks), the text
does not work on any branch, unpatched included. What works on all
of them is NO INHERIT from all parents but one, SET EXPRESSION, and
INHERIT again. Maybe, after "add them back afterwards.":
A child table with more than one parent must first be removed from
all but one with NO INHERIT, and added back once all its parents
have the same expression.
2. With the patch, adding a virtual column to the parent of such a
tree fails, naming the old column:
-- unpatched 18
CREATE TABLE ta (x int, v int GENERATED ALWAYS AS (x * 2) VIRTUAL)
PARTITION BY LIST (x);
CREATE TABLE ta1 PARTITION OF ta
(v GENERATED ALWAYS AS (x * 3) VIRTUAL) FOR VALUES IN (1);
-- 18 with the patch
ALTER TABLE ta ADD COLUMN w int GENERATED ALWAYS AS (x + 1) VIRTUAL;
ERROR: column "v" of relation "ta1" must have the same generation
expression as in parent relation "ta"
The check after the recursion in ATExecAddColumn (tablecmds.c:8347
on master, 8285 on 18) compares every virtual column of the parent,
but only the one being added is new to the child; ATTACH and INHERIT
are the ones that bring a whole new parent. Maybe compare only that
column there?
Regards,
Manu
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Haas | 2026-11-19 20:35:49 | Re: pgsql: ci: Enable PCH in mingw cross job |
| Previous Message | Tomas Vondra | 2026-11-19 18:24:25 | pgsql: Forget the prefetcher's current record in XLogPrefetcherBeginRea |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Manu | 2026-11-19 19:17:07 | Re: Use an index scan in RemoveSubscriptionRel() |
| Previous Message | Masahiko Sawada | 2026-11-19 18:46:04 | Re: Use an index scan in RemoveSubscriptionRel() |