public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Paul A Jungwirth <[email protected]>
Cc: Chao Li <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 Application Time Update & Delete
Date: Thu, 9 Apr 2026 12:42:23 -0700
Message-ID: <CAHg+QDcVL2d4ih5zs2Mzh63ts41N+jtnMQTdZ2_0be6aF4aqYQ@mail.gmail.com> (raw)
In-Reply-To: <CAHg+QDd74fnd4obCRMqVS0AVWf=cSFH=Cv7trTJWgm+_bhTK6w@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+renyUazgR-hB_6RY60n23L0y-n_h9G1AappZmPENO0k5pL1g@mail.gmail.com>
<[email protected]>
<CA+renyVXg5pV84wQnGQuK8-=qoKw3BiBgQzesxM_LkcxxWmYjA@mail.gmail.com>
<[email protected]>
<CA+renyWKOj5=rMmQmJcbybu-Vdomxdp=eJ93kp76AgmQKYdfiQ@mail.gmail.com>
<[email protected]>
<CA+renyUhuXB2nTVCMREXew9E4DZOnFxQNjME5bcw91+k72Bosg@mail.gmail.com>
<CA+renyWUCSyTMn3s03kviEN-oaVrJP-QkDQCLNfaY=MHV5QEiQ@mail.gmail.com>
<CA+renyV4tWU2d=n9_v=XNPHbZfNqqLokzd-Xt78M-zLd+46ubA@mail.gmail.com>
<[email protected]>
<CA+renyUSgqXpjj+vV7w+wirPB49VQFrmPjVT_s04JmZSOPNNsQ@mail.gmail.com>
<[email protected]>
<CA+renyX-eV+2hFUaZg3BSREqLE7dh+LoWm7ZqhFAiGsirjjtRQ@mail.gmail.com>
<[email protected]>
<CAHg+QDckLFqthQyox2NDetYRs9sRrjmAiSA-gYRowyg8w_4vgw@mail.gmail.com>
<CAHg+QDd74fnd4obCRMqVS0AVWf=cSFH=Cv7trTJWgm+_bhTK6w@mail.gmail.com>
On Thu, Apr 9, 2026 at 12:35 PM SATYANARAYANA NARLAPURAM <
[email protected]> wrote:
> Hi Paul, Peter,
>
> I found a Server crash when using UPDATE ... FOR PORTION OF or DELETE ...
> FOR PORTION OF on a view that has INSTEAD OF triggers.
>
> Repro:
>
> CREATE TABLE t (id INT, valid_at daterange, val INT);
> INSERT INTO t VALUES (1, '[2026-01-01,2026-12-31)', 100);
> CREATE VIEW v AS SELECT * FROM t;
>
> CREATE FUNCTION v_trig() RETURNS trigger LANGUAGE plpgsql AS $$
> BEGIN
> UPDATE t SET val = NEW.val WHERE id = OLD.id;
> RETURN NEW;
> END;
> $$;
> CREATE TRIGGER trg INSTEAD OF UPDATE ON v
> FOR EACH ROW EXECUTE FUNCTION v_trig();
>
> -- This crashes the server:
> UPDATE v FOR PORTION OF valid_at FROM '2026-04-01' TO '2026-08-01'
> SET val = 999 WHERE id = 1;
>
> I am thinking we should just reject this case. Attached a draft patch to
> fix the issue.
>
Patches attached now.
Attachments:
[application/octet-stream] 0008-test-fpo-crash-instead-of-trigger-views.patch (2.9K, 3-0008-test-fpo-crash-instead-of-trigger-views.patch)
download | inline diff:
diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out
index 31f772c7..b862e5d4 100644
--- a/src/test/regress/expected/for_portion_of.out
+++ b/src/test/regress/expected/for_portion_of.out
@@ -2097,4 +2097,26 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
(4 rows)
DROP TABLE temporal_partitioned;
+-- Test: FOR PORTION OF should be rejected on views with INSTEAD OF triggers
+CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
+INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
+CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
+CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+-- These should produce clean error messages, not crash the server
+UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ SET val = 999 WHERE id = 1; -- error
+ERROR: FOR PORTION OF is not supported on views with INSTEAD OF triggers
+DELETE FROM fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ WHERE id = 1; -- error
+ERROR: FOR PORTION OF is not supported on views with INSTEAD OF triggers
+DROP VIEW fpo_instead_view;
+DROP TABLE fpo_instead_base;
RESET datestyle;
diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql
index d4062acf..77610e5f 100644
--- a/src/test/regress/sql/for_portion_of.sql
+++ b/src/test/regress/sql/for_portion_of.sql
@@ -1365,4 +1365,25 @@ SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at;
DROP TABLE temporal_partitioned;
+-- Test: FOR PORTION OF should be rejected on views with INSTEAD OF triggers
+CREATE TABLE fpo_instead_base (id int, valid_at daterange, val int);
+INSERT INTO fpo_instead_base VALUES (1, '[2024-01-01,2024-12-31)', 100);
+CREATE VIEW fpo_instead_view AS SELECT * FROM fpo_instead_base;
+CREATE FUNCTION fpo_instead_trig_fn() RETURNS trigger LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN NEW;
+END;
+$$;
+CREATE TRIGGER fpo_instead_trig INSTEAD OF UPDATE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+CREATE TRIGGER fpo_instead_del_trig INSTEAD OF DELETE ON fpo_instead_view
+ FOR EACH ROW EXECUTE FUNCTION fpo_instead_trig_fn();
+-- These should produce clean error messages, not crash the server
+UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ SET val = 999 WHERE id = 1; -- error
+DELETE FROM fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO '2024-08-01'
+ WHERE id = 1; -- error
+DROP VIEW fpo_instead_view;
+DROP TABLE fpo_instead_base;
+
RESET datestyle;
[application/octet-stream] 0007-fix-fpo-crash-instead-of-trigger-views.patch (874B, 4-0007-fix-fpo-crash-instead-of-trigger-views.patch)
download | inline diff:
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 021c73f1..9a9cc82c 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1769,6 +1769,18 @@ ApplyRetrieveRule(Query *parsetree,
* Also note that there's a hack in fireRIRrules to avoid calling this
* function again when it arrives at the copied RTE.
*/
+
+ /*
+ * FOR PORTION OF requires access to the physical row to compute temporal leftovers.
+ * Views with INSTEAD OF triggers have no physical storage.
+ */
+ if (parsetree->forPortionOf)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FOR PORTION OF is not supported on views with INSTEAD OF triggers")));
+
if (parsetree->commandType == CMD_INSERT)
return parsetree;
else if (parsetree->commandType == CMD_UPDATE ||
view thread (54+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 Application Time Update & Delete
In-Reply-To: <CAHg+QDcVL2d4ih5zs2Mzh63ts41N+jtnMQTdZ2_0be6aF4aqYQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox