Re: Repeatable read transaction doesn't see dropped table

On 23/12/2024 10:17, David G. Johnston wrote:
> On Monday, December 23, 2024, Daniil Davydov <3danissimo(at)gmail(dot)com
> <mailto: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.

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)