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@gmail.com 
> <mailto: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.

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)