Skip to content

Commit 51efe38

Browse files
akorotkovakorotkov
committed
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 <[email protected]> Author: Japin Li <[email protected]> Author: Junwang Zhao <[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]>
1 parent 5c9f2f9 commit 51efe38

File tree

23 files changed

+343
-5
lines changed

23 files changed

+343
-5
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9140,6 +9140,42 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
91409140
</listitem>
91419141
</varlistentry>
91429142

9143+
<varlistentry id="guc-transaction-timeout" xreflabel="transaction_timeout">
9144+
<term><varname>transaction_timeout</varname> (<type>integer</type>)
9145+
<indexterm>
9146+
<primary><varname>transaction_timeout</varname> configuration parameter</primary>
9147+
</indexterm>
9148+
</term>
9149+
<listitem>
9150+
<para>
9151+
Terminate any session that spans longer than the specified amount of
9152+
time in the transaction. The limit applies both to explicit transactions
9153+
(started with <command>BEGIN</command>) and to an implicitly started
9154+
transaction corresponding to a single statement.
9155+
If this value is specified without units, it is taken as milliseconds.
9156+
A value of zero (the default) disables the timeout.
9157+
</para>
9158+
9159+
<para>
9160+
If <varname>transaction_timeout</varname> is shorter or equal to
9161+
<varname>idle_in_transaction_session_timeout</varname> or <varname>statement_timeout</varname>
9162+
<varname>transaction_timeout</varname> will invalidate the longer timeout.
9163+
</para>
9164+
9165+
<para>
9166+
Setting <varname>transaction_timeout</varname> in
9167+
<filename>postgresql.conf</filename> is not recommended because it would
9168+
affect all sessions.
9169+
</para>
9170+
9171+
<note>
9172+
<para>
9173+
Prepared transactions are not subject to this timeout.
9174+
</para>
9175+
</note>
9176+
</listitem>
9177+
</varlistentry>
9178+
91439179
<varlistentry id="guc-lock-timeout" xreflabel="lock_timeout">
91449180
<term><varname>lock_timeout</varname> (<type>integer</type>)
91459181
<indexterm>

‎src/backend/access/transam/xact.c‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2139,6 +2139,10 @@ StartTransaction(void)
21392139
*/
21402140
s->state = TRANS_INPROGRESS;
21412141

2142+
/* Schedule transaction timeout */
2143+
if (TransactionTimeout > 0)
2144+
enable_timeout_after(TRANSACTION_TIMEOUT, TransactionTimeout);
2145+
21422146
ShowTransactionState("StartTransaction");
21432147
}
21442148

