Horton
Menu

Re: SQL:2011 Application Time Update & Delete

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Chao Li <li.evan.chao@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-15T17:30:21Z
Lists: pgsql-hackers 
On Tue, Apr 14, 2026 at 10:34 PM Paul A Jungwirth
<pj@illuminatedcomputing.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?

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Make ExecForPortionOfLeftovers() obey SRF protocol.

  2. Add isolation tests for UPDATE/DELETE FOR PORTION OF

  3. Add UPDATE/DELETE FOR PORTION OF

  4. Record range constructor functions in pg_range

  5. Add range_minus_multi and multirange_minus_multi functions

  6. doc: Add section for temporal tables

  7. Add assertion check for WAL receiver state during stream-archive transition