Menu
PostgreSQL Version: 9.3
We have online system which gets transnational data (approximately 15000 records per day).
We have table partitioning on date & time and have a PostgreSQL function to load the incoming request into the table.
Sometimes we see the error message
ERROR: 57014: canceling statement due to statement timeout
The client sends the request again after some time if not successful and on second try it gets recorded successfully. It seems this has to be something with table locks but I am not sure.
980
Stops any statement that takes over the specified number of milliseconds. The statement_timeout value is the maximum amount of time a query can run before Amazon Redshift terminates it. This time includes planning, queueing in workload management (WLM), and execution time.
idle in transaction: This indicates the backend is in a transaction, but it is currently not doing anything and could be waiting for an input from the end user.
If you want to see how many idle connections you have that have an open transaction, you could use: select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null; This will provide a list of open connections that are in the idle state, that also have an open transaction.
tcp_keepalives_idle ( integer ) Specifies the amount of time with no network activity after which the operating system should send a TCP keepalive message to the client. If this value is specified without units, it is taken as seconds. A value of 0 (the default) selects the operating system's default.
Set log_lock_waits = on in postgresql.conf and see if you get log entries. That would tell you that a query is hanging for more than one second.
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
