1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
|
@@ -8310,15 +8310,52 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</term>
<listitem>
<para>
- Terminate any session with an open transaction that has been idle for
- longer than the specified amount of time. This allows any
- locks held by that session to be released and the connection slot to be reused;
- it also allows tuples visible only to this transaction to be vacuumed. See
- <xref linkend="routine-vacuuming"/> for more details about this.
+ Terminate any session that has been idle (that is, waiting for a
+ client query) within an open transaction for longer than the
+ specified amount of time.
+ If this value is specified without units, it is taken as milliseconds.
+ A value of zero (the default) disables the timeout.
+ </para>
+
+ <para>
+ This option can be used to ensure that idle sessions do not hold
+ locks for an unreasonable amount of time. Even when no significant
+ locks are held, an open transaction prevents vacuuming away
+ recently-dead tuples that may be visible only to this transaction;
+ so remaining idle for a long time can contribute to table bloat.
+ See <xref linkend="routine-vacuuming"/> for more details.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-idle-session-timeout" xreflabel="idle_session_timeout">
+ <term><varname>idle_session_timeout</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>idle_session_timeout</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Terminate any session that has been idle (that is, waiting for a
+ client query), but not within an open transaction, for longer than
+ the specified amount of time.
+ If this value is specified without units, it is taken as milliseconds.
+ A value of zero (the default) disables the timeout.
</para>
+
+ <para>
+ Unlike the case with an open transaction, an idle session without a
+ transaction imposes no large costs on the server, so there is less
+ need to enable this timeout
+ than <varname>idle_in_transaction_session_timeout</varname>.
+ </para>
+
<para>
- If this value is specified without units, it is taken as milliseconds.
- A value of zero (the default) disables the timeout.
+ Be wary of enforcing this timeout on connections made through
+ connection-pooling software or other middleware, as such a layer
+ may not react well to unexpected connection closure. It may be
+ helpful to enable this timeout only for interactive sessions,
+ perhaps by applying it only to particular users.
</para>
</listitem>
</varlistentry>
@@ -61,6 +61,7 @@ int DeadlockTimeout = 1000;
int StatementTimeout = 0;
int LockTimeout = 0;
int IdleInTransactionSessionTimeout = 0;
+int IdleSessionTimeout = 0;
bool log_lock_waits = false;
/* Pointer to this process's PGPROC struct, if any */
@@ -3242,14 +3242,28 @@ ProcessInterrupts(void)
if (IdleInTransactionSessionTimeoutPending)
{
- /* Has the timeout setting changed since last we looked? */
+ /*
+ * If the GUC has been reset to zero, ignore the signal. This is
+ * important because the GUC update itself won't disable any pending
+ * interrupt.
+ */
if (IdleInTransactionSessionTimeout > 0)
ereport(FATAL,
(errcode(ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT),
errmsg("terminating connection due to idle-in-transaction timeout")));
else
IdleInTransactionSessionTimeoutPending = false;
+ }
+ if (IdleSessionTimeoutPending)
+ {
+ /* As above, ignore the signal if the GUC has been reset to zero. */
+ if (IdleSessionTimeout > 0)
+ ereport(FATAL,
+ (errcode(ERRCODE_IDLE_SESSION_TIMEOUT),
+ errmsg("terminating connection due to idle-session timeout")));
+ else
+ IdleSessionTimeoutPending = false;
}
if (ProcSignalBarrierPending)
@@ -3826,7 +3840,8 @@ PostgresMain(int argc, char *argv[],
StringInfoData input_message;
sigjmp_buf local_sigjmp_buf;
volatile bool send_ready_for_query = true;
- bool disable_idle_in_transaction_timeout = false;
+ bool idle_in_transaction_timeout_enabled = false;
+ bool idle_session_timeout_enabled = false;
/* Initialize startup process environment if necessary. */
if (!IsUnderPostmaster)
@@ -4228,6 +4243,8 @@ PostgresMain(int argc, char *argv[],
* processing of batched messages, and because we don't want to report
* uncommitted updates (that confuses autovacuum). The notification
* processor wants a call too, if we are not in a transaction block.
+ *
+ * Also, if an idle timeout is enabled, start the timer for that.
*/
if (send_ready_for_query)
{
@@ -4239,7 +4256,7 @@ PostgresMain(int argc, char *argv[],
/* Start the idle-in-transaction timer */
if (IdleInTransactionSessionTimeout > 0)
{
- disable_idle_in_transaction_timeout = true;
+ idle_in_transaction_timeout_enabled = true;
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
IdleInTransactionSessionTimeout);
}
@@ -4252,7 +4269,7 @@ PostgresMain(int argc, char *argv[],
/* Start the idle-in-transaction timer */
if (IdleInTransactionSessionTimeout > 0)
{
- disable_idle_in_transaction_timeout = true;
+ idle_in_transaction_timeout_enabled = true;
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
IdleInTransactionSessionTimeout);
}
@@ -4275,6 +4292,14 @@ PostgresMain(int argc, char *argv[],
set_ps_display("idle");
pgstat_report_activity(STATE_IDLE, NULL);
+
+ /* Start the idle-session timer */
+ if (IdleSessionTimeout > 0)
+ {
+ idle_session_timeout_enabled = true;
+ enable_timeout_after(IDLE_SESSION_TIMEOUT,
+ IdleSessionTimeout);
+ }
}
/* Report any recently-changed GUC options */
@@ -4310,12 +4335,21 @@ PostgresMain(int argc, char *argv[],
DoingCommandRead = false;
/*
- * (5) turn off the idle-in-transaction timeout
+ * (5) turn off the idle-in-transaction and idle-session timeouts, if
+ * active.
+ *
+ * At most one of these two will be active, so there's no need to
+ * worry about combining the timeout.c calls into one.
*/
- if (disable_idle_in_transaction_timeout)
+ if (idle_in_transaction_timeout_enabled)
{
disable_timeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, false);
- disable_idle_in_transaction_timeout = false;
+ idle_in_transaction_timeout_enabled = false;
+ }
+ if (idle_session_timeout_enabled)
+ {
+ disable_timeout(IDLE_SESSION_TIMEOUT, false);
+ idle_session_timeout_enabled = false;
}
/*
@@ -109,6 +109,7 @@ Section: Class 08 - Connection Exception
08004 E ERRCODE_SQLSERVER_REJECTED_ESTABLISHMENT_OF_SQLCONNECTION sqlserver_rejected_establishment_of_sqlconnection
08007 E ERRCODE_TRANSACTION_RESOLUTION_UNKNOWN transaction_resolution_unknown
08P01 E ERRCODE_PROTOCOL_VIOLATION protocol_violation
+08P02 E ERRCODE_IDLE_SESSION_TIMEOUT idle_session_timeout
Section: Class 09 - Triggered Action Exception
@@ -32,6 +32,7 @@ volatile sig_atomic_t QueryCancelPending = false;
volatile sig_atomic_t ProcDiePending = false;
volatile sig_atomic_t ClientConnectionLost = false;
volatile sig_atomic_t IdleInTransactionSessionTimeoutPending = false;
+volatile sig_atomic_t IdleSessionTimeoutPending = false;
volatile sig_atomic_t ProcSignalBarrierPending = false;
volatile uint32 InterruptHoldoffCount = 0;
volatile uint32 QueryCancelHoldoffCount = 0;
@@ -72,6 +72,7 @@ static void ShutdownPostgres(int code, Datum arg);
static void StatementTimeoutHandler(void);
static void LockTimeoutHandler(void);
static void IdleInTransactionSessionTimeoutHandler(void);
+static void IdleSessionTimeoutHandler(void);
static bool ThereIsAtLeastOneRole(void);
static void process_startup_options(Port *port, bool am_superuser);
static void process_settings(Oid databaseid, Oid roleid);
@@ -619,6 +620,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
IdleInTransactionSessionTimeoutHandler);
+ RegisterTimeout(IDLE_SESSION_TIMEOUT, IdleSessionTimeoutHandler);
}
/*
@@ -1233,6 +1235,14 @@ IdleInTransactionSessionTimeoutHandler(void)
SetLatch(MyLatch);
}
+static void
+IdleSessionTimeoutHandler(void)
+{
+ IdleSessionTimeoutPending = true;
+ InterruptPending = true;
+ SetLatch(MyLatch);
+}
+
/*
* Returns true if at least one role is defined in this database cluster.
*/
@@ -2509,7 +2509,7 @@ static struct config_int ConfigureNamesInt[] =
{
{"idle_in_transaction_session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT,
- gettext_noop("Sets the maximum allowed duration of any idling transaction."),
+ gettext_noop("Sets the maximum allowed idle time between queries, when in a transaction."),
gettext_noop("A value of 0 turns off the timeout."),
GUC_UNIT_MS
},
@@ -2518,6 +2518,17 @@ static struct config_int ConfigureNamesInt[] =
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."),
+ gettext_noop("A value of 0 turns off the timeout."),
+ GUC_UNIT_MS
+ },
+ &IdleSessionTimeout,
+ 0, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"vacuum_freeze_min_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Minimum age at which VACUUM should freeze a table row."),
@@ -663,6 +663,7 @@
#statement_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
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
@@ -82,6 +82,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 IdleSessionTimeoutPending;
extern PGDLLIMPORT volatile sig_atomic_t ProcSignalBarrierPending;
extern PGDLLIMPORT volatile sig_atomic_t ClientConnectionLost;
@@ -378,6 +378,7 @@ extern PGDLLIMPORT int DeadlockTimeout;
extern PGDLLIMPORT int StatementTimeout;
extern PGDLLIMPORT int LockTimeout;
extern PGDLLIMPORT int IdleInTransactionSessionTimeout;
+extern PGDLLIMPORT int IdleSessionTimeout;
extern bool log_lock_waits;
@@ -31,10 +31,11 @@ typedef enum TimeoutId
STANDBY_TIMEOUT,
STANDBY_LOCK_TIMEOUT,
IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
+ IDLE_SESSION_TIMEOUT,
/* First user-definable timeout reason */
USER_TIMEOUT,
/* Maximum number of timeout reasons */
- MAX_TIMEOUTS = 16
+ MAX_TIMEOUTS = USER_TIMEOUT + 10
} TimeoutId;
/* callback function signature */
|
