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

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: shihao zhong <zhong950419(at)gmail(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, Manu <manuelreyesbravo(at)gmail(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>, Richard Guo <guofenglinux(at)gmail(dot)com>
Subject: Re: pgsql: Don't trust NOT NULL on virtual generated columns of partitioned
Date: 2026-11-17 19:26:56
Message-ID: CAN4CZFNiAvaeVfXT4Az67wXm21GKm9p0odwFd75DNg0CAJ5aCQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

Thanks for the update!

+-- a child with two parents cannot have its expression changed through one
+CREATE TABLE gtesty (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestz (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
+CREATE TABLE gtestyz () INHERITS (gtesty, gtestz);
+NOTICE: merging multiple inherited definitions of column "a"
+NOTICE: merging multiple inherited definitions of column "b"
+ALTER TABLE gtesty ALTER COLUMN b SET EXPRESSION AS (a * 3); -- error

But it can with a combined alter:

CREATE TABLE q (a int, b int GENERATED ALWAYS AS (nullif(a, 1))
VIRTUAL NOT NULL);
CREATE TABLE p (a int);
CREATE TABLE c () INHERITS (p, q);
ALTER TABLE p ADD COLUMN b int GENERATED ALWAYS AS (nullif(a, 1)) VIRTUAL,
ALTER COLUMN b SET EXPRESSION AS (a); -- accepted

Also, in the above example if we try to alter the child instead we get:

ALTER TABLE gtestyz ALTER COLUMN b SET EXPRESSION AS (a * 3);
-- ERROR: cannot alter inherited column "b"
-- HINT: Alter the generation expression of the top-level parent table instead.

But that hint won't work, as the testcase shows.

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Zsolt Parragi 2026-11-17 19:36:42 Re: CREATE TABLE LIKE with INHERITS skips checks on generated columns
Previous Message Bharath Rupireddy 2026-11-17 19:24:16 Re: Add tests for concurrent DML retry paths in logical replication apply

Browse pgsql-committers by date

  From Date Subject
Previous Message Magnus Hagander 2026-11-17 13:50:29 Re: pgsql: ci: Enable PCH in mingw cross job