阅读中文版本

Set Statement Timeout per Role

Apply a default statement_timeout to reporting roles so a runaway query cannot monopolise connections and starve the pool.

ALTER ROLE analyst SET statement_timeout = '30s';
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '60s';

SELECT name, setting FROM pg_settings
WHERE name IN ('statement_timeout','idle_in_transaction_session_timeout');

What this recipe does

Configure per-role statement_timeout so long analytical queries are cancelled instead of exhausting the pool.

An ad-hoc analyst query occasionally runs for minutes, holding a connection and reducing capacity for the application.

Prerequisites

• PostgreSQL 13+
• A dedicated reporting role
• Permission to alter role settings

Procedure

1. Set a conservative default timeout on the reporting role.

ALTER ROLE analyst SET statement_timeout = '30s';
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '60s';

Expected result: New sessions for the analyst role cancel statements after 30 seconds and close idle transactions.

2. Verify the settings apply to a new session.

SELECT name, setting FROM pg_settings
WHERE name IN ('statement_timeout','idle_in_transaction_session_timeout');

Expected result: Both settings reflect the role values, proving the session inherited them.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows analyst sessions cancel at 30 seconds by default while the application role keeps its own budget.

Troubleshooting

Queries need longer occasionally — Provide a separate long-query role with a larger timeout rather than removing the guard globally.

Connection killed mid-report — Have the analyst use a statement that can be resumed or chunked for large exports.

Limitations

• A timeout cannot make a bad query fast; it only bounds damage.
• Does not cover other roles.
• Requires careful value selection to avoid false cancellations.