Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create extension if not exists dblink;
select dblink_connect('s1','');

CREATE EXTENSION

dblink_connect

OK

SELECT 1

begin;
create table t as select 'some data' as v;
commit and chain;--my transaction opens

BEGIN

SELECT 1

COMMIT

select dblink_send_query('s1','begin;');--concurrent transaction opens 
select*from dblink_get_result('s1',true)as r(v text);
select pg_sleep(.05);
select dblink_is_busy('s1');

dblink_send_query

1

SELECT 1

v

BEGIN

SELECT 1

pg_sleep

SELECT 1

dblink_is_busy

0

SELECT 1

delete from t;--my transaction wipes the table
select dblink_send_query('s1','select * from t;');--the other transaction shouldn't see the wipe yet
select pg_sleep(.05);
commit and chain;

DELETE 1

dblink_send_query

1

SELECT 1

pg_sleep

SELECT 1

COMMIT

--the other transaction fetched things from `t` 
--after my `delete` but before my `commit`, 
--so it should still have received the contents
select*from dblink_get_result('s1')as r(v text);

v

some data

SELECT 1

--prepare for equivalent `truncate` test
select*from dblink_get_result('s1',true)as r(v text);
select pg_sleep(.05);
select dblink_is_busy('s1');
insert into t select 99;
commit and chain;--my new transaction opens
select dblink_send_query('s1','commit and chain;');--concurrent transaction opens 
select*from dblink_get_result('s1',true)as r(v text);
select pg_sleep(.05);
select dblink_is_busy('s1');

v

SELECT 0

pg_sleep

SELECT 1

dblink_is_busy

0

SELECT 1

INSERT 0 1

COMMIT

dblink_send_query

1

SELECT 1

v

COMMIT

SELECT 1

pg_sleep

SELECT 1

dblink_is_busy

0

SELECT 1

truncate t;
select dblink_send_query('s1','select * from t;');--the other transaction shouldn't see the wipe yet
select pg_sleep(.05);
commit and chain;

TRUNCATE TABLE

dblink_send_query

1

SELECT 1

pg_sleep

SELECT 1

COMMIT

select*from dblink_get_result('s1',true)as r(v text);
select pg_sleep(.05);
select dblink_is_busy('s1');

v

SELECT 0

pg_sleep

SELECT 1

dblink_is_busy

0

SELECT 1