Re: Repeatable read transaction doesn't see dropped table

On Monday, December 23, 2024, Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:

> Hi,
> The documentation for PostgreSQL 17 says the following :
> "query in a repeatable read transaction sees a snapshot as of the
> start of the first non-transaction-control statement in the
> transaction, not as of the start of the current statement within the
> transaction"
>
> But I noticed this behavior (REL_17_STABLE):
> ***
> SESSION 1: create two user tables and fill them with data
> CREATE TABLE test (id INT);
> CREATE TABLE test_1 (id INT);
> INSERT INTO test VALUES (1);
> INSERT INTO test_1 VALUES (1);
>
> SESSION 2 : begin transaction and allow it to take snapshot
> BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> SELECT * FROM test_1;
>
> SESSION 1 : drop table, that was not accessed from second session
> DROP TABLE test;
>
> SESSION 2 :
> SELECT * FROM test;
> ***
>
> If I'm not mistaken, second transaction must see all data in table
> test (according to documentation), but an error occurs:
>
> I would like to know your opinion.
>

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.