BUG #16035: STATEMENT_TIMEOUT not working when we have single quote usage inside CTE which is used in inner sql
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: rmohite@xento.com
Date: 2019-10-03T06:38:33Z
Lists: pgsql-bugs
The following bug has been logged on the website: Bug reference: 16035 Logged by: Raj Mohite Email address: rmohite@xento.com PostgreSQL version: 10.8 Operating system: Windows 10 Description: I have created a simple sql where I have added STATEMENT_TIMEOUT = 1 seconds and added explicit delay(pg_sleep(5) ) of 5 seconds inside CTE. Below sql should get killed after 1 second but looks like it is running for 5 seconds. SET STATEMENT_TIMEOUT = '1s';SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM test ) AS sub; Note: If we remove the quoted string 'Billy' AS emp_name from above sql then proper timeout is getting applied. If we keep quoted string -- 'Billy' AS emp_name in the comment then also it doesn't work. Solution: We are still finding the root cause for the above behavior but I found that if we will put the SET STATEMENT_TIMEOUT before and after the sql then it working fine. Example: SET STATEMENT_TIMEOUT = '3s';SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM test ) AS sub;SET STATEMENT_TIMEOUT = '1s'; Above example will timeout after 3 seconds i.e. it will consider the 1st timeout value. Can you please help us to know why STATEMENT_TIMEOUT is not working in first example?
Commits
-
Improve management of statement timeouts.
- 22f6f2c1ccb5 13.0 landed
-
Reset statement_timeout between queries of a multi-query string.
- 2b2bacdca010 13.0 landed
-
Rearm statement_timeout after each executed query.
- f8e5f156b30e 11.0 cited