MIME-Version: 1.0
From: PetSerAl <petseral@gmail.com>
Date: Thu, 26 Dec 2024 20:40:44 +0300
Message-ID: <CAKygsHRz=pu35g0rGW8xPorho=1x+BKFd3_HegDerzgi2UYabw@mail.gmail.com>
Subject: Synchronous commit after asynchronous commit
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAKygsHRz%3Dpu35g0rGW8xPorho%3D1x%2BBKFd3_HegDerzgi2UYabw%40mail.gmail.com>
Precedence: bulk

--setup
create table a(i int);
create table b(i int);
insert into a values (1);
insert into b values (1);

-- case 1
set synchronous_commit = off;
begin read write;
update a set i = i + 1;
commit;

set synchronous_commit = on;
begin read write;
update b set i = i + 1;
commit;

-- case 2
set synchronous_commit = off;
begin read write;
update a set i = i + 1;
commit;

set synchronous_commit = on;
begin read only;
select i from a;
commit;

-- case 3
set synchronous_commit = off;
begin read write;
update a set i = i + 1;
commit;

set synchronous_commit = on;
begin read only;
select i from b;
commit;

-- case 4
set synchronous_commit = off;
begin read write;
update a set i = i + 1;
commit;

set synchronous_commit = on;
begin read only;
commit;


As I understand documentation, case 1 is clear: following synchronous
commit of read write transaction force previous asynchronous commits
to be flushed with it. But what about case 2 (read changes from
asynchronous commit), case 3 (read unrelated data) and case 4 (empty
commit)? Would synchronous commit of read only transaction force
previous asynchronous commits to be flushed to disk?