On Monday, December 23, 2024, Daniil Davydov <
3danissimo@gmail.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.
