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-03dK for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:31 +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-1pmc for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:50704) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJSk-00000002tTG-0xkq for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:18:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJSf-00000001XeR-2mKK for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:18:09 +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=K8ZIgjETGBkVUKJqqm1ABHhjmdCWC8zzq94H0KoxIXw=; b=hKSlmvdUO4k2BftUHhmLU7fCb/ 20LNPU2OAqzFesJuDX+063gXLQgTs51a0D6AmO/O+zHuA8OND61VAeqCLhhM2E/ArAdUSWYejJSAz TbSOEZTb97RV3mvbZfMuq2ZizTiZ7iWxi9dYLRWPIA0VIIhjGQ1F6EOWPj126Ltioig2K422rqwCK OHCGLCnk+oQGBPn8932xBbwT9kmIwZLfAvqhBzV5wLiTUSShBPY7x4ffRuKoaoab5WkmYpq/gt76O az6jlPgisxYdELux76bUod50ldgRASVsDcqiyjzqokv5GBCzDJQlPz3VYVHMiPPqBJlX2gZ23XcFG PQGol5lQ==; 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 1xMJSe-00000004HGW-20YU for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:18:04 +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 1xMJSc-00000005anC-2CWb for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:18:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu 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:17:19 +0000 Message-ID: <19807-9d1d34b611597835@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: 19807 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 Description: =20 A CHECK constraint declared on a partitioned table is enforced at INSERT time using the *partition's* version of a virtual generated column when the partition overrides that column's expression with WITH OPTIONS. Queries through the parent table, however, evaluate the column with the parent's expression. As a result, a row that violates the parent's CHECK constraint is accepted on INSERT and remains visible through the parent. Test case (self-contained): CREATE TABLE gp (f2 int, f3 int GENERATED ALWAYS AS (f2 * 2) VIRTUAL, CONSTRAINT gp_ck CHECK (f3 > 30)) PARTITION BY RANGE (f2); CREATE TABLE gp1 PARTITION OF gp FOR VALUES FROM (0) TO (8); CREATE TABLE gp2 PARTITION OF gp (f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) VIRTUAL) FOR VALUES FROM (8) TO (100); INSERT INTO gp (f2) VALUES (10); -- accepted Result on 18.6 (official docker image postgres:latest): SELECT f2, f3 FROM gp; -> (10, 20) parent evaluates f3 =3D f2*2 SELECT f2, f3 FROM gp WHERE NOT (f3 > 30); -> (10, 20) a row visible through the parent violates the parent's own CHECK SELECT f2, f3 FROM gp2; -> (10, 220) the partition evaluated f3 =3D f2*22 Baselines - the same constraint rejects the same INSERT as soon as no expression override is involved: (1) plain table, same CHECK: ERROR: new row for relation "plain_t" violates check constraint "plain_ck" (2) partitioned table without the WITH OPTIONS override: ERROR: new row for relation "gb2" violates check constraint "gb_ck" (3) partitioned table with the override: INSERT 0 1 Expected: the INSERT should be rejected, as in both baselines - or the combination of a parent CHECK referencing a generated column together with a partition expression override should be rejected at DDL time. A CHECK constraint defined on the parent must hold for every row visible through the parent. Mechanism: constraint checking runs on the leaf partition using the leaf's generated expression (f2*22 =3D 220 > 30 passes), while reads through the parent compute the column with the parent's expression (f2*2 =3D 20). The t= wo evaluations disagree, so the constraint the user declared is never applied to the value the user sees. Related, same parent/leaf evaluation mismatch: a row-level trigger declared on the parent with WHEN (OLD.f3 > 30) also fires based on the leaf expression, i.e. for rows whose parent-visible value is 20. Notes: - The WITH OPTIONS clause is the documented syntax for overriding a generated column's expression in a partition, so this combination is reachable by ordinary DDL.