Re: Repeatable read transaction doesn't see dropped table

On Mon, Dec 23, 2024 at 3:17 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> The quoted section describes how two consecutive select queries will see the same data. Your example shows how a single query behaves in isolation. The “as the first query saw it” is fundamentally important since until it successfully executes there are no locks being held restricting the changing of non-data structural aspects of the database. In short, the snapshot doesn’t include an object until it is requested. It’s a repeatable read, not a frozen point-in-time read. The performance implications for the later would be unacceptable.
>
> Thus, the behavior is expected and needed as-is; but I would say that the concurrency control chapter of the documentation is one of the harder to actually learn and understand. It is a challenging topic, so I get why. In its defense, the commentary surrounding the regarding control record and detail does try to make this distinction clear to the reader. YMMV as to its effectiveness in this regard.
>
> David J.
>

Thank you for your comments.

OK, I agree that there are no contradictions from the point of view of
the source code. But essentially, snapshot is just a range of xids,
and we must not see changes of transactions that started after the
snapshot was taken.
As far as I understand, documentation says that repeatable read
transactions take a snapshot at first non-transaction-control
statement, and this snapshot remains relevant for all statements
within the transaction.

My example shows that the second session sees changes that have been
made by a transaction that started after snapshot creation (and it
sees them only because of cache optimization). It might be unexpected
behavior for users.

I also agree that taking it into account will reduce performance, but
maybe we can clarify this aspect in documentation (?)

--
Best regards,
Daniil Davydov