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>

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 037a3b8a64c8b3de324481793649b7327d4752ae..ffd711b7f21839b874cae0de2d8af254d32eb78f 100644 (file)
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9140,6 +9140,42 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-transaction-timeout" xreflabel="transaction_timeout">
+      <term><varname>transaction_timeout</varname> (<type>integer</type>)
+      <indexterm>
+       <primary><varname>transaction_timeout</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Terminate any session that spans longer than the specified amount of
+        time in the transaction. The limit applies both to explicit transactions
+        (started with <command>BEGIN</command>) and to an implicitly started
+        transaction corresponding to a single statement.
+        If this value is specified without units, it is taken as milliseconds.
+        A value of zero (the default) disables the timeout.
+       </para>
+
+       <para>
+        If <varname>transaction_timeout</varname> is shorter or equal to
+        <varname>idle_in_transaction_session_timeout</varname> or <varname>statement_timeout</varname>
+        <varname>transaction_timeout</varname> will invalidate the longer timeout.
+       </para>
+
+       <para>
+        Setting <varname>transaction_timeout</varname> in
+        <filename>postgresql.conf</filename> is not recommended because it would
+        affect all sessions.
+       </para>
+
+       <note>
+        <para>
+         Prepared transactions are not subject to this timeout.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-lock-timeout" xreflabel="lock_timeout">
       <term><varname>lock_timeout</varname> (<type>integer</type>)
       <indexterm>

diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 464858117e04b9fc4ec232ea84ffc9abb323668f..a124ba5933030293213b779a2e3ed5a3eb45fb59 100644 (file)
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2139,6 +2139,10 @@ StartTransaction(void)
     */
    s->state = TRANS_INPROGRESS;
 
+   /* Schedule transaction timeout */
+   if (TransactionTimeout > 0)
+       enable_timeout_after(TRANSACTION_TIMEOUT, TransactionTimeout);
+
    ShowTransactionState("StartTransaction");
 }
 

diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index c9ce380f0f1e5133abecc2be55da780e3dac22b6..37998f73871a5a1d7215fbd1603ec26c8c3c1bbf 100644 (file)
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -586,6 +586,7 @@ AutoVacLauncherMain(int argc, char *argv[])
     * regular maintenance from being executed.
     */
    SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+   SetConfigOption("transaction_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
    SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
    SetConfigOption("idle_in_transaction_session_timeout", "0",
                    PGC_SUSET, PGC_S_OVERRIDE);
@@ -1587,6 +1588,7 @@ AutoVacWorkerMain(int argc, char *argv[])
     * regular maintenance from being executed.
     */
    SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
+   SetConfigOption("transaction_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
    SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
    SetConfigOption("idle_in_transaction_session_timeout", "0",
                    PGC_SUSET, PGC_S_OVERRIDE);

diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index e5977548fe2e37537013f2feb9034a7ca760a811..1afcbfc052cefa4e2555c3c49d66f6ac9f8a6df6 100644 (file)
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -59,6 +59,7 @@ int           DeadlockTimeout = 1000;
 int            StatementTimeout = 0;
 int            LockTimeout = 0;
 int            IdleInTransactionSessionTimeout = 0;
+int            TransactionTimeout = 0;
 int            IdleSessionTimeout = 0;
 bool       log_lock_waits = false;
 

diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 01b5530f0b1e8813e5807dd744475e8e0d8e1f09..de9f5d1a6c455ad9635712b15db4f9caaeb3604d 100644 (file)
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -3418,6 +3418,17 @@ ProcessInterrupts(void)
            IdleInTransactionSessionTimeoutPending = false;
    }
 
+   if (TransactionTimeoutPending)
+   {
+       /* As above, ignore the signal if the GUC has been reset to zero. */
+       if (TransactionTimeout > 0)
+           ereport(FATAL,
+                   (errcode(ERRCODE_TRANSACTION_TIMEOUT),
+                    errmsg("terminating connection due to transaction timeout")));
+       else
+           TransactionTimeoutPending = false;
+   }
+
    if (IdleSessionTimeoutPending)
    {
        /* As above, ignore the signal if the GUC has been reset to zero. */
@@ -3632,6 +3643,15 @@ check_log_stats(bool *newval, void **extra, GucSource source)
    return true;
 }
 
+/* GUC assign hook for transaction_timeout */
+void
+assign_transaction_timeout(int newval, void *extra)
+{
+   if (TransactionTimeout <= 0 &&
+       get_timeout_active(TRANSACTION_TIMEOUT))
+       disable_timeout(TRANSACTION_TIMEOUT, false);
+}
+
 
 /*
  * set_debug_options --- apply "-d N" command line option
@@ -4483,12 +4503,18 @@ PostgresMain(const char *dbname, const char *username)
                pgstat_report_activity(STATE_IDLEINTRANSACTION_ABORTED, NULL);
 
                /* Start the idle-in-transaction timer */
-               if (IdleInTransactionSessionTimeout > 0)
+               if (IdleInTransactionSessionTimeout > 0
+                   && (IdleInTransactionSessionTimeout < TransactionTimeout || TransactionTimeout == 0))
                {
                    idle_in_transaction_timeout_enabled = true;
                    enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
                                         IdleInTransactionSessionTimeout);
                }
