What is this issue?
The statement_timeout setting automatically cancels statements that run
longer than the specified duration. With no timeout (0):
- Runaway queries can run forever
- Accidental cartesian joins consume unlimited resources
- Missing WHERE clauses in UPDATE/DELETE are uncontrolled
- Application bugs can overwhelm the database
While some long operations are legitimate, having no timeout means no
safety net for problematic queries.
Why it matters
Runaway Queries
Problematic queries consume resources indefinitely
Resource Exhaustion
One bad query can impact all users
No Automatic Recovery
Manual intervention required to stop bad queries
Cascade Effects
Long queries can hold locks, blocking others
How PG Pilot detects it
```sql SELECT name, setting, unit FROM pg_settings WHERE name = 'statement_timeout'; ```
How to fix it
1
Set a reasonable default
Choose a timeout that allows legitimate operations:
-- 30 minutes is a common default
ALTER SYSTEM SET statement_timeout = '30min';
SELECT pg_reload_conf();
2
Allow longer for specific operations
Override per-session or per-transaction:
SET statement_timeout = '2h';
-- Run long operation
RESET statement_timeout;
3
Consider per-role settings
Different timeouts for different users:
ALTER ROLE web_app SET statement_timeout = '30s';
ALTER ROLE batch_job SET statement_timeout = '2h';
ALTER ROLE admin SET statement_timeout = 0;Prevention
- Always set a default statement_timeout
- Use shorter timeouts for interactive applications
- Allow overrides for batch operations
- Monitor long-running queries with PG Pilot