Re: SQL:2011 Application Time Update & Delete

On Wed, Oct 29, 2025 at 11:02 PM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)com> wrote:
>
> On Tue, Oct 28, 2025 at 3:49 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> > On 24.10.25 19:08, Paul A Jungwirth wrote:
> > > The first 3 doc patches all apply to features that we released in v18,
> > > so it would be nice to get those reviewed/merged soon if possible.
> >
> > I have looked through the documentation patches 0001 through 0003.
>
> Thanks for taking a look! New patches attached; details below.

Hi Hackers,

Here is another set of patches. I added isolation tests for FOR
PORTION OF. In REPEATABLE READ and SERIALIZABLE you get
easy-to-predict results. In READ COMMITTED you get a lot of lost
updates/deletes, because the second operation doesn't see the
leftovers created by the first (and sometimes the first operation
changes the start/end times in a way that EvalPlanQual no longer sees
the being-changed row either). I think those results make sense, if
you think step-by-step what Postgres is doing, but they are not really
what a user wants.

I tested the same sequences in MariaDB, and they also gave nonsense
results, although not always the same nonsense as Postgres. At
UNCOMMITTED READ it actually gave the results you'd want, but at that
level I assume you will have other problems.

I also tested DB2. It doesn't have READ COMMITTED, but I think READ
STABILITY is the closest. At that level (as well as CURSOR STABILITY
and REPEATABLE READ), you get correct results.

Back to Postgres, you can get "desired" results IN READ COMMITTED by
explicitly locking rows (with SELECT FOR UPDATE) just before
updating/deleting them. Since you acquire the lock before the
update/delete starts, there can be no new leftovers created within
that span of history, and the update/delete sees everything that is
there. The same approach also gives correct results in MariaDB. I
think it is just the way you have to do things with temporal tables in
READ COMMITTED whenever you expect concurrent updates to the same
history.

I considered whether we should make EvalPlanQual (or something else)
automatically rescan for leftovers when it's a temporal operation.
Then you wouldn't have to explicitly lock anything. But it seems like
that is more than the isolation level "contract", and maybe even plain
violates it (but arguably not, if you say the update shouldn't *start*
until the other session commits). But since there is a workaround, and
since other RDBMSes also scramble temporal data in READ COMMITTED, and
since it is a lot of work and seems tricky, I didn't attempt it.

Another idea (or maybe nearly the same thing) would be to
automatically do the same thing that SELECT FOR UPDATE is doing,
whenever we see a FOR PORTION OF DML command---i.e. scan for rows and
lock them first, then do the update. But that has similar issues. If
it adds locks the user doesn't expect, is it really the right thing?
And it means users pay the cost even when no concurrency is expected.
It offers strictly fewer options than requiring users to do SELECT FOR
UPDATE explicitly.

The isolation tests are a separate patch for now, because they felt
like a significant chunk, and I wanted to emphasize them, but really
they should be part of the main FOR PORTION OF commit. Probably I'll
squash them in future submissions. That patch also makes some small
updates to a comment in ExecForPortionOf and the docs for
UPDATE/DELETE FOR PORTION OF, to raise awareness of the READ COMMITTED
issues.

Rebased to 65f4976189.

Yours,

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