Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: SQL:2011 Application Time Update & Delete
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <7A20FF56-1A90-4221-A95E-5A605E36410A@gmail.com>
Date: Thu, 13 Nov 2025 11:31:57 +0800
Cc: Peter Eisentraut <peter@eisentraut.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <33505902-8524-430B-A39A-C71119937E91@gmail.com>
References: <ec498c3d-5f2b-48ec-b989-5561c8aa2024@illuminatedcomputing.com>
 <CA+renyW-S0LyG0E4qxFvnKNKsgq_6WWeTStOXHpjCvwj6LKS6Q@mail.gmail.com>
 <CA+renyXXJJCmgG0Wdf89JgVNAeRKkVn+EuZGTf4Ph-BWoJafQA@mail.gmail.com>
 <CA+renyWA-b00qvz4gDdPbPBjVatacB1T5v7SJc6J_xg3R6-qRw@mail.gmail.com>
 <CA+renyVYnV9rGDiUhTQEy8r6gx6Xg-+OEo-2DK7JbuBXcLVq5A@mail.gmail.com>
 <CA+renyWcNBdnaW4zc9S03aN+fEbVVB1S+q8e9MEjhM2YB+kkiw@mail.gmail.com>
 <CA+renyUiEu2yTHk+Vy-Rt2xA5Vnp2N_ohN=npiNmMKTh53qDfA@mail.gmail.com>
 <2f5364f3-a1d3-4410-98f3-d788b11e6525@eisentraut.org>
 <CA+renyW7ZB_k9AgmSFJU2EegL9r1k1sgWo4-9tGGkgwxNqe6kw@mail.gmail.com>
 <CA+renyUodzxAvMnpa_LTvo+Ru1ZKH+Su8KaPvD4iMtguFKzq4g@mail.gmail.com>
 <7A20FF56-1A90-4221-A95E-5A605E36410A@gmail.com>
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Archived-At: 
 <https://www.postgresql.org/message-id/33505902-8524-430B-A39A-C71119937E91%40gmail.com>
Precedence: bulk


> On Nov 12, 2025, at 17:31, Chao Li <li.evan.chao@gmail.com> wrote:
>=20
>=20
>=20
>> On Nov 5, 2025, at 03:12, Paul A Jungwirth =
<pj@illuminatedcomputing.com> wrote:
>>=20
>> On Wed, Oct 29, 2025 at 11:02=E2=80=AFPM Paul A Jungwirth
>> <pj@illuminatedcomputing.com> wrote:
>>>=20
>>> On Tue, Oct 28, 2025 at 3:49=E2=80=AFAM Peter Eisentraut =
<peter@eisentraut.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.
>>>>=20
>>>> I have looked through the documentation patches 0001 through 0003.
>>>=20
>>> Thanks for taking a look! New patches attached; details below.
>>=20
>> Hi Hackers,
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> 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.
>>=20
>> Rebased to 65f4976189.
>>=20
>> Yours,
>>=20
>> --=20
>> Paul              ~{:-)
>> pj@illuminatedcomputing.com
>> =
<v59-0003-Document-temporal-update-delete.patch><v59-0005-Add-UPDATE-DELET=
E-FOR-PORTION-OF.patch><v59-0001-Add-docs-section-for-temporal-tables-with=
-primar.patch><v59-0004-Add-range_minus_multi-and-multirange_minus_multi.p=
atch><v59-0007-Add-tg_temporal-to-TriggerData.patch><v59-0002-Document-tem=
poral-foreign-keys.patch><v59-0008-Look-up-more-temporal-foreign-key-helpe=
r-procs.patch><v59-0009-Add-CASCADE-SET-NULL-SET-DEFAULT-for-temporal-fo.p=
atch><v59-0006-Add-isolation-tests-for-UPDATE-DELETE-FOR-PORTIO.patch><v59=
-0011-Add-PERIODs.patch><v59-0010-Expose-FOR-PORTION-OF-to-plpgsql-trigger=
s.patch>
>=20
> I tried to review this patch. Though I =E2=80=9Cgit reset=E2=80=9D to =
commit 65f4976189, =E2=80=9Cgit am=E2=80=9D still failed at 0009.
>=20
> Today I only reviewed 0001, it was a happy reading. I found a small =
typo and got a suggestion:
>=20
> 1 - 0001
> ```
> +    entity described by a table. In a typical non-temporal table, =
there is
> +    single row for each entity. In a temporal table, an entity may =
have=20
> ```
>=20
> =E2=80=9CThere is single row=E2=80=9D should be =E2=80=9Cthere is a =
single row=E2=80=9D.
>=20
>=20
> 2 - 0001 - The doc mentions rangetypes which is the key factor for =
defining a temporal table, can we add a hyper link on =E2=80=9Crangetype=E2=
=80=9D so that readers can easily jump to learn which rangetypes can be =
used.
>=20
> I will continue to review the rest of commits tomorrow.
>=20

I spent a hour reading through 0002-0004 and got my brain stuck. I=E2=80=99=
d stop here today, and maybe continue tomorrow.

A few more comments:

3 - 0002
```
+<programlisting>
+CREATE TABLE variants (
+  id         integer   NOT NULL,
+  product_id integer   NOT NULL,
+  name       text      NOT NULL,
+  valid_at   daterange NOT NULL,
+  CONSTRAINT variants_pkey
+    PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+);
+</programlisting>
```

The common before ) is not needed.

4 - 0002
```
+    <para>
+
+     In a table, these records would be:
+<programlisting>
+ id | product_id |  name  |        valid_at
+----+------------+--------+-------------------------
+  8 |          5 | Medium | [2021-01-01,2023-06-01)
+  9 |          5 | XXL    | [2022-03-01,2024-06-01)
+</programlisting>
+    </para>
```

The blank line after =E2=80=9C<para>=E2=80=9D is not needed.

5 - 0003
```
+     zero, one, or two stretches of history that where not =
updated/deleted
```

Typo: where -> were

6 - 0004 - func-range.sgml
```
      <row>
       <entry role=3D"func_table_entry"><para role=3D"func_signature">
        <indexterm>
         <primary>multirange_minus_multi</primary>
        </indexterm>
        <function>multirange_minus_multi</function> ( =
<type>anymultirange</type>, <type>anymultirange</type> )
        <returnvalue>setof anymultirange</returnvalue>
       </para>
       <para>
        Returns the non-empty multirange(s) remaining after subtracting =
the second multirange from the first.
        If the subtraction yields an empty multirange, no rows are =
returned.
        Two rows are never returned, because a single multirange can =
always accommodate any result.
       </para>
       <para>
        <literal>range_minus_multi('[0,10)'::int4range, =
'[3,4)'::int4range)</literal>
        <returnvalue>{[0,3), [4,10)}</returnvalue>
       </para></entry>
      </row>
```

I believe in " <literal>range_minus_multi('[0,10)'::int4range, =
'[3,4)'::int4range)</literal>=E2=80=9D, it should be =
=E2=80=9Cmultirange_minus_multi=E2=80=9D.


Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/