Re: SQL:2011 Application Time Update & Delete

On Wed, Apr 15, 2026 at 10:30 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> On Tue, Apr 14, 2026 at 10:34 PM Paul A Jungwirth
> <pj(at)illuminatedcomputing(dot)com> wrote:
> >
> > > A BEFORE UPDATE trigger that modifies the range column creates overlapping rows. The trigger widening the range doesn't affect leftover computation, which uses the original FPO bounds. Result: updated row overlaps both leftovers.
> >
> > I'm working on a fix for this. It's not quite ready, but I can finish
> > it in the morning. . . .
>
> Actually I think the proper behavior here is to raise an error. We
> forbid setting the application-time column when using FOR PORTION OF
> (per the standard), so why should we allow a BEFORE trigger to set it?
> I think it has the same inconsistency problems. We could support it,
> but then why not support both?
>
> Assuming we want to raise an error, I think the best way is to check
> the tuple in ExecForPortionOfLeftovers to see if a trigger has
> modified it, and in that case raise an error. What do you think?

Here is a patch that forbids changing the valid_at column in a BEFORE
trigger. It works by capturing the value before triggers run, then
checking afterwards if it is still the same (using the default btree
equality operator; probably a simple binary comparison is good
enough).

This copy+check only happens if the table has BEFORE UPDATE row
triggers, so there is no cost in most cases.

I'm raising ERRCODE_TRIGGERED_DATA_CHANGE_VIOLATION, which is what we
use when (basically) a trigger & UPDATE both change a row in a way
that leaves the user intent unclear. I think that's a very close fit
here, but you could argue we should use the same errcode as SETing
valid_at. That is ERRCODE_SYNTAX_ERROR. That strikes me as a
questionable choice, actually. Personally I think using different
errcodes is correct though.

In ExecForPortionOfSaveRange there is a lot of code duplication
copying the structure for child partitions, but I think we could cut
that by first adding jian he's helper function (ExecInitForPortionOf)
from another bugfix patch [1].

[1] https://www.postgresql.org/message-id/CA%2BrenyWD%2BXXifwswE74vhjooqbiVKu4qVhLvpMcUQBzrjVjT7A%40mail.gmail.com

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com