Postgres Pro
Downloads
Home   >   mailing lists

Re: statement_timeout has no effect if sync standby is unavailable - Mailing list pgsql-admin

From Nikhil Shetty
Subject Re: statement_timeout has no effect if sync standby is unavailable
Date
Msg-id CAFpL5VzcQXxzbzkVAnRM87qVZnZX4VYh1YcWZVMqSz+jH2AErw@mail.gmail.com
Whole thread Raw
In response to statement_timeout has no effect if sync standby is unavailable  (Nikhil Shetty <nikhil.dba04@gmail.com>)
Responses Re: statement_timeout has no effect if sync standby is unavailable
List pgsql-admin
Tree view
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@gmail.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

pgsql-admin by date:

Previous
From: Zhaoxun Yan
Date:
Subject: connection with the ha-availability software 'repmgr' broke down for idle
Next
From: Laurenz Albe
Date:
Subject: Re: statement_timeout has no effect if sync standby is unavailable