+
+               /* Schedule or reschedule transaction timeout */
+               if (TransactionTimeout > 0 && !get_timeout_active(TRANSACTION_TIMEOUT))
+                   enable_timeout_after(TRANSACTION_TIMEOUT,
+                                        TransactionTimeout);
            }
            else if (IsTransactionOrTransactionBlock())
            {
@@ -4496,12 +4522,18 @@ PostgresMain(const char *dbname, const char *username)
                pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL);
 
                /* Start the idle-in-transaction timer */
-               if (IdleInTransactionSessionTimeout > 0)
+               if (IdleInTransactionSessionTimeout > 0
+                   && (IdleInTransactionSessionTimeout < TransactionTimeout || TransactionTimeout == 0))
                {
                    idle_in_transaction_timeout_enabled = true;
                    enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
                                         IdleInTransactionSessionTimeout);
                }
+
+               /* Schedule or reschedule transaction timeout */
+               if (TransactionTimeout > 0 && !get_timeout_active(TRANSACTION_TIMEOUT))
+                   enable_timeout_after(TRANSACTION_TIMEOUT,
+                                        TransactionTimeout);
            }
            else
            {
@@ -4554,6 +4586,13 @@ PostgresMain(const char *dbname, const char *username)
                    enable_timeout_after(IDLE_SESSION_TIMEOUT,
                                         IdleSessionTimeout);
                }
+
+               /*
+                * If GUC is changed then it's handled in
+                * assign_transaction_timeout().
+                */
+               if (TransactionTimeout > 0 && get_timeout_active(TRANSACTION_TIMEOUT))
+                   disable_timeout(TRANSACTION_TIMEOUT, false);
            }
 
            /* Report any recently-changed GUC options */
@@ -5112,7 +5151,8 @@ enable_statement_timeout(void)
    /* must be within an xact */
    Assert(xact_started);
 
-   if (StatementTimeout > 0)
+   if (StatementTimeout > 0
+       && (StatementTimeout < TransactionTimeout || TransactionTimeout == 0))
    {
        if (!get_timeout_active(STATEMENT_TIMEOUT))
            enable_timeout_after(STATEMENT_TIMEOUT, StatementTimeout);

diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index 29f367a5e1c1c74a3ba049e696951083d3a77f69..3250d539e1c467af6431283baf7ee1fa2704f55a 100644 (file)
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -252,6 +252,7 @@ Section: Class 25 - Invalid Transaction State
 25P01    E    ERRCODE_NO_ACTIVE_SQL_TRANSACTION                              no_active_sql_transaction
 25P02    E    ERRCODE_IN_FAILED_SQL_TRANSACTION                              in_failed_sql_transaction
 25P03    E    ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT                    idle_in_transaction_session_timeout
+25P04    E    ERRCODE_TRANSACTION_TIMEOUT                                    transaction_timeout
 
 Section: Class 26 - Invalid SQL Statement Name
 

diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 88b03e8fa3c95cdb4acc6478fbfafddd354299aa..f024b1a84970d602c0f5192fcfa23fc68273aea3 100644 (file)
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -33,6 +33,7 @@ volatile sig_atomic_t ProcDiePending = false;
 volatile sig_atomic_t CheckClientConnectionPending = false;
 volatile sig_atomic_t ClientConnectionLost = false;
 volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false;
+volatile sig_atomic_t TransactionTimeoutPending = false;
 volatile sig_atomic_t IdleSessionTimeoutPending = false;
 volatile sig_atomic_t ProcSignalBarrierPending = false;
 volatile sig_atomic_t LogMemoryContextPending = false;

diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c
index 1ad3367159844343fd016996f751dd5c82bb0635..7797876d008110aab795a85d92b8e6af0c5293a9 100644 (file)
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -75,6 +75,7 @@ static void ShutdownPostgres(int code, Datum arg);
 static void StatementTimeoutHandler(void);
 static void LockTimeoutHandler(void);
 static void IdleInTransactionSessionTimeoutHandler(void);
+static void TransactionTimeoutHandler(void);
 static void IdleSessionTimeoutHandler(void);
 static void IdleStatsUpdateTimeoutHandler(void);
 static void ClientCheckTimeoutHandler(void);
@@ -764,6 +765,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
        RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
        RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
                        IdleInTransactionSessionTimeoutHandler);
