Re: BUG #16035: STATEMENT_TIMEOUT not working when we have singlequote usage inside CTE which is used in inner sql - Mailing list pgsql-bugs

From Tatsuo Ishii
Subject Re: BUG #16035: STATEMENT_TIMEOUT not working when we have singlequote usage inside CTE which is used in inner sql
Date
Msg-id 20191006.162220.1257531585774510977.t-ishii@sraoss.co.jp
Whole thread Raw
In response to Re: BUG #16035: STATEMENT_TIMEOUT not working when we have singlequote usage inside CTE which is used in inner sql  (Tatsuo Ishii <ishii@sraoss.co.jp>)
Responses Re: BUG #16035: STATEMENT_TIMEOUT not working when we have singlequote usage inside CTE which is used in inner sql
Re: BUG #16035: STATEMENT_TIMEOUT not working when we have single quote usage inside CTE which is used in inner sql
List pgsql-bugs
>> tl;dr: I do not think this is buggy in v10.  But arguably there's
>> a bug in later branches, and they need to go back to behaving
>> like v10.
> 
> I understand the original reporter's complain. Also I understand Tom's
> complain to v11's behavior. I will look into the v11 (and above) code.

I admit v11's current behavior is inconstant, but I am not sue going
to back V10's behavior is a good idea.

With attached patch (against master), SET STATEMENT_TIMEOUT
immediately affects to subsequent commands in the multi statement. I
think this is not only more intuitive than v10's behavior but it meets
the original reporter's expectation.

SET STATEMENT_TIMEOUT = '10s';
SET
Time: 0.418 ms
-- SELECT timeout in 1 second.
SET STATEMENT_TIMEOUT = '1s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 
ERROR:  canceling statement due to statement timeout
Time: 1001.107 ms (00:01.001)

Here are results of test cases attached (statement_timeout.sql).

\timing
Timing is on.
-- SELECT timeout in 1 second.
SET STATEMENT_TIMEOUT = '1s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 
psql:/home/t-ishii/tmp/statement_timeout.sql:3: ERROR:  canceling statement due to statement timeout
Time: 1001.138 ms (00:01.001)
SET STATEMENT_TIMEOUT = '10s';
SET
Time: 0.434 ms
-- SELECT timeout in 1 second.
SET STATEMENT_TIMEOUT = '1s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 
psql:/home/t-ishii/tmp/statement_timeout.sql:7: ERROR:  canceling statement due to statement timeout
Time: 1000.978 ms (00:01.001)
-- This time SELECT succeeds and STATEMENT_TIMEOUT is set to 8 secinds.
SET STATEMENT_TIMEOUT = '8s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 
 ?column? 
----------
        1
(1 row)

Time: 5004.886 ms (00:05.005)
SHOW STATEMENT_TIMEOUT;
 statement_timeout 
-------------------
 8s
(1 row)

Time: 0.433 ms
-- Following SELECT succeeds because now statement_timeout value is 8 seconds..
SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM test ) AS sub;
 ?column? 
----------
        1
(1 row)

Time: 5006.196 ms (00:05.006)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index e8d8e6f828..0ba6fa9d5e 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4701,7 +4701,8 @@ enable_statement_timeout(void)
 
     if (StatementTimeout > 0)
     {
-        if (!stmt_timeout_active)
+        if (!doing_extended_query_message ||
+            (!stmt_timeout_active && doing_extended_query_message))
         {
             enable_timeout_after(STATEMENT_TIMEOUT, StatementTimeout);
             stmt_timeout_active = true;
\timing
-- SELECT timeout in 1 second.
SET STATEMENT_TIMEOUT = '1s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 

SET STATEMENT_TIMEOUT = '10s';
-- SELECT timeout in 1 second.
SET STATEMENT_TIMEOUT = '1s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 

-- This time SELECT succeeds and STATEMENT_TIMEOUT is set to 8 secinds.
SET STATEMENT_TIMEOUT = '8s'\;SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM
test) AS sub;
 
SHOW STATEMENT_TIMEOUT;

-- Following SELECT succeeds because now statement_timeout value is 8 seconds..
SELECT * FROM ( WITH test AS ( SELECT pg_sleep(5), 'Billy' as emp_name ) SELECT 1 FROM test ) AS sub;

pgsql-bugs by date:

Previous
From: Michael Paquier
Date:
Subject: Re: BUG #16039: PANIC when activating replication slots in Postgres12.0 64bit under Windows
Next
From: Andrew Gierth
Date:
Subject: Re: BUG #16031: Group by returns duplicate groups