Re: Queries waiting on SyncRep even though synchronous_standby_names is absent

Hi Affan,

Other than that, if the standbys are slow, disconnected, or down, the
> primary will wait for acknowledgements from one or more synchronous
> standbys. By resetting the synchronous_standby_names parameter, you changed
> the configuration of which standby servers need to confirm transactions for
> them to be considered committed. Please execute the following command on
> the primary node after resetting the synchronous_standby_names to reload
> the latest configurations:
> SELECT pg_reload_conf();

I had done a database reload for the synchronous_standby_names setting to
take effect and that is why when I do 'show synchronous_standby_names' it
shows empty. Without reload it would show an old setting.

Forcefully truncate long or stuck transactions; if it still doesn't help,
> restart the instance.

This is an easy way to get the system back which I already did but I would
like to understand the reason behind this or at least see how we can debug ?

Thanks,
Nikhil

On Thu, Jul 18, 2024 at 3:02 PM khan Affan <bawag773(at)gmail(dot)com> wrote:

> Hi
>
> Other than that, if the standbys are slow, disconnected, or down, the
> primary will wait for acknowledgements from one or more synchronous
> standbys. By resetting the synchronous_standby_names parameter, you changed
> the configuration of which standby servers need to confirm transactions for
> them to be considered committed. Please execute the following command on
> the primary node after resetting the synchronous_standby_names to reload
> the latest configurations:
> SELECT pg_reload_conf();
>
>
> *Muhammad Affan (*아판*)*
>
> *PostgreSQL Technical Support Engineer** / Pakistan R&D*
>
> Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan
>
> On Thu, Jul 18, 2024 at 2:20 PM khan Affan <bawag773(at)gmail(dot)com> wrote:
>
>> Hi
>>
>> Check the stuck transactions by
>>
>> SELECT pid, username, state, query, xact_start
>> FROM pg_stat_activity
>> WHERE state = 'active' AND xact_start IS NOT NULL;
>>
>> Forcefully truncate long or stuck transactions; if it still doesn't help,
>> restart the instance.
>>
>> Thanks
>>
>>
>> *Muhammad Affan (*아판*)*
>>
>> *PostgreSQL Technical Support Engineer** / Pakistan R&D*
>>
>> Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan
>>
>>
>> On Thu, Jul 18, 2024 at 1:43 PM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
>> wrote:
>>
>>> Hi Team,
>>>
>>> PostgreSQL : 13.6
>>>
>>> I saw a strange behavior today. All write queries were stuck on the
>>> 'SyncRep' wait event so I reset the synchronous_standby_names.
>>>
>>> postgres=# show synchronous_standby_names ;
>>>> synchronous_standby_names
>>>> ---------------------------
>>>>
>>>> (1 row)
>>>
>>>
>>> We still saw many queries waiting on SyncRep
>>>
>>> datid | datname | pid | leader_pid | usesysid | usename |
>>>> application_name | client_addr | client_hostname | client_port |
>>>> backend_start | xact_start |
>>>> query_start
>>>> | state_change | wait_event_type | wait_event
>>>> | state | backend_xid | backend_xmin |
>>>> query | backend_type
>>>>
>>>> -------+----------+-------+------------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+------------------------
>>>>
>>>> -------+-------------------------------+-----------------+------------+--------+-------------+--------------+-------------------------------------------------------------------------------------+----------------
>>>> 16401 | repmgr | 24344 | | 16385 | postgres | psql
>>>> | | | -1 | 2024-07-14
>>>> 09:55:09.615682+00 | 2024-07-14 09:55:09.622254+00 | 2024-07-14 09:55:09.622
>>>> 254+00 | 2024-07-14 09:55:09.622255+00 | IPC | SyncRep |
>>>> active | 343975184 | 343974710 | UPDATE repmgr.sync_check SET
>>>> row_update_time = (select timezone('UTC', now())) | client backend
>>>> (1 row)
>>>
>>>
>>>
>>> Has anyone faced this issue or have any inputs
>>>
>>>
>>> Thank you,
>>>
>>> Nikhil
>>>
>>