Re: Change log level for notifying hot standby is waiting non-overflowed snapshot

On 2025-03-03 13:10, Fujii Masao wrote:

Thanks for your comments!

> On 2025/02/03 22:35, torikoshia wrote:
>> Hi,
>> 
>> When a hot standby is restarted in a state where subtransactions have 
>> overflowed, it may become inaccessible:
>> 
>>    $ psql: error: connection to server at "localhost" (::1), port 5433 
>> failed: FATAL:  the database system is not yet accepting connections
>>            DETAIL:  Consistent recovery state has not been yet 
>> reached.
> 
> Could you share the steps to reproduce this situation?

We can reproduce this situation using the following procedure.
I performed this test with one asynchronous standby server.

-- overflow subtransaction
(primary)=# create table t1 (i int);
(primary)=# select 'insert into t1 values (1); savepoint s_' || 
generate_series(1, 70) ; \gexec
(primary)=# checkpoint;

-- restart standby
$ pg_ctl restart -D data_stb/
waiting for server to shut down.... done
server stopped
waiting for server to start.... LOG:  redirecting log output to logging 
collector process
........................................................... stopped 
waiting
pg_ctl: server did not start in time

-- standby log
DEBUG:  recovery snapshot waiting for non-overflowed snapshot or until 
oldest active xid on standby is at least 887 (now 818)


>> However, the log message that indicates the cause of this issue seems 
>> to be only output at the DEBUG1 level:
>> 
>>    elog(DEBUG1,
>>         "recovery snapshot waiting for non-overflowed snapshot or "
>>         "until oldest active xid on standby is at least %u (now %u)",
>>         standbySnapshotPendingXmin,
>>         running->oldestRunningXid);
>> 
>> I believe this message would be useful not only for developers but 
>> also for users.
> 
> Isn't this log message too difficult for most users? It seems to
> describe PostgreSQL's internal mechanisms, making it hard
> for users to understand the issue and what actions to take.

Agreed and I feel that a message suggesting something like "check if 
there are any overflowing transactions on the primary side" would make 
it useful.
On the other hand, the manual's explanation of 
pg_stat_get_backend_subxact() does not mention subtransaction overflow, 
so I am not sure how much detail should be included.


-- 
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA GROUP CORPORATION to SRA OSS K.K.