Re: Make wal_receiver_timeout configurable per subscription

On 2025/05/22 21:21, Amit Kapila wrote:
> On Wed, May 21, 2025 at 6:04 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote:
>>
>> On 2025/05/20 18:13, vignesh C wrote:
>>> If we set the wal_receiver_timeout configuration using ALTER ROLE for
>>> the subscription owner's role, the apply worker will start with that
>>> value. However, any changes made via ALTER ROLE ... SET
>>> wal_receiver_timeout will not take effect for an already running apply
>>> worker unless the subscription is disabled and re-enabled. In
>>> contrast, this is handled automatically during CREATE SUBSCRIPTION,
>>> where parameter changes are detected.
>>
>> Yes, this is one of the limitations of the user-settable wal_receiver_timeout
>> approach. If we want to change the timeout used by the apply worker without
>> restarting it, storing the value in pg_subscription (similar to how
>> synchronous_commit is handled) would be a better solution.
>>
>> In that case, for example, we could set the default value of
>> pg_subscription.wal_receiver_timeout to -1, meaning the apply worker should
>> use the global wal_receiver_timeout from postgresql.conf. If the value is 0
>> or higher, the apply worker would use the value stored in pg_subscription.
>>
>
> Yeah, I had a similar idea in my mind.

OK, I've implemented two patches:

- 0001 makes the wal_receiver_timeout GUC user-settable.
- 0002 adds support for setting wal_receiver_timeout per subscription.
It depends on the changes in 0001.

With both patches applied, wal_receiver_timeout can be set per role or
per database using ALTER ROLE or ALTER DATABASE (from 0001), and also
per subscription using CREATE SUBSCRIPTION or ALTER SUBSCRIPTION (from 0002).
The per-subscription value is stored in pg_subscription.subwalrcvtimeout,
and it overrides the global setting of wal_receiver_timeout for that
subscription's apply worker. The default is -1, meaning the global setting
(from server config, command line, role, or database) is used.

I'll add this to the next CommitFest.

>> On further thought, another downside of the user-settable approach is that
>> it doesn't work for parameters like wal_retrieve_retry_interval, which is
>> used by the logical replication launcher not the apply worker. So if we
>> want to support per-subscription control for non-apply workers, storing
>> the settings in pg_subscription might be more appropriate.
>>
>
> Yeah, that could be an option, but one might not want to keep such
> variables different for each subscription. Do you think one would like
> to prefer specifying variables that only apply to the subscriber-node
> in a way other than GUC? I always have this question whenever I see
> GUCs like max_sync_workers_per_subscription, which are specific to
> only subscriber nodes.

I like still using the xxx_per_subscription GUC as the default,
and also allowing it to be overridden per subscription. This seems
intuitive for some users and adds useful flexibility.

Regards,

--
Fujii Masao
NTT DATA Japan Corporation