On Mon, 15 Sept 2025 at 13:49, Chao Li <li.evan.chao@gmail.com> wrote:
> This is a wrong example and (0,3) should NOT be updated. According to the definition of “read committed”:
>
> 13.2. Transaction Isolation
> postgresql.org
>
> “A query sees only data committed before the query began”.
I'm afraid your understanding of the documentation isn't correct.
Maybe you've observed some translated version which isn't correct, or
you've made a mistake in your translation back to English.
For the quoted line, I'm not quite sure where that comes from, but I
don't see it as it is in the documents. The particular part of the
documentation that's relevant is in [1]. The following is the
relevant text:
"If the first updater commits, the second updater will ignore the row
if the first updater deleted it, otherwise it will attempt to apply
its operation to the updated version of the row. The search condition
of the command (the WHERE clause) is re-evaluated to see if the
updated version of the row still matches the search condition. If so,
the second updater proceeds with its operation using the updated
version of the row."
> And this example also proves my solution of checking visibility works.
The specific part that Sophie aims to fix is the incorrect behaviour
regarding the "re-evaluation" of the updated row. The correct way to
fix that is to ensure the updated tuple matches the WHERE clause of
the statement. Adding some visibility checks in place of that is
complete nonsense. What does need to happen is validation that the
ctid of the updated tuple matches the WHERE clause of the UPDATE
statement. The reason anything special must happen for TID Scan and
TID Range Scan and not Seq Scan is that in Seq Scan the ctid qual will
be part of the scan's qual, whereas in the TID Scan variants, that's
been extracted as it's assumed the scan itself will handle only
getting the required rows. It's just that's currently not enforced
during the recheck.
If you review the ExecScan() comments, you'll see the recheck
function's role is the following:
* A 'recheck method' must also be provided that can check an
* arbitrary tuple of the relation against any qual conditions
* that are implemented internal to the access method.
If you need guidance about how this should be behaving, try with "SET
enable_tidscan = 0;" and see what that does.
David
[1] https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED
