Introduce transaction_timeout

This commit adds timeout that is expected to be used as a prevention
of long-running queries. Any session within the transaction will be
terminated after spanning longer than this timeout.

However, this timeout is not applied to prepared transactions.
Only transactions with user connections are affected.

Discussion: https://postgr.es/m/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com
Author: Andrey Borodin <amborodin@acm.org>
Author: Japin Li <japinli@hotmail.com>
Author: Junwang Zhao <zhjwpku@gmail.com>
Reviewed-by: Nikolay Samokhvalov <samokhvalov@gmail.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com>
Reviewed-by: bt23nguyent <bt23nguyent@oss.nttdata.com>
Reviewed-by: Yuhang Qiu <iamqyh@gmail.com>

6 files changed, 225 insertions, 2 deletions

diff --git a/src/test/isolation/Makefile b/src/test/isolation/Makefile
index ade2256ed3a..91307e1a7e8 100644
--- a/src/test/isolation/Makefile
+++ b/src/test/isolation/Makefile
@@ -72,3 +72,6 @@ installcheck-prepared-txns: all temp-install
 
 check-prepared-txns: all temp-install
 	$(pg_isolation_regress_check) --schedule=$(srcdir)/isolation_schedule prepared-transactions prepared-transactions-cic
+
+check-timeouts: all temp-install
+	$(pg_isolation_regress_check) timeouts timeouts-long
diff --git a/src/test/isolation/expected/timeouts-long.out b/src/test/isolation/expected/timeouts-long.out
new file mode 100644
index 00000000000..26a6672c051
--- /dev/null
+++ b/src/test/isolation/expected/timeouts-long.out
@@ -0,0 +1,69 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s7_begin s7_sleep s7_commit_and_chain s7_sleep s7_check s7_abort
+step s7_begin: 
+    BEGIN ISOLATION LEVEL READ COMMITTED;
+    SET transaction_timeout = '1s';
+
+step s7_sleep: SELECT pg_sleep(0.6);
+pg_sleep
+--------
+        
+(1 row)
+
+step s7_commit_and_chain: COMMIT AND CHAIN;
+step s7_sleep: SELECT pg_sleep(0.6);
+pg_sleep
+--------
+        
+(1 row)
+
+step s7_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7';
+count
+-----
+    0
+(1 row)
+
+step s7_abort: ABORT;
+
+starting permutation: s8_begin s8_sleep s8_select_1 s8_check checker_sleep checker_sleep s8_check
+step s8_begin: 
+    BEGIN ISOLATION LEVEL READ COMMITTED;
+    SET transaction_timeout = '900ms';
+
+step s8_sleep: SELECT pg_sleep(0.6);
+pg_sleep
+--------
+        
+(1 row)
+
+step s8_select_1: SELECT 1;
+?column?
+--------
+       1
+(1 row)
+
+step s8_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s8';
+count
+-----
+    0
+(1 row)
+
+step checker_sleep: SELECT pg_sleep(0.3);
+pg_sleep
+--------
+        
+(1 row)
+
+step checker_sleep: SELECT pg_sleep(0.3);
+pg_sleep
+--------
+        
+(1 row)
+
+step s8_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s8';
+count
+-----
+    0
+(1 row)
+
diff --git a/src/test/isolation/expected/timeouts.out b/src/test/isolation/expected/timeouts.out
index 9328676f1cc..81a0016375b 100644
--- a/src/test/isolation/expected/timeouts.out
+++ b/src/test/isolation/expected/timeouts.out
@@ -1,4 +1,4 @@
-Parsed test spec with 2 sessions
+Parsed test spec with 7 sessions
 
 starting permutation: rdtbl sto locktbl
 step rdtbl: SELECT * FROM accounts;
@@ -79,3 +79,80 @@ step slto: SET lock_timeout = '10s'; SET statement_timeout = '10ms';
 step update: DELETE FROM accounts WHERE accountid = 'checking'; <waiting ...>
 step update: <... completed>
 ERROR:  canceling statement due to statement timeout
+
+starting permutation: stto s3_begin s3_sleep s3_check s3_abort
+step stto: SET statement_timeout = '10ms'; SET transaction_timeout = '1s';
+step s3_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s3_sleep: SELECT pg_sleep(0.1);
+ERROR:  canceling statement due to statement timeout
+step s3_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s3';
+count
+-----
+    1
+(1 row)
+
+step s3_abort: ABORT;
+
+starting permutation: tsto s3_begin checker_sleep s3_check
+step tsto: SET statement_timeout = '1s'; SET transaction_timeout = '10ms';
+step s3_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step checker_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+        
+(1 row)
+
+step s3_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s3';
+count
+-----
+    0
+(1 row)
+
+
+starting permutation: itto s4_begin checker_sleep s4_check
+step itto: SET idle_in_transaction_session_timeout = '10ms'; SET transaction_timeout = '1s';
+step s4_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step checker_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+        
+(1 row)
+
+step s4_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s4';
+count
+-----
+    0
+(1 row)
+
+
+starting permutation: tito s5_begin checker_sleep s5_check
+step tito: SET idle_in_transaction_session_timeout = '1s'; SET transaction_timeout = '10ms';
+step s5_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step checker_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+        
+(1 row)
+
+step s5_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s5';
+count
+-----
+    0
+(1 row)
+
+
+starting permutation: s6_begin s6_tt checker_sleep s6_check
+step s6_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s6_tt: SET statement_timeout = '1s'; SET transaction_timeout = '10ms';
+step checker_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+        
+(1 row)
+
+step s6_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s6';
+count
+-----
+    0
+(1 row)
+
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index b2be88ead1d..86ef62bbcf6 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -89,6 +89,7 @@ test: sequence-ddl
 test: async-notify
 test: vacuum-no-cleanup-lock
 test: timeouts
