DELETE FOR PORTION OF bypasses view WITH CHECK OPTION for leftover rows
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253170psql -h localhost -U postgresBuilt from patchset v4 (message #4), August 13, 2026 at 11:32 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253170_4 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253170_4 && git checkout t253170_4Patchset v4 (message #4) is on t253170_4
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.
Hi all,
On Tue, Jul 28, 2026 at 5:35 PM Zsolt Parragi <zsolt.parragi@percona.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
On Wed, Jul 22, 2026 at 2:43 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
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.
[RMT hat]
It's been a few weeks since this was reported, and there hasn't been
engagement by the feature authors. Is this on your radar?
I added an open item [1]https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items.
- Melanie
[1]: https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
On Wed, Aug 12, 2026 at 2:30 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
On Wed, Jul 22, 2026 at 2:43 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
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.[RMT hat]
It's been a few weeks since this was reported, and there hasn't been
engagement by the feature authors. Is this on your radar?
I added an open item [1].
Thanks Melanie! I'm sorry I missed this before.
I can reproduce the bug. The fix looks good to me.
Here are some slight revisions: I added doc updates and a couple extra
regress lines (verifying the same cases, but for UPDATE too). And I
expanded the comment to explain why this is needed for DELETE but not
UPDATE, since that wasn't obvious to me right away.
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
On Thu, 13 Aug 2026, 00:19 Paul A Jungwirth, <pj@illuminatedcomputing.com>
wrote:
On Wed, Aug 12, 2026 at 2:30 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:On Wed, Jul 22, 2026 at 2:43 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:
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.[RMT hat]
It's been a few weeks since this was reported, and there hasn't been
engagement by the feature authors. Is this on your radar?
I added an open item [1].Thanks Melanie! I'm sorry I missed this before.
I can reproduce the bug. The fix looks good to me.
Here are some slight revisions: I added doc updates and a couple extra
regress lines (verifying the same cases, but for UPDATE too). And I
expanded the comment to explain why this is needed for DELETE but not
UPDATE, since that wasn't obvious to me right away.
Seems reasonable to me, after a quick read through.
Regards,
Dean
On 13.08.26 11:23, Dean Rasheed wrote:
On Thu, 13 Aug 2026, 00:19 Paul A Jungwirth,
<pj@illuminatedcomputing.com <mailto:pj@illuminatedcomputing.com>> wrote:On Wed, Aug 12, 2026 at 2:30 PM Melanie Plageman
<melanieplageman@gmail.com <mailto:melanieplageman@gmail.com>> wrote:On Wed, Jul 22, 2026 at 2:43 PM Zsolt Parragi
<zsolt.parragi@percona.com <mailto:zsolt.parragi@percona.com>> wrote:
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.[RMT hat]
It's been a few weeks since this was reported, and there hasn't been
engagement by the feature authors. Is this on your radar?
I added an open item [1].Thanks Melanie! I'm sorry I missed this before.
I can reproduce the bug. The fix looks good to me.
Here are some slight revisions: I added doc updates and a couple extra
regress lines (verifying the same cases, but for UPDATE too). And I
expanded the comment to explain why this is needed for DELETE but not
UPDATE, since that wasn't obvious to me right away.Seems reasonable to me, after a quick read through.
Committed, thanks.