[YSQL] Non-transactional side-effects can occur more than once when a conflict/ read restart error in functions/ procedures is handled by read committed isolation level

[pkj415]

Jira Link: DB-2692

Description

Right now, statements are internally retried in READ COMMITTED isolation level with exponential backoff if a kConflict or kReadRestart error is seen at the query layer. They are retried on a new latest snapshot for kConflict and with restart read time for kReadRestart errors.

However the following known issue can occur -

create table t(k serial primary key, v varchar(100) not null);
insert into t(v) values ('dog');

create or replace procedure f(new_v in text)
  language sql
as $body$
  deallocate dummy_query;
  insert into t(v) values ('cat'); -- if this faces a kConflict and the whole function is retried because the top level client statement is retried
$body$;

Session 1: begin; insert into t(v) values ('cat');
Session 2: prepare dummy_query as select * from t; call f();

call f() would fail because deallocation on dummy_query would be attempted more than once. The error wouldn't occur if only the statement that faced the conflict in the procedure was retried. This is not an issue in Postgres because it handles conflicts with eval plan qual handling (see #11573).

This issue would be solved for kConflicts once #11573 is fixed. It might be solved for kReadRestart too in case the solution for #11572 gets rid of statement retries for kReadRestart errors using some new scheme to get rid of kReadRestart errors.