PostgreSQL statement timeout

Question

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.

ALTER USER your_user SET statement_timeout=0 and add VACUUM ANALYZE VERBOSE;? — dwir182
– dwir182, Commented Nov 14, 2018 at 7:54
Thanks @dwir182. statement_timeout we can change but client expects real-time response within 5-10 seconds which always works but only in few cases it times out. But will try VACUUM ANALYZE — Jayadeep
– Jayadeep, Commented Nov 14, 2018 at 8:05
you will need to show us the slow queries if you want to improve them. There is no magic knob that makes everything go faster. — user330315
– user330315, Commented Nov 14, 2018 at 8:30
Postgres 9.3 is no longer supported you should plan your upgrade to a supported version as soon as possible. Given the substantial performance improvements that were made in the last 5 years since 9.3 was released that might even solve (some of) your performance problems. — user330315
– user330315, Commented Nov 14, 2018 at 8:32
@a_horse_with_no_name, Thanks for the comments. Its not slow query. Same query on 2nd retry works faster as expected and completes execution within 2-3 seconds. Only sometimes it is slow which still cant understand why. And yes, we must plan the upgrade very soon — Jayadeep
– Jayadeep, Commented Nov 14, 2018 at 8:44

Laurenz Albe · Accepted Answer · 2018-11-14 11:33:36Z

7

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.

answered Nov 14, 2018 at 11:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL statement timeout

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related