‎src/backend/postmaster/autovacuum.c‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -586,6 +586,7 @@ AutoVacLauncherMain(int argc, char *argv[])
586586
* regular maintenance from being executed.
587587
*/
588588
SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
589+
SetConfigOption("transaction_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
589590
SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
590591
SetConfigOption("idle_in_transaction_session_timeout", "0",
591592
PGC_SUSET, PGC_S_OVERRIDE);
@@ -1587,6 +1588,7 @@ AutoVacWorkerMain(int argc, char *argv[])
15871588
* regular maintenance from being executed.
15881589
*/
15891590
SetConfigOption("statement_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
1591+
SetConfigOption("transaction_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
15901592
SetConfigOption("lock_timeout", "0", PGC_SUSET, PGC_S_OVERRIDE);
15911593
SetConfigOption("idle_in_transaction_session_timeout", "0",
15921594
PGC_SUSET, PGC_S_OVERRIDE);

‎src/backend/storage/lmgr/proc.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -59,6 +59,7 @@ int DeadlockTimeout = 1000;
5959
int StatementTimeout = 0;
6060
int LockTimeout = 0;
6161
int IdleInTransactionSessionTimeout = 0;
62+
int TransactionTimeout = 0;
6263
int IdleSessionTimeout = 0;
6364
bool log_lock_waits = false;
6465

‎src/backend/tcop/postgres.c‎

Lines changed: 43 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3418,6 +3418,17 @@ ProcessInterrupts(void)
34183418
IdleInTransactionSessionTimeoutPending = false;
34193419
}
34203420

3421+
if (TransactionTimeoutPending)
3422+
{
3423+
/* As above, ignore the signal if the GUC has been reset to zero. */
3424+
if (TransactionTimeout > 0)
3425+
ereport(FATAL,
3426+
(errcode(ERRCODE_TRANSACTION_TIMEOUT),
3427+
errmsg("terminating connection due to transaction timeout")));
3428+
else
3429+
TransactionTimeoutPending = false;
3430+
}
3431+
34213432
if (IdleSessionTimeoutPending)
34223433
{
34233434
/* 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)
36323643
return true;
36333644
}
36343645

3646+
/* GUC assign hook for transaction_timeout */
3647+
void
3648+
assign_transaction_timeout(int newval, void *extra)
3649+
{
3650+
if (TransactionTimeout <= 0 &&
3651+
get_timeout_active(TRANSACTION_TIMEOUT))
3652+
disable_timeout(TRANSACTION_TIMEOUT, false);
3653+
}
3654+
36353655

36363656
/*
36373657
* set_debug_options --- apply "-d N" command line option
@@ -4483,25 +4503,37 @@ PostgresMain(const char *dbname, const char *username)
44834503
pgstat_report_activity(STATE_IDLEINTRANSACTION_ABORTED, NULL);
44844504

44854505
/* Start the idle-in-transaction timer */
4486-
if (IdleInTransactionSessionTimeout > 0)
4506+
if (IdleInTransactionSessionTimeout > 0
4507+
&& (IdleInTransactionSessionTimeout < TransactionTimeout || TransactionTimeout == 0))
44874508
{
44884509
idle_in_transaction_timeout_enabled = true;
44894510
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
44904511
IdleInTransactionSessionTimeout);
44914512
}
4513+
4514+
/* Schedule or reschedule transaction timeout */
4515+
if (TransactionTimeout > 0 && !get_timeout_active(TRANSACTION_TIMEOUT))
4516+
enable_timeout_after(TRANSACTION_TIMEOUT,
4517+
TransactionTimeout);
44924518
}
44934519
else if (IsTransactionOrTransactionBlock())
44944520
{
44954521
set_ps_display("idle in transaction");
44964522
pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL);
44974523

44984524
/* Start the idle-in-transaction timer */
4499-
if (IdleInTransactionSessionTimeout > 0)
4525+
if (IdleInTransactionSessionTimeout > 0
4526+
&& (IdleInTransactionSessionTimeout < TransactionTimeout || TransactionTimeout == 0))
45004527
{
45014528
idle_in_transaction_timeout_enabled = true;
45024529
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
45034530
IdleInTransactionSessionTimeout);
45044531
}
4532+
4533+
/* Schedule or reschedule transaction timeout */
4534+
if (TransactionTimeout > 0 && !get_timeout_active(TRANSACTION_TIMEOUT))
4535+
enable_timeout_after(TRANSACTION_TIMEOUT,
4536+
TransactionTimeout);
45054537
}
45064538
else
45074539
{
@@ -4554,6 +4586,13 @@ PostgresMain(const char *dbname, const char *username)
45544586
enable_timeout_after(IDLE_SESSION_TIMEOUT,
45554587
IdleSessionTimeout);
45564588
}
4589+
4590+
/*
4591+
* If GUC is changed then it's handled in
4592+
* assign_transaction_timeout().
4593+
*/
4594+
if (TransactionTimeout > 0 && get_timeout_active(TRANSACTION_TIMEOUT))
4595+
disable_timeout(TRANSACTION_TIMEOUT, false);
45574596
}
45584597

45594598
/* Report any recently-changed GUC options */
@@ -5112,7 +5151,8 @@ enable_statement_timeout(void)
51125151
/* must be within an xact */
51135152
Assert(xact_started);
51145153