+       RegisterTimeout(TRANSACTION_TIMEOUT, TransactionTimeoutHandler);
        RegisterTimeout(IDLE_SESSION_TIMEOUT, IdleSessionTimeoutHandler);
        RegisterTimeout(CLIENT_CONNECTION_CHECK_TIMEOUT, ClientCheckTimeoutHandler);
        RegisterTimeout(IDLE_STATS_UPDATE_TIMEOUT,
@@ -1395,6 +1397,14 @@ LockTimeoutHandler(void)
    kill(MyProcPid, SIGINT);
 }
 
+static void
+TransactionTimeoutHandler(void)
+{
+   TransactionTimeoutPending = true;
+   InterruptPending = true;
+   SetLatch(MyLatch);
+}
+
 static void
 IdleInTransactionSessionTimeoutHandler(void)
 {

diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 7fe58518d7da9493b11fc0f1bf620c799ba721d2..70652f0a3fc7f44b4273cbd2d876ad8abf4cf508 100644 (file)
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2577,6 +2577,17 @@ struct config_int ConfigureNamesInt[] =
        NULL, NULL, NULL
    },
 
+   {
+       {"transaction_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
+           gettext_noop("Sets the maximum allowed time in a transaction with a session (not a prepared transaction)."),
+           gettext_noop("A value of 0 turns off the timeout."),
+           GUC_UNIT_MS
+       },
+       &TransactionTimeout,
+       0, 0, INT_MAX,
+       NULL, assign_transaction_timeout, NULL
+   },
+
    {
        {"idle_session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
            gettext_noop("Sets the maximum allowed idle time between queries, when not in a transaction."),

diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 770118ad5e37c06ca8feb74338dba6eb154019db..e10755972aeebeba5e158c13d49cdc06493eb3c3 100644 (file)
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -701,6 +701,7 @@
 #default_transaction_deferrable = off
 #session_replication_role = 'origin'
 #statement_timeout = 0             # in milliseconds, 0 is disabled
+#transaction_timeout = 0           # in milliseconds, 0 is disabled
 #lock_timeout = 0              # in milliseconds, 0 is disabled
 #idle_in_transaction_session_timeout = 0   # in milliseconds, 0 is disabled
 #idle_session_timeout = 0          # in milliseconds, 0 is disabled

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 256d1e35a4ecc61c8df648a3dd349d3809d1d074..d97ebaff5b818a5152ec857e60a89ad0fa088910 100644 (file)
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3115,6 +3115,7 @@ _doSetFixedOutputState(ArchiveHandle *AH)
    ahprintf(AH, "SET statement_timeout = 0;\n");
    ahprintf(AH, "SET lock_timeout = 0;\n");
    ahprintf(AH, "SET idle_in_transaction_session_timeout = 0;\n");
+   ahprintf(AH, "SET transaction_timeout = 0;\n");
 
    /* Select the correct character set encoding */
    ahprintf(AH, "SET client_encoding = '%s';\n",

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f40bc759c5c81053e93e30e8fb9d180578fc30a4..2225a12718beb6d32b0ee034b7e424b750b36f3b 100644 (file)
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1252,6 +1252,8 @@ setup_connection(Archive *AH, const char *dumpencoding,
        ExecuteSqlStatement(AH, "SET lock_timeout = 0");
    if (AH->remoteVersion >= 90600)
        ExecuteSqlStatement(AH, "SET idle_in_transaction_session_timeout = 0");
+   if (AH->remoteVersion >= 170000)
+       ExecuteSqlStatement(AH, "SET transaction_timeout = 0");
 
    /*
     * Quote all identifiers, if requested.

diff --git a/src/bin/pg_rewind/libpq_source.c b/src/bin/pg_rewind/libpq_source.c
index 11347ab182463bb6bd8069f00d46a1b26f024b30..7d898c3b501c9058ab922be62b484aedf7198cfc 100644 (file)
--- a/src/bin/pg_rewind/libpq_source.c
+++ b/src/bin/pg_rewind/libpq_source.c
@@ -117,6 +117,7 @@ init_libpq_conn(PGconn *conn)
    run_simple_command(conn, "SET statement_timeout = 0");
    run_simple_command(conn, "SET lock_timeout = 0");
    run_simple_command(conn, "SET idle_in_transaction_session_timeout = 0");
+   run_simple_command(conn, "SET transaction_timeout = 0");
 
    /*
     * we don't intend to do any updates, put the connection in read-only mode

diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 0b01c1f093564557724c73369573a32fdff2ddc5..0445fbf61d72af01a6427a8cb7508361276f7214 100644 (file)
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -91,6 +91,7 @@ extern PGDLLIMPORT volatile sig_atomic_t InterruptPending;
 extern PGDLLIMPORT volatile sig_atomic_t QueryCancelPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcDiePending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleInTransactionSessionTimeoutPending;
+extern PGDLLIMPORT volatile sig_atomic_t TransactionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
 extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
 extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;

diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index 4bc226e36cdb719c478a95272cfd29096fc2dcd1..20d6fa652dc690a3770e57abdfcffe6f0713dbf1 100644 (file)
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -429,6 +429,7 @@ extern PGDLLIMPORT int DeadlockTimeout;
 extern PGDLLIMPORT int StatementTimeout;
 extern PGDLLIMPORT int LockTimeout;
 extern PGDLLIMPORT int IdleInTransactionSessionTimeout;
+extern PGDLLIMPORT int TransactionTimeout;
 extern PGDLLIMPORT int IdleSessionTimeout;
 extern PGDLLIMPORT bool log_lock_waits;
 

diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h
index 5300c44f3b0f677030c798a693522e14a2428521..339c490300e27a021d58fb63ce48c7d376a7f5a3 100644 (file)
--- a/src/include/utils/guc_hooks.h
+++ b/src/include/utils/guc_hooks.h
@@ -155,6 +155,7 @@ extern void assign_timezone_abbreviations(const char *newval, void *extra);
 extern bool check_transaction_deferrable(bool *newval, void **extra, GucSource source);
 extern bool check_transaction_isolation(int *newval, void **extra, GucSource source);
 extern bool check_transaction_read_only(bool *newval, void **extra, GucSource source);
+extern void assign_transaction_timeout(int newval, void *extra);
 extern const char *show_unix_socket_permissions(void);
 extern bool check_wal_buffers(int *newval, void **extra, GucSource source);
 extern bool check_wal_consistency_checking(char **newval, void **extra,

diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h
index 20e7cf72d0d1e1c629f7570b95b94f66e29dc224..a5d8f078246fcf4497b5bbf79978d398000f69dc 100644 (file)
--- a/src/include/utils/timeout.h
+++ b/src/include/utils/timeout.h
@@ -31,6 +31,7 @@ typedef enum TimeoutId
    STANDBY_TIMEOUT,
    STANDBY_LOCK_TIMEOUT,
    IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
+   TRANSACTION_TIMEOUT,
    IDLE_SESSION_TIMEOUT,
    IDLE_STATS_UPDATE_TIMEOUT,
    CLIENT_CONNECTION_CHECK_TIMEOUT,

diff --git a/src/test/isolation/Makefile b/src/test/isolation/Makefile
index ade2256ed3aa73146c9dda66187efd9aa9de0e3f..91307e1a7e8428baf56b24556acabd44beb80bde 100644 (file)
--- 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 (file)
index 0000000..26a6672
--- /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 9328676f1ccb5bcf2d85f516f187ea62d593a6f5..81a0016375bfccd4de7a43872d7f805dd42c9bba 100644 (file)
--- 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 b2be88ead1d2597c4b54805930ba8e9b310927b7..86ef62bbcf6feaa5af6025f7b23551c547498a99 100644 (file)
--- 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 (file)
index 0000000..ce2c9a4
--- /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 c747b4ae28dbf52ad81866695060d09654e183c9..c2cc5d8d37b604b79071aae91c79753dece9c0ca 100644 (file)
--- 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