postgres.git / summary / log / commit / refs
commit f3a116a26ff0c0be83b990d3d9b3fd9e425bb197
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Fri Aug 14 09:43:19 2026 +0000
Enforce WITH CHECK OPTION on DELETE FOR PORTION OF leftovers
DELETE FOR PORTION OF inserts temporal leftovers through ExecInsert(),
but the rewriter only attached view WCOs for INSERT/UPDATE. Leftover
rows could therefore silently escape a WITH CHECK OPTION view, while
the equivalent UPDATE correctly raised an error.
This commit fixes it by attaching the WCOs for FOR PORTION OF deletes
too.
Author: Zsolt Parragi <zsolt.parragi@percona.com>
Co-authored-by: Paul A Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: solai v <solai.cdac@gmail.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CAN4CZFOuTyhGspG0Nyits8PiK2keoNXkLj-u3APzc66aRcWY9A%40mail.gmail.com
doc/src/sgml/ref/create_view.sgml | 20 ++++++----
src/backend/rewrite/rewriteHandler.c | 8 +++-
src/test/regress/expected/updatable_views.out | 56 +++++++++++++++++++++++++++
src/test/regress/sql/updatable_views.sql | 29 ++++++++++++++
4 files changed, 104 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml
index 60215eba3b8..2c08f7e3cff 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -197,11 +197,12 @@ CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</repl
commands on the view will be checked to ensure that new rows satisfy the
view-defining condition (that is, the new rows are checked to ensure that
they are visible through the view). If they are not, the update will be
- rejected. If the <literal>CHECK OPTION</literal> is not specified,
- <command>INSERT</command>, <command>UPDATE</command>, and
- <command>MERGE</command> commands on the view are
- allowed to create rows that are not visible through the view. The
- following check options are supported:
+ rejected. The <glossterm linkend="glossary-temporal-leftovers">temporal
+ leftovers</glossterm> inserted by an <command>UPDATE</command> or
+ <command>DELETE</command> with a <literal>FOR PORTION OF</literal> clause
+ are checked in the same way. If the <literal>CHECK OPTION</literal> is
+ not specified, these commands are allowed to create rows that are not
+ visible through the view. The following check options are supported:
<variablelist>
<varlistentry>
@@ -431,10 +432,13 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
potentially insert base-relation rows
that do not satisfy the <literal>WHERE</literal> condition and thus are not
visible through the view (<literal>ON CONFLICT DO SELECT/UPDATE</literal> may
- similarly affect an existing row not visible through the view).
+ similarly affect an existing row not visible through the view). An
+ <command>UPDATE</command> or <command>DELETE</command> with a
+ <literal>FOR PORTION OF</literal> clause can do so as well, since the
+ temporal leftovers it inserts may fall outside the
+ <literal>WHERE</literal> condition.
The <literal>CHECK OPTION</literal> may be used to prevent
- <command>INSERT</command>, <command>UPDATE</command>, and
- <command>MERGE</command> commands from creating such rows that are not
+ these commands from creating such rows that are not
visible through the view.
</para>
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 38f54b57eec..3e43418e996 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3953,8 +3953,14 @@ rewriteTargetView(Query *parsetree, Relation view)
* the WITH CHECK OPTION, or any parent view specified WITH CASCADED CHECK
* OPTION, add the quals from the view to the query's withCheckOptions
* list.
+ *
+ * DELETE FOR PORTION OF needs this too: it inserts temporal leftovers to
+ * preserve the untouched parts of the deleted row, and those must not
+ * escape the view either. For UPDATE, any WCO we add below will apply to
+ * inserted leftovers as well.
*/
- if (insert_or_update)
+ if (insert_or_update ||
+ (parsetree->commandType == CMD_DELETE && parsetree->forPortionOf != NULL))
{
bool has_wco = RelationHasCheckOption(view);
bool cascaded = RelationHasCascadedCheckOption(view);
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 9c6bb2219f9..b4b4e93a7dd 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3768,6 +3768,62 @@ delete from uv_fpo_view_nonupd for portion of valid_at from 1 to 10;
ERROR: cannot delete from view "uv_fpo_view_nonupd" using FOR PORTION OF "valid_at"
DETAIL: View columns that are not columns of their base relation are not updatable.
drop view uv_fpo_view_nonupd;
+-- WITH CHECK OPTION must be enforced on temporal leftovers, i.e. the rows
+-- FOR PORTION OF inserts to preserve the untouched parts of the target row.
+-- This applies to DELETE as well as UPDATE.
+create table uv_fpo_wco_tab (id int4range, valid_at daterange, b int);
+insert into uv_fpo_wco_tab values ('[1,1]', '[2020-01-01,2030-01-01)', 0);
+create view uv_fpo_wco_view as
+ select * from uv_fpo_wco_tab
+ where valid_at && daterange('2024-01-01', '2025-01-01')
+ with check option;
+-- The leftovers fall outside the view, so both commands fail:
+update uv_fpo_wco_view for portion of valid_at from '2024-01-01' to '2025-01-01' set b = 1;
+ERROR: new row violates check option for view "uv_fpo_wco_view"
+DETAIL: Failing row contains ([1,2), [01-01-2020,01-01-2024), 0).
+delete from uv_fpo_wco_view for portion of valid_at from '2024-01-01' to '2025-01-01';
+ERROR: new row violates check option for view "uv_fpo_wco_view"
+DETAIL: Failing row contains ([1,2), [01-01-2020,01-01-2024), 0).
+-- The base table is unchanged:
+select * from uv_fpo_wco_tab order by valid_at;
+ id | valid_at | b
+-------+-------------------------+---
+ [1,2) | [01-01-2020,01-01-2030) | 0
+(1 row)
+
+-- Leftovers that still satisfy the view are allowed:
+delete from uv_fpo_wco_view for portion of valid_at from '2024-03-01' to '2024-06-01';
+update uv_fpo_wco_view for portion of valid_at from '2024-07-01' to '2024-08-01' set b = 1;
+select * from uv_fpo_wco_tab order by valid_at;
+ id | valid_at | b
+-------+-------------------------+---
+ [1,2) | [01-01-2020,03-01-2024) | 0
+ [1,2) | [06-01-2024,07-01-2024) | 0
+ [1,2) | [07-01-2024,08-01-2024) | 1
+ [1,2) | [08-01-2024,01-01-2030) | 0
+(4 rows)
+
+-- Without WITH CHECK OPTION the leftovers may leave the view:
+create view uv_fpo_nowco_view as
+ select * from uv_fpo_wco_tab
+ where valid_at && daterange('2024-01-01', '2025-01-01');
+delete from uv_fpo_nowco_view for portion of valid_at from '2024-01-01' to '2024-02-01';
+update uv_fpo_nowco_view for portion of valid_at from '2024-09-01' to '2026-01-01' set b = 2;
+select * from uv_fpo_wco_tab order by valid_at;
+ id | valid_at | b
+-------+-------------------------+---
+ [1,2) | [01-01-2020,01-01-2024) | 0
+ [1,2) | [02-01-2024,03-01-2024) | 0
+ [1,2) | [06-01-2024,07-01-2024) | 0
+ [1,2) | [07-01-2024,08-01-2024) | 1
+ [1,2) | [08-01-2024,09-01-2024) | 0
+ [1,2) | [09-01-2024,01-01-2026) | 2
+ [1,2) | [01-01-2026,01-01-2030) | 0
+(7 rows)
+
+drop view uv_fpo_wco_view;
+drop view uv_fpo_nowco_view;
+drop table uv_fpo_wco_tab;
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index 2ef9aa32f36..3ddb7b43cc8 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1914,6 +1914,35 @@ update uv_fpo_view_nonupd for portion of valid_at from 1 to 10 set b = 2;
delete from uv_fpo_view_nonupd for portion of valid_at from 1 to 10;
drop view uv_fpo_view_nonupd;
+-- WITH CHECK OPTION must be enforced on temporal leftovers, i.e. the rows
+-- FOR PORTION OF inserts to preserve the untouched parts of the target row.
+-- This applies to DELETE as well as UPDATE.
+create table uv_fpo_wco_tab (id int4range, valid_at daterange, b int);
+insert into uv_fpo_wco_tab values ('[1,1]', '[2020-01-01,2030-01-01)', 0);
+create view uv_fpo_wco_view as
+ select * from uv_fpo_wco_tab
+ where valid_at && daterange('2024-01-01', '2025-01-01')
+ with check option;
+-- The leftovers fall outside the view, so both commands fail:
+update uv_fpo_wco_view for portion of valid_at from '2024-01-01' to '2025-01-01' set b = 1;
+delete from uv_fpo_wco_view for portion of valid_at from '2024-01-01' to '2025-01-01';
+-- The base table is unchanged:
+select * from uv_fpo_wco_tab order by valid_at;
+-- Leftovers that still satisfy the view are allowed:
+delete from uv_fpo_wco_view for portion of valid_at from '2024-03-01' to '2024-06-01';
+update uv_fpo_wco_view for portion of valid_at from '2024-07-01' to '2024-08-01' set b = 1;
+select * from uv_fpo_wco_tab order by valid_at;
+-- Without WITH CHECK OPTION the leftovers may leave the view:
+create view uv_fpo_nowco_view as
+ select * from uv_fpo_wco_tab
+ where valid_at && daterange('2024-01-01', '2025-01-01');
+delete from uv_fpo_nowco_view for portion of valid_at from '2024-01-01' to '2024-02-01';
+update uv_fpo_nowco_view for portion of valid_at from '2024-09-01' to '2026-01-01' set b = 2;
+select * from uv_fpo_wco_tab order by valid_at;
+drop view uv_fpo_wco_view;
+drop view uv_fpo_nowco_view;
+drop table uv_fpo_wco_tab;
+
-- Test whole-row references to the view
create table uv_iocu_tab (a int unique, b text);
create view uv_iocu_view as
[parent: 48965e14655a]