pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu
Date: Thu, 29 Oct 2026 06:17:19 +0000
Message-ID: <19807-9d1d34b611597835@postgresql.org> (raw)

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:        

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
= 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 = 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 = 220 > 30 passes), while reads through the
parent compute the column with the parent's expression (f2*2 = 20). The two
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.








view thread (3+ messages)  latest in thread

Message-ID: <19807-9d1d34b611597835@postgresql.org>
Permalink:  ../19807-9d1d34b611597835@postgresql.org/
Also on:    postgresql.org/message-id/19807-9d1d34b611597835@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19807: CHECK constraint not enforced on partitioned table when partition overrides a virtual generated colu
  In-Reply-To: <19807-9d1d34b611597835@postgresql.org>
  References: <19807-9d1d34b611597835@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox