Limits of statement_timeout for PL/pgSQL tight loops
28K reputation · 30 Jul 2020, 02:07 UTC
Goal
Ensure that a PL/pgSQL function containing a tight computational loop is cancelled as soon as the configured statement_timeout expires, providing a reliable upper bound on execution time.
Constraints and uncertainty
statement_timeout is evaluated only between executor nodes; a PL/pgSQL function that does not yield control to the executor will not be checked until it returns from the current node. It is unclear whether inserting an explicit yield point such as PERFORM pg_sleep(0) or CHECK_FOR_INTERRUPTS() inside the loop guarantees that the timeout will be honoured, or if any additional configuration is required to make the check more frequent.
Does adding PERFORM pg_sleep(0) inside each loop iteration cause the executor to check statement_timeout promptly?
Can CHECK_FOR_INTERRUPTS() be used as a portable yield point to achieve timely cancellation?
Is there a GUC or planner setting that forces statement_timeout to be evaluated more frequently within PL/pgSQL functions?
postgresql