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

Hi all,

On Tue, Jul 28, 2026 at 5:35 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> 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.

I reviewed and tested this patch. I was able to reproduce the reported
issue before applying the patch and confirmed that DELETE FOR PORTION
OF on a view with WITH CHECK OPTION incorrectly allowed temporal
leftover rows to bypass the check, whereas the equivalent UPDATE FOR
PORTION OF correctly raised a WITH CHECK OPTION violation. After
applying the patch, DELETE FOR PORTION OF now correctly raises a WITH
CHECK OPTION error for invalid leftover rows, and the statement is
rolled back, leaving the base table unchanged.
I also verified that
1. The valid DELETE FOR PORTION OF operations, where the generated
leftovers continue to satisfy the view condition, still succeed.
2. Ordinary DELETE behavior is unchanged and consistent.
3. UPDATE FOR PORTION OF continues to behave as before, indicating no
regression.
4. Multi-row operations remain atomic as if any generated leftover
violates the WITH CHECK OPTION, the entire statement is rolled back.

I also reviewed the changes in rewriteHandler.c. The implementation
regarding extending the existing WITH CHECK OPTION handling to DELETE
FOR PORTION OF during query rewriting seems correct and reuses the
existing infrastructure without introducing special-case executor
logic. I did not notice any correctness issues during testing.
Overall, the implementation looks good to me.

Regards,
Solai