Hi!
On Wed, Oct 4, 2023 at 1:22 PM Alexander Korotkov <[email protected]> wrote:
> I see you're concentrating on the procedural version of this feature. But
> when you're calling a procedure within a normal SQL statement, the executor
> gets a snapshot and holds it until the procedure finishes. In the case the
> WAL record conflicts with this snapshot, the query will be canceled.
> Alternatively, when hot_standby_feedback = on, the query and WAL replayer
> will be in a deadlock (WAL replayer will wait for the query to finish, and
> the query will wait for WAL replayed). Do you see this issue? Or do you
> think I'm missing something?
I'm sorry, I actually meant hot_standby_feedback = off
(hot_standby_feedback = on actually avoids query conflicts). I
managed to reproduce this problem.
master: create table test as (select i from generate_series(1,10000) i);
slave conn1: select pg_wal_replay_pause();
master: delete from test;
master: vacuum test;
master: select pg_current_wal_lsn();
slave conn2: select pg_wait_lsn('the value from previous query'::pg_lsn, 0);
slave conn1: select pg_wal_replay_resume();
slave conn2: ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
Needless to say, this is very undesirable behavior. This happens
because pg_wait_lsn() has to run within a snapshot as any other
function. This is why I think this functionality should be
implemented as a separate statement.
Another issue I found is that pg_wait_lsn() hangs on the primary. I
think an error should be reported instead.
------
Regards,
Alexander Korotkov
