[YSQL] Read committed isolation doesn't switch back to top-level statement's snapshot after function invocation

[pkj415]

[YSQL] Read committed isolation doesn't switch back to top-level statement's snapshot after function invocation

[pkj415]

A simple test below shows two aspects of read committed isolation:

1. Each statement in a function uses a new read point
2. If and when a function execution returns control to the outer caller statement, the execution switches back to using the read point of the outer caller. In other words, read points are saved and the execution can switch between read points.

create table dummy(k int primary key, v int);
insert into dummy values (1, 1);
create table test(k int primary key, v text);
insert into test select generate_series(1,2), '';
insert into test select generate_series(101,102), '';
insert into test select generate_series(201,202), '';
insert into test select generate_series(301,302), '';
insert into test select generate_series(401,402), '';

create or replace function inner_func_volatile(n int)
  returns int
  language plpgsql volatile
as $body$
begin
  perform pg_sleep(1);
  update test set v=v || clock_timestamp() || (' Inner func stmt 1 ...' || ' v:' || (select v from dummy)) where k in (n+100, n+200);
  perform pg_sleep(1);
  update test set v=v || clock_timestamp() || (' Inner func stmt 2 ...' || ' v:' || (select v from dummy)) where k in (n+300, n+400);
  perform pg_sleep(1);
  return n;
end;
$body$;

create or replace procedure parallel_update_loop(num_times int)
  language plpgsql
as $body$
begin
  FOR i IN 1..num_times LOOP
    update dummy set v=v+1 where k=1;
    commit;
  END LOOP;
end;
$body$;

S1: call parallel_update_loop(100000);
S2: update test set v=v || clock_timestamp() || (' Top level stmt ...' || ' v:' || (select v from dummy where k=1)) where inner_func_volatile(k)=k and k IN (1, 2);
S2: select * from test;
  k  |                               v
-----+----------------------------------------------------------------
 101 | 2024-01-22 20:51:38.381113+05:30 Inner func stmt 1 ... v:25271
 201 | 2024-01-22 20:51:38.381221+05:30 Inner func stmt 1 ... v:25271
 301 | 2024-01-22 20:51:39.381978+05:30 Inner func stmt 2 ... v:28940
 401 | 2024-01-22 20:51:39.382512+05:30 Inner func stmt 2 ... v:28940
   1 | 2024-01-22 20:51:40.383365+05:30 Top level stmt ... v:19536
 102 | 2024-01-22 20:51:41.384057+05:30 Inner func stmt 1 ... v:34041
 202 | 2024-01-22 20:51:41.384383+05:30 Inner func stmt 1 ... v:34041
 302 | 2024-01-22 20:51:42.384776+05:30 Inner func stmt 2 ... v:35710
 402 | 2024-01-22 20:51:42.385071+05:30 Inner func stmt 2 ... v:35710
   2 | 2024-01-22 20:51:43.385532+05:30 Top level stmt ... v:19536
(10 rows)