Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgsh-000000029Tv-1qXd for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:32 +0000 Received: from localhost ([127.0.0.1]:51180 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJl-227N for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:60950) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJvk-000000034o5-1K8k for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:48:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJvi-00000001UNs-1xwp for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:48:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=F/xX3SKEke8TM3RI9dRNlyMmQ8KcXttLtnNJ3K9oBUo=; b=j3eyWeeJrexsnHSstpMuZcXyq3 DnrbIAtQiRXHY9peqLQwA08N0YOEsfN9/uECAmaH1IxH5EL44U4+y4w9yAhw25NAYnQ7gT/z5rj8E Lgsi8qg60BixUEn5TH3ivKl0hiMkr+wm47673V818YRiys/AHqkHw41vOXZis9uQE8ZFc4YDl858C duAVnaPLEPRFRfQsBTbWGr//GFjpdN4ULdJUkf6wEbwIxZ340EgDPTZPCOL1/lBYJn8TMvp1IAy7P kH89Mqhnz26f/r1OEhhEtDEPZ2HaeWadNXi9cggJeHEOsuGVrnH7sIXm4MKotfGObUZHXpyiAfK2x VjZnR+nA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJvh-00000004HvX-1KWX for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:48:06 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMJve-00000005cZp-46Xi for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:48:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19812: Multiple wrong-result and data-integrity issues with virtual generated columns (v18) To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 29 Oct 2026 06:47:10 +0000 Message-ID: <19812-32c98d4a667ac18f@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19812 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 Description: =20 Virtual generated columns (new in v18) have several gaps where a code path that does not expand the generation expression accepts a state it should reject. All scenarios below reproduce on the official postgres:latest docker image (18.6); each is self-contained. 1. INSERT/UPDATE accept values violating the column's typmod CREATE TABLE b1_v (id int, a text, c varchar(3) GENERATED ALWAYS AS (a) VIRTUAL); INSERT INTO b1_v VALUES (1, 'abcdef'); -- accepted SELECT * FROM b1_v; -- ERROR: value too long for type character varying(3) The same definition with STORED, or a virtual column with NOT NULL or referenced by a CHECK, is rejected at INSERT time. The bare virtual column is only evaluated at read time, so the row is stored and every subsequent read fails (same for smallint range overflow; UPDATE and INSERT ... ON CONFLICT DO UPDATE paths likewise). 2. Triggers see NULL instead of the computed value CREATE TABLE tv2 (a int, b int GENERATED ALWAYS AS (a*2) VIRTUAL, c int GENERATED ALWAYS AS (a*3) STORED, d int); An AFTER INSERT FOR EACH ROW trigger sees NEW.b =3D NULL while NEW.c =3D 15 (STORED column correct); a statement-level trigger with REFERENCING NEW TABLE sees NULL for b in the transition table; an ordinary SELECT returns b =3D 10. A transition-table WHERE clause referencing the virtual column rais= es XX000 "unexpected virtual generated column reference". 3. DROP EXPRESSION crosses inheritance boundaries it must not cross CREATE TABLE bh_p1 (a int, b int GENERATED ALWAYS AS (a + 1) STORED); CREATE TABLE bh_p2 (a int, b int GENERATED ALWAYS AS (a + 2) STORED); CREATE TABLE bh_leaf (a int, b int GENERATED ALWAYS AS (a + 3) STORED) INHERITS (bh_p1, bh_p2); ALTER TABLE bh_p1 ALTER COLUMN b DROP EXPRESSION; INSERT INTO bh_leaf (a) VALUES (10); SELECT a, b FROM bh_p2; -- (10, NULL) After the ALTER, bh_leaf.b is no longer a generated column even though bh_p2.b still is, so inserts through bh_p2 silently read b =3D NULL. PostgreSQL refuses to create this state directly (ALTER TABLE ... INHERIT of a plain column under a generated parent errors), and sibling operations (RENAME, ALTER COLUMN TYPE) correctly refuse to cross the multi-parent boundary. A later DROP EXPRESSION on bh_p2 now fails because the child is no longer generated. 4. ALTER TABLE ONLY ... SET EXPRESSION skips NOT NULL validation CREATE TABLE vnp (a int, v int GENERATED ALWAYS AS (a) VIRTUAL NOT NULL) PARTITION BY RANGE (a); CREATE TABLE vnp1 PARTITION OF vnp FOR VALUES FROM (0) TO (100); INSERT INTO vnp VALUES (1); ALTER TABLE vnp ALTER COLUMN v SET EXPRESSION AS (nullif(a, 1)); -- ERROR: contains null values (correct) ALTER TABLE ONLY vnp ALTER COLUMN v SET EXPRESSION AS (nullif(a, 1)); -- accepted SELECT a, v FROM vnp; -- (1, NULL), while the column still has attnotnull =3D true Virtual columns are computed using the expression of the table referenced in the query, so rows stored in partitions now read as NULL through the parent. The ONLY variant validates only rows stored in the parent itself (there are none), so the stored rows escape the check. 5. pg_dump silently loses a child's own generation expression CREATE TABLE ep (a int, g int GENERATED ALWAYS AS (a + 1) STORED); CREATE TABLE ec () INHERITS (ep); ALTER TABLE ONLY ec ALTER COLUMN g SET EXPRESSION AS (a * 100); pg_dump -s emits: CREATE TABLE ec ( ) INHERITS (ep); with no SET EXPRESSION - the child's column is not attislocal, shouldPrintColumn() skips it, and unlike DEFAULT columns there is no patch-up ALTER statement. After restore, ec.g computes (a + 1) instead of (a * 100), silently changing values. --binary-upgrade mode dumps the expression correctly, which locates the gap in shouldPrintColumn(). A child expression differing from the parent is explicitly allowed by the documentation. 6. Index DDL accepts expressions reaching a virtual column via a whole-row reference CREATE TABLE t2 (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); CREATE FUNCTION t2_b(r t2) RETURNS int LANGUAGE sql IMMUTABLE AS $$ SELECT $1.b $$; CREATE INDEX ON t2 ((t2_b(t2))); -- accepted INSERT INTO t2 VALUES (1); -- ERROR: unexpected virtual generated column reference A direct reference (CREATE INDEX ON t2 ((b + 1))) is correctly rejected with "indexes on virtual generated columns are not supported"; the whole-row form slips past the check. Partial-index predicates behave the same. STORED columns are unaffected. Related, same whole-row-index theme, no virtual column involved: 7. Whole-row index expressions are not invalidated by ADD/DROP COLUMN CREATE TABLE b6b (a int); CREATE FUNCTION b6b_t(r b6b) RETURNS text LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN $1::text; END $$; CREATE UNIQUE INDEX b6bi ON b6b ((b6b_t(b6b))); INSERT INTO b6b VALUES (1); ALTER TABLE b6b ADD COLUMN c int; INSERT INTO b6b VALUES (1); -- accepted, although both rows now have whole-row value "(1,)" REINDEX INDEX b6bi; -- ERROR: could not create unique index ... Key ... is duplicated The unique constraint is not enforced for the new row shape and the index is provably corrupt; DROP COLUMN is symmetric. Note: the partition-related facet of this family (a parent CHECK validated against a partition's overridden generation expression) was reported separately earlier today.