+test: timeouts-long
 test: vacuum-concurrent-drop
 test: vacuum-conflict
 test: vacuum-skip-locked
diff --git a/src/test/isolation/specs/timeouts-long.spec b/src/test/isolation/specs/timeouts-long.spec
new file mode 100644
index 00000000000..ce2c9a43011
--- /dev/null
+++ b/src/test/isolation/specs/timeouts-long.spec
@@ -0,0 +1,35 @@
+# Tests for transaction timeout that require long wait times
+
+session s7
+step s7_begin
+{
+    BEGIN ISOLATION LEVEL READ COMMITTED;
+    SET transaction_timeout = '1s';
+}
+step s7_commit_and_chain { COMMIT AND CHAIN; }
+step s7_sleep	{ SELECT pg_sleep(0.6); }
+step s7_abort	{ ABORT; }
+
+session s8
+step s8_begin
+{
+    BEGIN ISOLATION LEVEL READ COMMITTED;
+    SET transaction_timeout = '900ms';
+}
+# to test that quick query does not restart transaction_timeout
+step s8_select_1 { SELECT 1; }
+step s8_sleep	{ SELECT pg_sleep(0.6); }
+
+session checker
+step checker_sleep	{ SELECT pg_sleep(0.3); }
+step s7_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7'; }
+step s8_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s8'; }
+
+# COMMIT AND CHAIN must restart transaction timeout
+permutation s7_begin s7_sleep s7_commit_and_chain s7_sleep s7_check s7_abort
+# transaction timeout expires in presence of query flow, session s7 FATAL-out
+# this relatevely long sleeps are picked to ensure 300ms gap between check and timeouts firing
+# expected flow: timeouts is scheduled after s8_begin and fires approximately after checker_sleep (300ms before check)
+# possible buggy flow: timeout is schedules after s8_select_1 and fires 300ms after s8_check
+# to ensure this 300ms gap we need minimum transaction_timeout of 300ms
+permutation s8_begin s8_sleep s8_select_1 s8_check checker_sleep checker_sleep s8_check
diff --git a/src/test/isolation/specs/timeouts.spec b/src/test/isolation/specs/timeouts.spec
index c747b4ae28d..c2cc5d8d37b 100644
--- a/src/test/isolation/specs/timeouts.spec
+++ b/src/test/isolation/specs/timeouts.spec
@@ -1,4 +1,4 @@
-# Simple tests for statement_timeout and lock_timeout features
+# Simple tests for statement_timeout, lock_timeout and transaction_timeout features
 
 setup
 {
@@ -27,6 +27,33 @@ step locktbl	{ LOCK TABLE accounts; }
 step update	{ DELETE FROM accounts WHERE accountid = 'checking'; }
 teardown	{ ABORT; }
 
+session s3
+step s3_begin	{ BEGIN ISOLATION LEVEL READ COMMITTED; }
+step stto	{ SET statement_timeout = '10ms'; SET transaction_timeout = '1s'; }
+step tsto	{ SET statement_timeout = '1s'; SET transaction_timeout = '10ms'; }
+step s3_sleep	{ SELECT pg_sleep(0.1); }
+step s3_abort	{ ABORT; }
+
+session s4
+step s4_begin	{ BEGIN ISOLATION LEVEL READ COMMITTED; }
+step itto	{ SET idle_in_transaction_session_timeout = '10ms'; SET transaction_timeout = '1s'; }
+
+session s5
+step s5_begin	{ BEGIN ISOLATION LEVEL READ COMMITTED; }
+step tito	{ SET idle_in_transaction_session_timeout = '1s'; SET transaction_timeout = '10ms'; }
+
+session s6
+step s6_begin	{ BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s6_tt	{ SET statement_timeout = '1s'; SET transaction_timeout = '10ms'; }
+
+session checker
+step checker_sleep	{ SELECT pg_sleep(0.1); }
+step s3_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s3'; }
+step s4_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s4'; }
+step s5_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s5'; }
+step s6_check	{ SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s6'; }
+
+
 # It's possible that the isolation tester will not observe the final
 # steps as "waiting", thanks to the relatively short timeouts we use.
 # We can ensure consistent test output by marking those steps with (*).
@@ -47,3 +74,14 @@ permutation wrtbl lto update(*)
 permutation wrtbl lsto update(*)
 # statement timeout expires first, row-level lock
 permutation wrtbl slto update(*)
+
+# statement timeout expires first
+permutation stto s3_begin s3_sleep s3_check s3_abort
+# transaction timeout expires first, session s3 FATAL-out
+permutation tsto s3_begin checker_sleep s3_check
+# idle in transaction timeout expires first, session s4 FATAL-out
+permutation itto s4_begin checker_sleep s4_check
+# transaction timeout expires first, session s5 FATAL-out
+permutation tito s5_begin checker_sleep s5_check
+# transaction timeout can be schedule amid transaction, session s6 FATAL-out
+permutation s6_begin s6_tt checker_sleep s6_check
\ No newline at end of file