Resolution
Adding PERFORM pg_sleep(0); inside a tight PL/pgSQL loop is an effective way to force the executor to check for statement_timeout. Because pg_sleep is a function call that invokes the executor, it creates a checkpoint where the backend process evaluates pending interrupts and timeout limits.
Technical Explanation
In PostgreSQL, statement_timeout is not monitored continuously via a separate thread; instead, it is checked at specific "interrupt points" during execution. In a standard SQL query, these points occur between executor nodes. However, a PL/pgSQL loop performing pure variable manipulation or computational logic stays within the PL/pgSQL interpreter and does not yield control back to the main executor.
If a loop contains no SQL statements (e.g., only IF, WHILE, and variable assignments), the timeout will not be triggered until the function either completes or executes a statement that interacts with the executor. By inserting PERFORM pg_sleep(0);, you force a context switch to the executor, which immediately checks if the wall-clock time has exceeded the configured limit.
Implementation and Verification
To ensure a reliable upper bound on execution time, structure your loop as follows:
CREATE OR REPLACE FUNCTION tight_loop_test() RETURNS void AS $$
BEGIN
LOOP
-- Perform computational work here
-- Yield to executor to check statement_timeout
PERFORM pg_sleep(0);
END LOOP;
END;
$$ LANGUAGE plpgsql;
Verification Steps:
- Set a short timeout:
SET statement_timeout = '1s';
- Execute the function:
SELECT tight_loop_test();
- Confirm the statement is cancelled with a
canceling statement due to statement timeout error.
Clarifications on Constraints
- CHECK_FOR_INTERRUPTS(): This is not a standard, portable PL/pgSQL function. The
pg_sleep(0) method is the established pattern for yielding.
- GUC Settings: There is no global configuration setting to increase the frequency of timeout checks within the PL/pgSQL interpreter itself; it is fundamentally tied to executor entry points.
Missing Diagnostic: To provide a more optimized recommendation, please specify if the loop performs any SELECT, INSERT, or UPDATE operations. If it does, those operations already act as interrupt points, and pg_sleep(0) may be redundant.
postgresql