5115-
if (StatementTimeout > 0)
5154+
if (StatementTimeout > 0
5155+
&& (StatementTimeout < TransactionTimeout || TransactionTimeout == 0))
51165156
{
51175157
if (!get_timeout_active(STATEMENT_TIMEOUT))
51185158
enable_timeout_after(STATEMENT_TIMEOUT, StatementTimeout);

‎src/backend/utils/errcodes.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,7 @@ Section: Class 25 - Invalid Transaction State
252252
25P01 E ERRCODE_NO_ACTIVE_SQL_TRANSACTION no_active_sql_transaction
253253
25P02 E ERRCODE_IN_FAILED_SQL_TRANSACTION in_failed_sql_transaction
254254
25P03 E ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT idle_in_transaction_session_timeout
255+
25P04 E ERRCODE_TRANSACTION_TIMEOUT transaction_timeout
255256

256257
Section: Class 26 - Invalid SQL Statement Name
257258

‎src/backend/utils/init/globals.c‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ volatile sig_atomic_t ProcDiePending = false;
3333
volatile sig_atomic_t CheckClientConnectionPending = false;
3434
volatile sig_atomic_t ClientConnectionLost = false;
3535
volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false;
36+
volatile sig_atomic_t TransactionTimeoutPending = false;
3637
volatile sig_atomic_t IdleSessionTimeoutPending = false;
3738
volatile sig_atomic_t ProcSignalBarrierPending = false;
3839
volatile sig_atomic_t LogMemoryContextPending = false;

‎src/backend/utils/init/postinit.c‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,7 @@ static void ShutdownPostgres(int code, Datum arg);
7575
static void StatementTimeoutHandler(void);
7676
static void LockTimeoutHandler(void);
7777
static void IdleInTransactionSessionTimeoutHandler(void);
78+
static void TransactionTimeoutHandler(void);
7879
static void IdleSessionTimeoutHandler(void);
7980
static void IdleStatsUpdateTimeoutHandler(void);
8081
static void ClientCheckTimeoutHandler(void);
@@ -764,6 +765,7 @@ InitPostgres(const char *in_dbname, Oid dboid,
764765
RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
765766
RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
766767
IdleInTransactionSessionTimeoutHandler);
768+
RegisterTimeout(TRANSACTION_TIMEOUT, TransactionTimeoutHandler);
767769
RegisterTimeout(IDLE_SESSION_TIMEOUT, IdleSessionTimeoutHandler);
768770
RegisterTimeout(CLIENT_CONNECTION_CHECK_TIMEOUT, ClientCheckTimeoutHandler);
769771
RegisterTimeout(IDLE_STATS_UPDATE_TIMEOUT,
@@ -1395,6 +1397,14 @@ LockTimeoutHandler(void)
13951397
kill(MyProcPid, SIGINT);
13961398
}
13971399

1400+
static void
1401+
TransactionTimeoutHandler(void)
1402+
{
1403+
TransactionTimeoutPending = true;
1404+
InterruptPending = true;
1405+
SetLatch(MyLatch);
1406+
}
1407+
13981408
static void
13991409
IdleInTransactionSessionTimeoutHandler(void)
14001410
{

‎src/backend/utils/misc/guc_tables.c‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2577,6 +2577,17 @@ struct config_int ConfigureNamesInt[] =
25772577
NULL, NULL, NULL
25782578
},
25792579

2580+
{
2581+
{"transaction_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
2582+
gettext_noop("Sets the maximum allowed time in a transaction with a session (not a prepared transaction)."),
2583+
gettext_noop("A value of 0 turns off the timeout."),
2584+
GUC_UNIT_MS
2585+
},
2586+
&TransactionTimeout,
2587+
0, 0, INT_MAX,
2588+
NULL, assign_transaction_timeout, NULL
2589+
},
2590+
25802591
{
25812592
{"idle_session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
25822593
gettext_noop("Sets the maximum allowed idle time between queries, when not in a transaction."),

‎src/backend/utils/misc/postgresql.conf.sample‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -701,6 +701,7 @@
701701
#default_transaction_deferrable = off
702702
#session_replication_role = 'origin'
703703
#statement_timeout = 0 # in milliseconds, 0 is disabled
704+
#transaction_timeout = 0 # in milliseconds, 0 is disabled
704705
#lock_timeout = 0 # in milliseconds, 0 is disabled
705706
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
706707
#idle_session_timeout = 0 # in milliseconds, 0 is disabled

0 commit comments

Comments
 (0)