public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey M. Borodin <[email protected]>
To: Junwang Zhao <[email protected]>
Cc: Li Japin <[email protected]>
Cc: 邱宇航 <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Nikolay Samokhvalov <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Transaction timeout
Date: Wed, 3 Jan 2024 11:39:44 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ@mail.gmail.com>
<SY8PR01MB9288F5CF03D38662BE59C628B68AA@SY8PR01MB9288.ausprd01.prod.outlook.com>
<[email protected]>
<SY8PR01MB9288335A3B81E8D6E121980FB68AA@SY8PR01MB9288.ausprd01.prod.outlook.com>
<[email protected]>
<ME3P282MB3166E33F193D2E7B0CDB633FB692A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<[email protected]>
<MEYP282MB316457B6810B840A012649AFB690A@MEYP282MB3164.AUSP282.PROD.OUTLOOK.COM>
<[email protected]>
<ME3P282MB316699E4C3A165983731076EB697A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<[email protected]>
<[email protected]>
<ME3P282MB316662848DEF947EE0B4B091B697A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<ME3P282MB3166FCD27CCC06C8E94134B5B694A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<CAEG8a3J4DY1VaSd-GHkN8VHXV0vGkO83-Y50OUyac+jObmFW+g@mail.gmail.com>
<ME3P282MB316662CE6ED67FB49C930BC9B694A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<CAEG8a3JAStzhgPpEL4KvhJz7+s_S2-39h1PRe6+WdWW7=xcOFA@mail.gmail.com>
<ME3P282MB31665F69A72FE8427EE8C49FB694A@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<CAEG8a3K8kD0mQDFzj=v0zV7h+m4J4TDBLkXSJJS3+VGWNvnDCQ@mail.gmail.com>
<ME3P282MB3166AB8DF0381F27C077AF70B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<ME3P282MB31663557EEF80E7CA97FBC32B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<ME3P282MB316691F6AD09F4F8EA5578C1B69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<CAEG8a3+nXPnceKAyFT12XBwDvEe3POvre9Z=h6j2cCiK+zL7vg@mail.gmail.com>
<ME3P282MB31664A5018CEC4782D2DA7DAB69BA@ME3P282MB3166.AUSP282.PROD.OUTLOOK.COM>
<[email protected]>
<CAEG8a3KDAWrCY5coVdfO6PNFbNJNgLOusNKE7advB8fy7_6jMw@mail.gmail.com>
<[email protected]>
<CAEG8a3J6mSPXgHq_aqC7yk4xq1e9XF9B1Q+QVBr48fzdw1uKqw@mail.gmail.com>
<[email protected]>
<[email protected]>
> On 1 Jan 2024, at 19:28, Andrey M. Borodin <[email protected]> wrote:
>
> 3. Check that timeout is not rescheduled by new queries (Nik's case)
The test of Nik's case was not stable enough together with COMMIT AND CHAIN. So I've separated these cases into different permutations.
Looking through CI logs it seems variation in sleeps and actual timeouts easily reach 30+ms. I'm not entirely sure we can reach 100% stable tests without too big timeouts.
Best regards, Andrey Borodin.
Attachments:
[application/octet-stream] v21-0001-Introduce-transaction_timeout.patch (19.8K, ../[email protected]/3-v21-0001-Introduce-transaction_timeout.patch)
download | inline diff:
From d9f6e4d7c7183fe6042f11d98270f707f87b9e97 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Sun, 3 Dec 2023 23:18:00 +0500
Subject: [PATCH v21 1/4] Introduce transaction_timeout
This commit adds timeout that is expected to be used as a prevention
of long-running queries. Any session within 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.
Author: Andrey Borodin <[email protected]>
Reviewed-by: Nikolay Samokhvalov <[email protected]>
Reviewed-by: Andres Freund <[email protected]>
Reviewed-by: Fujii Masao <[email protected]>
Reviewed-by: bt23nguyent <[email protected]>
Reviewed-by: Yuhang Qiu <[email protected]>
Reviewed-by: Japin Li <[email protected]>
Reviewed-by: Junwang Zhao <[email protected]>
Discussion: https://postgr.es/m/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com
---
doc/src/sgml/config.sgml | 35 ++++++++++++++++
src/backend/postmaster/autovacuum.c | 2 +
src/backend/storage/lmgr/proc.c | 1 +
src/backend/tcop/postgres.c | 27 ++++++++++--
src/backend/utils/errcodes.txt | 1 +
src/backend/utils/init/globals.c | 1 +
src/backend/utils/init/postinit.c | 10 +++++
src/backend/utils/misc/guc_tables.c | 11 +++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/pg_dump/pg_backup_archiver.c | 2 +
src/bin/pg_dump/pg_dump.c | 2 +
src/bin/pg_rewind/libpq_source.c | 1 +
src/include/miscadmin.h | 1 +
src/include/storage/proc.h | 1 +
src/include/utils/timeout.h | 1 +
src/test/isolation/expected/timeouts.out | 41 ++++++++++++++++++-
src/test/isolation/specs/timeouts.spec | 29 ++++++++++++-
17 files changed, 162 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f323bba018..0d849a11ce 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -9134,6 +9134,41 @@ 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 transaction. The limit applies both to explicit transactions
+ (started with <command>BEGIN</command>) and to implicitly started
+ transaction corresponding to single statement. But this limit is not
+ applied to prepared transactions.
+ 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 than
+ <varname>idle_in_transaction_session_timeout</varname> or <varname>statement_timeout</varname>
+ <varname>transaction_timeout</varname> will invalidate longer timeout.
+ </para>
+
+ <para>
+ Setting <varname>transaction_timeout</varname> in
+ <filename>postgresql.conf</filename> is not recommended because it would
+ affect all sessions.
+ </para>
+
+ <para>
+ Prepared transactions are not subject for this timeout.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-lock-timeout" xreflabel="lock_timeout">
<term><varname>lock_timeout</varname> (<type>integer</type>)
<indexterm>
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index b04fcfc8c8..e6fa1cfdc2 100644
--- 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);
@@ -1591,6 +1592,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 b6451d9d08..4be06c1e5d 100644
--- 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 7298a187d1..a2611cf8e6 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2745,6 +2745,10 @@ start_xact_command(void)
{
StartTransactionCommand();
+ /* Schedule or reschedule transaction timeout */
+ if (TransactionTimeout > 0)
+ enable_timeout_after(TRANSACTION_TIMEOUT, TransactionTimeout);
+
xact_started = true;
}
@@ -3426,6 +3430,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. */
@@ -4491,7 +4506,8 @@ 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,
@@ -4504,7 +4520,8 @@ 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,
@@ -4562,6 +4579,9 @@ PostgresMain(const char *dbname, const char *username)
enable_timeout_after(IDLE_SESSION_TIMEOUT,
IdleSessionTimeout);
}
+
+ if (get_timeout_active(TRANSACTION_TIMEOUT))
+ disable_timeout(TRANSACTION_TIMEOUT, false);
}
/* Report any recently-changed GUC options */
@@ -5120,7 +5140,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 8e97a0150f..8f1157afee 100644
--- 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 60bc1217fb..fd586c193c 100644
--- 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 552cf9d950..64be4de0c7 100644
--- 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 3945a92ddd..fcb214a04d 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2567,6 +2567,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 session (not a prepared transaction)."),
+ gettext_noop("A value of 0 turns off the timeout."),
+ GUC_UNIT_MS
+ },
+ &TransactionTimeout,
+ 0, 0, INT_MAX,
+ NULL, NULL, 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 b2809c711a..0b37117eb7 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -700,6 +700,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 256d1e35a4..3342971bd0 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3115,6 +3115,8 @@ _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");
+ // TODO: AB: do we need spacial handling for this?
+ 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 050a831226..39ca7e6d38 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1251,6 +1251,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 417c74cfef..9cda3f3667 100644
--- 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 74bc2f97cb..b2d0f84252 100644
--- 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 e87fd25d64..9dde9cbfdd 100644
--- 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/timeout.h b/src/include/utils/timeout.h
index 8a61853371..608a83d5a8 100644
--- 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/expected/timeouts.out b/src/test/isolation/expected/timeouts.out
index 9328676f1c..cabe28f2c8 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 6 sessions
starting permutation: rdtbl sto locktbl
step rdtbl: SELECT * FROM accounts;
@@ -79,3 +79,42 @@ 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: stt1_set stt1_begin sleep_here stt2_set stt2_begin sleep_there stt3_check_stt2 itt4_set itt4_begin sleep_there stt3_check_itt4
+step stt1_set: SET transaction_timeout = '1ms';
+step stt1_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step sleep_here: SELECT pg_sleep(1);
+FATAL: terminating connection due to transaction timeout
+server closed the connection unexpectedly
+ This probably means the server terminated abnormally
+ before or while processing the request.
+
+step stt2_set: SET transaction_timeout = '1ms';
+step stt2_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step sleep_there: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+
+(1 row)
+
+step stt3_check_stt2: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/stt2'
+count
+-----
+ 0
+(1 row)
+
+step itt4_set: SET idle_in_transaction_session_timeout = '1ms'; SET statement_timeout = '10s'; SET lock_timeout = '10s'; SET transaction_timeout = '10s';
+step itt4_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step sleep_there: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+
+(1 row)
+
+step stt3_check_itt4: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/itt4' <waiting ...>
+step stt3_check_itt4: <... completed>
+count
+-----
+ 0
+(1 row)
+
diff --git a/src/test/isolation/specs/timeouts.spec b/src/test/isolation/specs/timeouts.spec
index c747b4ae28..2772939b6b 100644
--- a/src/test/isolation/specs/timeouts.spec
+++ b/src/test/isolation/specs/timeouts.spec
@@ -18,7 +18,7 @@ step wrtbl { UPDATE accounts SET balance = balance + 100; }
teardown { ABORT; }
session s2
-setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
+setup { SET transaction_timeout = '10s'; SET idle_in_transaction_session_timeout = '10s'; BEGIN ISOLATION LEVEL READ COMMITTED; }
step sto { SET statement_timeout = '10ms'; }
step lto { SET lock_timeout = '10ms'; }
step lsto { SET lock_timeout = '10ms'; SET statement_timeout = '10s'; }
@@ -27,6 +27,29 @@ step locktbl { LOCK TABLE accounts; }
step update { DELETE FROM accounts WHERE accountid = 'checking'; }
teardown { ABORT; }
+session stt1
+# enable statement_timeout to check interaction
+setup { SET statement_timeout = '10s'; SET lock_timeout = '10s'; }
+step stt1_set { SET transaction_timeout = '1ms'; }
+step stt1_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step sleep_here { SELECT pg_sleep(1); }
+
+session stt2
+setup { SET statement_timeout = '10s'; SET lock_timeout = '10s'; }
+step stt2_set { SET transaction_timeout = '1ms'; }
+step stt2_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+# Session stt2 is terminated in the background. However, isolation tester needs a step to observe it.
+
+session stt3
+step sleep_there{ SELECT pg_sleep(0.1); }
+# Observe that stt2\itt4 died
+step stt3_check_stt2 { SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/stt2' }
+step stt3_check_itt4 { SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/itt4' }
+
+session itt4
+step itt4_set { SET idle_in_transaction_session_timeout = '1ms'; SET statement_timeout = '10s'; SET lock_timeout = '10s'; SET transaction_timeout = '10s'; }
+step itt4_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+
# 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 +70,7 @@ permutation wrtbl lto update(*)
permutation wrtbl lsto update(*)
# statement timeout expires first, row-level lock
permutation wrtbl slto update(*)
+
+# timeout of active query, idle transaction timeout
+permutation stt1_set stt1_begin sleep_here stt2_set stt2_begin sleep_there stt3_check_stt2 itt4_set itt4_begin sleep_there stt3_check_itt4(*)
+# can't run tests after this, sessions stt1, stt2, and itt4 are expected to FATAL-out
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v21-0002-Add-better-tests-for-transaction_timeout.patch (10.9K, ../[email protected]/5-v21-0002-Add-better-tests-for-transaction_timeout.patch)
download | inline diff:
From 1ed73bb8ee489483aac5522b417815d743141cc0 Mon Sep 17 00:00:00 2001
From: "Andrey M. Borodin" <[email protected]>
Date: Fri, 29 Dec 2023 14:54:02 +0500
Subject: [PATCH v21 2/4] Add better tests for transaction_timeout: 1. Check
COMMIT AND CHAIN 2. Check termination of active and idle queries 3. Check
timeout reschedult 4. Check that timeout is not rescheduled by new queries
---
src/test/isolation/Makefile | 3 +
src/test/isolation/expected/timeouts.out | 123 ++++++++++++++++++++---
src/test/isolation/specs/timeouts.spec | 74 +++++++++-----
3 files changed, 163 insertions(+), 37 deletions(-)
diff --git a/src/test/isolation/Makefile b/src/test/isolation/Makefile
index ade2256ed3..482bb31949 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
diff --git a/src/test/isolation/expected/timeouts.out b/src/test/isolation/expected/timeouts.out
index cabe28f2c8..a500e9ab91 100644
--- a/src/test/isolation/expected/timeouts.out
+++ b/src/test/isolation/expected/timeouts.out
@@ -1,4 +1,5 @@
-Parsed test spec with 6 sessions
+unused step name: s6_sleep
+Parsed test spec with 8 sessions
starting permutation: rdtbl sto locktbl
step rdtbl: SELECT * FROM accounts;
@@ -80,39 +81,133 @@ step update: DELETE FROM accounts WHERE accountid = 'checking'; <waiting ...>
step update: <... completed>
ERROR: canceling statement due to statement timeout
-starting permutation: stt1_set stt1_begin sleep_here stt2_set stt2_begin sleep_there stt3_check_stt2 itt4_set itt4_begin sleep_there stt3_check_itt4
-step stt1_set: SET transaction_timeout = '1ms';
-step stt1_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
-step sleep_here: SELECT pg_sleep(1);
+starting permutation: stto s3_begin s3_sleep s3_check s3_abort
+step stto: SET statement_timeout = '1ms'; 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 s3_sleep s3_check
+step tsto: SET statement_timeout = '1s'; SET transaction_timeout = '1ms';
+step s3_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
+step s3_sleep: SELECT pg_sleep(0.1);
FATAL: terminating connection due to transaction timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
-step stt2_set: SET transaction_timeout = '1ms';
-step stt2_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
-step sleep_there: SELECT pg_sleep(0.1);
+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 = '1ms'; 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 = '1ms';
+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 = '1ms';
+step checker_sleep: SELECT pg_sleep(0.1);
pg_sleep
--------
(1 row)
-step stt3_check_stt2: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/stt2'
+step s6_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s6';
count
-----
0
(1 row)
-step itt4_set: SET idle_in_transaction_session_timeout = '1ms'; SET statement_timeout = '10s'; SET lock_timeout = '10s'; SET transaction_timeout = '10s';
-step itt4_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
-step sleep_there: SELECT pg_sleep(0.1);
+
+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 = '150ms';
+
+step s7_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+
+(1 row)
+
+step s7_commit_and_chain: COMMIT AND CHAIN;
+step s7_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+
+(1 row)
+
+step s7_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7';
+count
+-----
+ 1
+(1 row)
+
+step s7_abort: ABORT;
+
+starting permutation: s7_begin s7_sleep s7_select_1 checker_sleep s7_check
+step s7_begin:
+ BEGIN ISOLATION LEVEL READ COMMITTED;
+ SET transaction_timeout = '150ms';
+
+step s7_sleep: SELECT pg_sleep(0.1);
+pg_sleep
+--------
+
+(1 row)
+
+step s7_select_1: SELECT 1;
+?column?
+--------
+ 1
+(1 row)
+
+step checker_sleep: SELECT pg_sleep(0.1);
pg_sleep
--------
(1 row)
-step stt3_check_itt4: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/itt4' <waiting ...>
-step stt3_check_itt4: <... completed>
+step s7_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7';
count
-----
0
diff --git a/src/test/isolation/specs/timeouts.spec b/src/test/isolation/specs/timeouts.spec
index 2772939b6b..dc47d4f362 100644
--- a/src/test/isolation/specs/timeouts.spec
+++ b/src/test/isolation/specs/timeouts.spec
@@ -18,7 +18,7 @@ step wrtbl { UPDATE accounts SET balance = balance + 100; }
teardown { ABORT; }
session s2
-setup { SET transaction_timeout = '10s'; SET idle_in_transaction_session_timeout = '10s'; BEGIN ISOLATION LEVEL READ COMMITTED; }
+setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
step sto { SET statement_timeout = '10ms'; }
step lto { SET lock_timeout = '10ms'; }
step lsto { SET lock_timeout = '10ms'; SET statement_timeout = '10s'; }
@@ -27,28 +27,45 @@ step locktbl { LOCK TABLE accounts; }
step update { DELETE FROM accounts WHERE accountid = 'checking'; }
teardown { ABORT; }
-session stt1
-# enable statement_timeout to check interaction
-setup { SET statement_timeout = '10s'; SET lock_timeout = '10s'; }
-step stt1_set { SET transaction_timeout = '1ms'; }
-step stt1_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
-step sleep_here { SELECT pg_sleep(1); }
+session s3
+step s3_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step stto { SET statement_timeout = '1ms'; SET transaction_timeout = '1s'; }
+step tsto { SET statement_timeout = '1s'; SET transaction_timeout = '1ms'; }
+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 = '1ms'; 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 = '1ms'; }
-session stt2
-setup { SET statement_timeout = '10s'; SET lock_timeout = '10s'; }
-step stt2_set { SET transaction_timeout = '1ms'; }
-step stt2_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
-# Session stt2 is terminated in the background. However, isolation tester needs a step to observe it.
+session s6
+step s6_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step s6_tt { SET statement_timeout = '1s'; SET transaction_timeout = '1ms'; }
+step s6_sleep { SELECT pg_sleep(0.1); }
-session stt3
-step sleep_there{ SELECT pg_sleep(0.1); }
-# Observe that stt2\itt4 died
-step stt3_check_stt2 { SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/stt2' }
-step stt3_check_itt4 { SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/itt4' }
+session s7
+step s7_begin
+{
+ BEGIN ISOLATION LEVEL READ COMMITTED;
+ SET transaction_timeout = '150ms';
+}
+step s7_commit_and_chain { COMMIT AND CHAIN; }
+# to test that quick query does not restart transaction_timeout
+step s7_select_1 { SELECT 1; }
+step s7_sleep { SELECT pg_sleep(0.1); }
+step s7_abort { ABORT; }
-session itt4
-step itt4_set { SET idle_in_transaction_session_timeout = '1ms'; SET statement_timeout = '10s'; SET lock_timeout = '10s'; SET transaction_timeout = '10s'; }
-step itt4_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
+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'; }
+step s7_check { SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7'; }
# It's possible that the isolation tester will not observe the final
# steps as "waiting", thanks to the relatively short timeouts we use.
@@ -71,6 +88,17 @@ permutation wrtbl lsto update(*)
# statement timeout expires first, row-level lock
permutation wrtbl slto update(*)
-# timeout of active query, idle transaction timeout
-permutation stt1_set stt1_begin sleep_here stt2_set stt2_begin sleep_there stt3_check_stt2 itt4_set itt4_begin sleep_there stt3_check_itt4(*)
-# can't run tests after this, sessions stt1, stt2, and itt4 are expected to FATAL-out
+# 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 s3_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
+# 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
+permutation s7_begin s7_sleep s7_select_1 checker_sleep s7_check
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v21-0003-Try-to-enable-transaction_timeout-before-next-co.patch (1.4K, ../[email protected]/7-v21-0003-Try-to-enable-transaction_timeout-before-next-co.patch)
download | inline diff:
From 86b4d6f4df132da83fc4d754b54eaefe17c303ee Mon Sep 17 00:00:00 2001
From: japinli <[email protected]>
Date: Sat, 23 Dec 2023 11:04:25 +0800
Subject: [PATCH v21 3/4] Try to enable transaction_timeout before next command
---
src/backend/tcop/postgres.c | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index a2611cf8e6..96161eb7ab 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4513,6 +4513,11 @@ PostgresMain(const char *dbname, const char *username)
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())
{
@@ -4527,6 +4532,11 @@ PostgresMain(const char *dbname, const char *username)
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
{
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v21-0004-fix-reschedule-timeout-for-each-commmand.patch (1.3K, ../[email protected]/9-v21-0004-fix-reschedule-timeout-for-each-commmand.patch)
download | inline diff:
From f7d67812410678c8f547c1f2038817f4426f0516 Mon Sep 17 00:00:00 2001
From: Zhao Junwang <[email protected]>
Date: Fri, 29 Dec 2023 18:41:24 +0800
Subject: [PATCH v21 4/4] fix reschedule timeout for each commmand
Signed-off-by: Zhao Junwang <[email protected]>
---
src/backend/access/transam/xact.c | 4 ++++
src/backend/tcop/postgres.c | 4 ----
2 files changed, 4 insertions(+), 4 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 8442c5e6a7..2d9b718762 100644
--- 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/tcop/postgres.c b/src/backend/tcop/postgres.c
index 96161eb7ab..36b9e3f8c5 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2745,10 +2745,6 @@ start_xact_command(void)
{
StartTransactionCommand();
- /* Schedule or reschedule transaction timeout */
- if (TransactionTimeout > 0)
- enable_timeout_after(TRANSACTION_TIMEOUT, TransactionTimeout);
-
xact_started = true;
}
--
2.37.1 (Apple Git-137.1)
view thread (61+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Transaction timeout
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox