Report Timeout and Running Reports
PostgreSQL can stop a statement that runs too long and can cancel a statement of another connection. myDBR uses both: a timeout for reports, and a Running reports page where an administrator can see and stop the reports that are running now. Both are available on PostgreSQL only.
Report timeout
The timeout protects the database from reports that run for too long, for example because of a missing filter or a user who starts the same report again and again.
On the Database tab of Preferences > Environment settings:
| Setting | Meaning |
|---|---|
| Report timeout (seconds) | The longest time of a statement of a report. 0 means no limit (default). |
| Lock wait timeout (seconds) | The longest time a statement waits for a lock held by another connection. 0 means no limit. |
myDBR sets statement_timeout and lock_timeout with SET LOCAL in the transaction of every report run, so they end with the report. A report that runs longer is stopped by PostgreSQL and the user sees The report has timed out. The next report starts again with the normal settings.
A limit for a single report
A report can have its own limit. Write it in the comment of the function (seconds, 0 for no limit):
comment on function sp_DBR_year_summary(integer) is 'Year summary. @mydbr timeout=300';
The tag overrides the global setting. A report with a long running summary can have a longer limit than the others, and a heavy report can have a shorter one.
The timeout applies to the run of reports. The Query Plan of the SQL Editor uses the same limit (60 seconds if no limit is set), other statements of the SQL Editor are not limited.
Running reports
The Running reports page of the administration menu lists the reports that are being run now. It is shown only for PostgreSQL connections and only to administrators.
| Column | Meaning |
|---|---|
| User | The myDBR user who runs the report |
| Report | The function of the report |
| Time | How long the statement has run |
| State | The state of the connection (active, idle in transaction) |
| Waiting for | What the statement waits for, for example a lock |
| Query | The beginning of the statement |
The list refreshes every 5 seconds.
- Cancel stops the statement (
pg_cancel_backend). The connection stays, and the user who runs the report sees The report has timed out. - Terminate ends the whole database connection (
pg_terminate_backend) after a confirmation.
myDBR names the connection of a report run mydbr:<user>:<report>, and the page shows only those connections of the database. A request for any other process is refused, so the page cannot be used to stop other applications.
Privileges
The connection account of myDBR can stop its own connections. To stop connections of other roles it needs membership in the role pg_signal_backend:
grant pg_signal_backend to mydbr_user;
If PostgreSQL refuses, the page shows the message that the connection account may only stop its own connections.