On 23/12/2024 10:17, David G. Johnston wrote:
On Monday, December 23, 2024, Daniil Davydov <3daniss...@gmail.com
<mailto:3daniss...@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.
Another way to say that is that the snapshot applies to table contents,
but not the schema. Here's another surprising example:
session 2: establish snapshot
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT 123;
session 1:
CREATE TABLE test AS SELECT 2 as id;
session 2:
SELECT * FROM test;
id
----
(0 rows)
Session 2 sees the table that was created concurrently, but not its
contents.
--
Heikki Linnakangas
Neon (https://neon.tech)
