Re: statement_timeout has no effect if sync standby is unavailable

Hi Team,

We have also seen this scenario when we froze the data mount point and run
a DML

Freeze mountpoint

date;fsfreeze --freeze /postgres

The statement is just stuck even thought statement_timeout is set

datid | 14175
datname | postgres
pid | 5789
leader_pid |
usesysid | 10
usename | postgres
application_name | hang_monitor
client_addr | 127.0.0.1
client_hostname |
client_port | 37440
backend_start| 2023-11-01 10:05:51.090023+00
xact_start | 2023-11-01 10:05:51.167816+00
query_start | 2023-11-01 10:05:51.167816+00
state_change | 2023-11-01 10:05:51.167817+00
wait_event_type | IO
wait_event | WALWrite
state | active
backend_xid | 43242183
backend_xmin |
query | update HEARTBEAT set last_updated_time =
timezone('UTC', now()) where ID = 1;
backend_type | client backend

Thanks,
Nikhil

On Fri, Oct 27, 2023 at 3:43 PM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
wrote:

> Hi Team,
>
> I am writing a monitoring tool to find if the database is hung.
>
> One of the scenarios is to stop the standby but keep 'synchronous_standby_names=standby1'
> and 'synchronous_commit=remote_apply' unchanged on primary.
>
> This way the DML queries on primary will be hung. I tried to use 'statement_timeout=5s'
> to timeout the query but it does not work.
>
> Is there another way to timeout query and report an error ? and why is
> statement_timeout not working?
>
> Thanks,
> Nikhil
>