| 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 |
| 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 |