Re: Repeatable read transaction doesn't see dropped table

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

> The quoted section describes how two consecutive select queries will see the same data.  Your example shows how a
singlequery behaves in isolation.  The “as the first query saw it” is fundamentally important since until it
successfullyexecutes 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-timeread.  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
documentationis 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
thereader. 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