On Mon, Apr 20, 2026 at 11:25 PM jian he <[email protected]> wrote: > > On Thu, Apr 16, 2026 at 7:26 AM Paul A Jungwirth > <[email protected]> wrote: > > > > I think using INSTEAD OF triggers to replace an UPDATE/DELETE FOR > > PORTION OF is a valid use-case, but it doesn't make sense to insert > > temporal leftovers. As you say, we can't access the underlying > > storage. But also we don't know what changes the trigger actually > > made. The trigger should be responsible for leftovers, and we > > shouldn't try to add more. So I think the fix is just to skip > > inserting leftovers. I've attached a patch to do that. > > > hi. > > 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(); > > UPDATE fpo_instead_view FOR PORTION OF valid_at FROM '2024-04-01' TO > '2024-08-01' > SET val = 999 WHERE id = 1 > RETURNING *; > > id | valid_at | val > ----+-------------------------+----- > 1 | [2024-01-01,2024-12-31) | 999 > (1 row) > > Should I expect the column `valid_at` value as [2024-04-01,2024-08-01) ?
Yes, because we ran an INSTEAD OF trigger and skipped the UPDATE (including setting the start/end dates). > We should also document this on doc/src/sgml/ref/update.sgml > Attached is a minor regession test enhancement for > "v2-0001-Fix-INSTEAD-OF-triggers-with-DELETE-UPDATE-FOR-PO.patch". Thanks! I squashed those patches and did some minor cleanup. I posted v4 to this dedicated thread: https://www.postgresql.org/message-id/CA%2BrenyVenLk%2Bu%3DyGvDAyeFEuvkmeQx448-KnnGczqQHB10_fbg%40mail.gmail.com I also made a commitfest entry pointing there. Let's continue on that thread so that future messages & patches get tracked correctly (and not as part of the original feature's CF entry). Hmm I forgot to add the documentation first. So I'll do that and post a v5 shortly. Yours, -- Paul ~{:-) [email protected]
