DELETE FOR PORTION OF bypasses view WITH CHECK OPTION for leftover rows

From: Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: DELETE FOR PORTION OF bypasses view WITH CHECK OPTION for leftover rows
Date: 2026-07-22 18:43:28
Message-ID: CAN4CZFOuTyhGspG0Nyits8PiK2keoNXkLj-u3APzc66aRcWY9A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

While testing FOR PORTION OF I noticed that DELETE FOR PORTION OF on a
view ignores the view's WITH CHECK OPTION for the temporal leftover
rows it inserts.

A minimal example:

create table t (id int, valid_at daterange, val text);
insert into t values (1, '[2020-01-01,2030-01-01)', 'a');
create view v as select * from t
where valid_at && daterange('2024-01-01', '2025-01-01')
with check option;

delete from v for portion of valid_at from '2022-01-01' to '2026-01-01';

The DELETE succeeds, and the 2 leftover rows it inserts
([2020-01-01,2022-01-01) and [2026-01-01,2030-01-01)) are invisible in
the view. The equivalent UPDATE fails:

update v for portion of valid_at from '2022-01-01' to '2026-01-01' set
val = 'b';
ERROR: new row violates check option for view "v"
DETAIL: Failing row contains (1, [2020-01-01,2022-01-01), a).

The attached patch fixes the DELETE behavior, and adds regression tests.

Attachment Content-Type Size
0001-Enforce-WITH-CHECK-OPTION-on-DELETE-FOR-PORTION-OF-l.patch application/octet-stream 6.4 KB

Browse pgsql-bugs by date

  From Date Subject
Previous Message Tom Lane 2026-07-22 11:34:01 Re: BUG #19567: Redundant outer DISTINCT causes a second full aggregation above UNION