Re: statement_timeout has no effect if sync standby is unavailable

[Nikhil Shetty <nikhil.dba04@xxxxxxxxx>]

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.dba04@xxxxxxxxx> 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