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 <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>
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

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3115,6 +3115,7 @@ _doSetFixedOutputState(ArchiveHandle *AH)
31153115
ahprintf(AH, "SET statement_timeout = 0;\n");
31163116
ahprintf(AH, "SET lock_timeout = 0;\n");
31173117
ahprintf(AH, "SET idle_in_transaction_session_timeout = 0;\n");
3118+
ahprintf(AH, "SET transaction_timeout = 0;\n");
31183119

31193120
/* Select the correct character set encoding */
31203121
ahprintf(AH, "SET client_encoding = '%s';\n",

‎src/bin/pg_dump/pg_dump.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1252,6 +1252,8 @@ setup_connection(Archive *AH, const char *dumpencoding,
12521252
ExecuteSqlStatement(AH, "SET lock_timeout = 0");
12531253
if (AH->remoteVersion >= 90600)
12541254
ExecuteSqlStatement(AH, "SET idle_in_transaction_session_timeout = 0");
1255+
if (AH->remoteVersion >= 170000)
1256+
ExecuteSqlStatement(AH, "SET transaction_timeout = 0");
12551257

12561258
/*
12571259
* Quote all identifiers, if requested.

‎src/bin/pg_rewind/libpq_source.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -117,6 +117,7 @@ init_libpq_conn(PGconn *conn)
117117
run_simple_command(conn, "SET statement_timeout = 0");
118118
run_simple_command(conn, "SET lock_timeout = 0");
119119
run_simple_command(conn, "SET idle_in_transaction_session_timeout = 0");
120+
run_simple_command(conn, "SET transaction_timeout = 0");
120121

121122
/*
122123
* we don't intend to do any updates, put the connection in read-only mode

‎src/include/miscadmin.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,7 @@ extern PGDLLIMPORT volatile sig_atomic_t InterruptPending;
9191
extern PGDLLIMPORT volatile sig_atomic_t QueryCancelPending;
9292
extern PGDLLIMPORT volatile sig_atomic_t ProcDiePending;
9393
extern PGDLLIMPORT volatile sig_atomic_t IdleInTransactionSessionTimeoutPending;
94+
extern PGDLLIMPORT volatile sig_atomic_t TransactionTimeoutPending;
9495
extern PGDLLIMPORT volatile sig_atomic_t IdleSessionTimeoutPending;
9596
extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
9697
extern PGDLLIMPORT volatile sig_atomic_t LogMemoryContextPending;

‎src/include/storage/proc.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,7 @@ extern PGDLLIMPORT int DeadlockTimeout;
429429
extern PGDLLIMPORT int StatementTimeout;
430430
extern PGDLLIMPORT int LockTimeout;
431431
extern PGDLLIMPORT int IdleInTransactionSessionTimeout;
432+
extern PGDLLIMPORT int TransactionTimeout;
432433
extern PGDLLIMPORT int IdleSessionTimeout;
433434
extern PGDLLIMPORT bool log_lock_waits;
434435

‎src/include/utils/guc_hooks.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -155,6 +155,7 @@ extern void assign_timezone_abbreviations(const char *newval, void *extra);
155155
extern bool check_transaction_deferrable(bool *newval, void **extra, GucSource source);
156156
extern bool check_transaction_isolation(int *newval, void **extra, GucSource source);
157157
extern bool check_transaction_read_only(bool *newval, void **extra, GucSource source);
158+
extern void assign_transaction_timeout(int newval, void *extra);
158159
extern const char *show_unix_socket_permissions(void);
159160
extern bool check_wal_buffers(int *newval, void **extra, GucSource source);
160161
extern bool check_wal_consistency_checking(char **newval, void **extra,

‎src/include/utils/timeout.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,7 @@ typedef enum TimeoutId
3131
STANDBY_TIMEOUT,
3232
STANDBY_LOCK_TIMEOUT,
3333
IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
34+
TRANSACTION_TIMEOUT,
3435
IDLE_SESSION_TIMEOUT,
3536
IDLE_STATS_UPDATE_TIMEOUT,
3637
CLIENT_CONNECTION_CHECK_TIMEOUT,

‎src/test/isolation/Makefile

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,3 +72,6 @@ installcheck-prepared-txns: all temp-install
7272

7373
check-prepared-txns: all temp-install
7474
$(pg_isolation_regress_check) --schedule=$(srcdir)/isolation_schedule prepared-transactions prepared-transactions-cic
75+
76+
check-timeouts: all temp-install
77+
$(pg_isolation_regress_check) timeouts timeouts-long

‎src/test/isolation/expected/timeouts-long.out

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
Parsed test spec with 3 sessions
2+
3+
starting permutation: s7_begin s7_sleep s7_commit_and_chain s7_sleep s7_check s7_abort
4+
step s7_begin:
5+
BEGIN ISOLATION LEVEL READ COMMITTED;
6+
SET transaction_timeout = '1s';
7+
8+
step s7_sleep: SELECT pg_sleep(0.6);
9+
pg_sleep
10+
--------
11+
12+
(1 row)
13+
14+
step s7_commit_and_chain: COMMIT AND CHAIN;
15+
step s7_sleep: SELECT pg_sleep(0.6);
16+
pg_sleep
17+
--------
18+
19+
(1 row)
20+
21+
step s7_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s7';
22+
count
23+
-----
24+
0
25+
(1 row)
26+
27+
step s7_abort: ABORT;
28+
29+
starting permutation: s8_begin s8_sleep s8_select_1 s8_check checker_sleep checker_sleep s8_check
30+
step s8_begin:
31+
BEGIN ISOLATION LEVEL READ COMMITTED;
32+
SET transaction_timeout = '900ms';
33+
34+
step s8_sleep: SELECT pg_sleep(0.6);
35+
pg_sleep
36+
--------
37+
38+
(1 row)
39+
40+
step s8_select_1: SELECT 1;
41+
?column?
42+
--------
43+
1
44+
(1 row)
45+
46+
step s8_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s8';
47+
count
48+
-----
49+
0
50+
(1 row)
51+
52+
step checker_sleep: SELECT pg_sleep(0.3);
53+
pg_sleep
54+
--------
55+
56+
(1 row)
57+
58+
step checker_sleep: SELECT pg_sleep(0.3);
59+
pg_sleep
60+
--------
61+
62+
(1 row)
63+
64+
step s8_check: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'isolation/timeouts/s8';
65+
count
66+
-----
67+
0
68+
(1 row)
69+

0 commit comments

Comments
 (0)