Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9877374

Browse files
committed
Add idle_session_timeout.
This GUC variable works much like idle_in_transaction_session_timeout,in that it kills sessions that have waited too long for a new clientquery. But it applies when we're not in a transaction, rather thanwhen we are.Li Japin, reviewed by David Johnston and Hayato Kuroda, somefixes by meDiscussion:https://postgr.es/m/763A0689-F189-459E-946F-F0EC4458980B@hotmail.com
1 parent09cf1d5 commit9877374

File tree

11 files changed

+115
-16
lines changed

11 files changed

+115
-16
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 44 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -8310,15 +8310,52 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
83108310
</term>
83118311
<listitem>
83128312
<para>
8313-
Terminate any session with an open transaction that has been idle for
8314-
longer than the specified amount of time. This allows any
8315-
locks held by that session to be released and the connection slot to be reused;
8316-
it also allows tuples visible only to this transaction to be vacuumed. See
8317-
<xref linkend="routine-vacuuming"/> for more details about this.
8313+
Terminate any session that has been idle (that is, waiting for a
8314+
client query) within an open transaction for longer than the
8315+
specified amount of time.
8316+
If this value is specified without units, it is taken as milliseconds.
8317+
A value of zero (the default) disables the timeout.
8318+
</para>
8319+
8320+
<para>
8321+
This option can be used to ensure that idle sessions do not hold
8322+
locks for an unreasonable amount of time. Even when no significant
8323+
locks are held, an open transaction prevents vacuuming away
8324+
recently-dead tuples that may be visible only to this transaction;
8325+
so remaining idle for a long time can contribute to table bloat.
8326+
See <xref linkend="routine-vacuuming"/> for more details.
8327+
</para>
8328+
</listitem>
8329+
</varlistentry>
8330+
8331+
<varlistentry id="guc-idle-session-timeout" xreflabel="idle_session_timeout">
8332+
<term><varname>idle_session_timeout</varname> (<type>integer</type>)
8333+
<indexterm>
8334+
<primary><varname>idle_session_timeout</varname> configuration parameter</primary>
8335+
</indexterm>
8336+
</term>
8337+
<listitem>
8338+
<para>
8339+
Terminate any session that has been idle (that is, waiting for a
8340+
client query), but not within an open transaction, for longer than
8341+
the specified amount of time.
8342+
If this value is specified without units, it is taken as milliseconds.
8343+
A value of zero (the default) disables the timeout.
83188344
</para>
8345+
8346+
<para>
8347+
Unlike the case with an open transaction, an idle session without a
8348+
transaction imposes no large costs on the server, so there is less
8349+
need to enable this timeout
8350+
than <varname>idle_in_transaction_session_timeout</varname>.
8351+
</para>
8352+
83198353
<para>
8320-
If this value is specified without units, it is taken as milliseconds.
8321-
A value of zero (the default) disables the timeout.
8354+
Be wary of enforcing this timeout on connections made through
8355+
connection-pooling software or other middleware, as such a layer
8356+
may not react well to unexpected connection closure. It may be
8357+
helpful to enable this timeout only for interactive sessions,
8358+
perhaps by applying it only to particular users.
83228359
</para>
83238360
</listitem>
83248361
</varlistentry>

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@ intDeadlockTimeout = 1000;
6161
intStatementTimeout=0;
6262
intLockTimeout=0;
6363
intIdleInTransactionSessionTimeout=0;
64+
intIdleSessionTimeout=0;
6465
boollog_lock_waits= false;
6566

6667
/* Pointer to this process's PGPROC struct, if any */

‎src/backend/tcop/postgres.c‎

Lines changed: 41 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3242,14 +3242,28 @@ ProcessInterrupts(void)
32423242

32433243
if (IdleInTransactionSessionTimeoutPending)
32443244
{
3245-
/* Has the timeout setting changed since last we looked? */
3245+
/*
3246+
* If the GUC has been reset to zero, ignore the signal. This is
3247+
* important because the GUC update itself won't disable any pending
3248+
* interrupt.
3249+
*/
32463250
if (IdleInTransactionSessionTimeout>0)
32473251
ereport(FATAL,
32483252
(errcode(ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT),
32493253
errmsg("terminating connection due to idle-in-transaction timeout")));
32503254
else
32513255
IdleInTransactionSessionTimeoutPending= false;
3256+
}
32523257

3258+
if (IdleSessionTimeoutPending)
3259+
{
3260+
/* As above, ignore the signal if the GUC has been reset to zero. */
3261+
if (IdleSessionTimeout>0)
3262+
ereport(FATAL,
3263+
(errcode(ERRCODE_IDLE_SESSION_TIMEOUT),
3264+
errmsg("terminating connection due to idle-session timeout")));
3265+
else
3266+
IdleSessionTimeoutPending= false;
32533267
}
32543268

32553269
if (ProcSignalBarrierPending)
@@ -3826,7 +3840,8 @@ PostgresMain(int argc, char *argv[],
38263840
StringInfoDatainput_message;
38273841
sigjmp_buflocal_sigjmp_buf;
38283842
volatileboolsend_ready_for_query= true;
3829-
booldisable_idle_in_transaction_timeout= false;
3843+
boolidle_in_transaction_timeout_enabled= false;
3844+
boolidle_session_timeout_enabled= false;
38303845

38313846
/* Initialize startup process environment if necessary. */
38323847
if (!IsUnderPostmaster)
@@ -4228,6 +4243,8 @@ PostgresMain(int argc, char *argv[],
42284243
* processing of batched messages, and because we don't want to report
42294244
* uncommitted updates (that confuses autovacuum). The notification
42304245
* processor wants a call too, if we are not in a transaction block.
4246+
*
4247+
* Also, if an idle timeout is enabled, start the timer for that.
42314248
*/
42324249
if (send_ready_for_query)
42334250
{
@@ -4239,7 +4256,7 @@ PostgresMain(int argc, char *argv[],
42394256
/* Start the idle-in-transaction timer */
42404257
if (IdleInTransactionSessionTimeout>0)
42414258
{
4242-
disable_idle_in_transaction_timeout= true;
4259+
idle_in_transaction_timeout_enabled= true;
42434260
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
42444261
IdleInTransactionSessionTimeout);
42454262
}
@@ -4252,7 +4269,7 @@ PostgresMain(int argc, char *argv[],
42524269
/* Start the idle-in-transaction timer */
42534270
if (IdleInTransactionSessionTimeout>0)
42544271
{
4255-
disable_idle_in_transaction_timeout= true;
4272+
idle_in_transaction_timeout_enabled= true;
42564273
enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
42574274
IdleInTransactionSessionTimeout);
42584275
}
@@ -4275,6 +4292,14 @@ PostgresMain(int argc, char *argv[],
42754292

42764293
set_ps_display("idle");
42774294
pgstat_report_activity(STATE_IDLE,NULL);
4295+
4296+
/* Start the idle-session timer */
4297+
if (IdleSessionTimeout>0)
4298+
{
4299+
idle_session_timeout_enabled= true;
4300+
enable_timeout_after(IDLE_SESSION_TIMEOUT,
4301+
IdleSessionTimeout);
4302+
}
42784303
}
42794304

42804305
/* Report any recently-changed GUC options */
@@ -4310,12 +4335,21 @@ PostgresMain(int argc, char *argv[],
43104335
DoingCommandRead= false;
43114336

43124337
/*
4313-
* (5) turn off the idle-in-transaction timeout
4338+
* (5) turn off the idle-in-transaction and idle-session timeouts, if
4339+
* active.
4340+
*
4341+
* At most one of these two will be active, so there's no need to
4342+
* worry about combining the timeout.c calls into one.
43144343
*/
4315-
if (disable_idle_in_transaction_timeout)
4344+
if (idle_in_transaction_timeout_enabled)
43164345
{
43174346
disable_timeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, false);
4318-
disable_idle_in_transaction_timeout= false;
4347+
idle_in_transaction_timeout_enabled= false;
4348+
}
4349+
if (idle_session_timeout_enabled)
4350+
{
4351+
disable_timeout(IDLE_SESSION_TIMEOUT, false);
4352+
idle_session_timeout_enabled= false;
43194353
}
43204354

43214355
/*

‎src/backend/utils/errcodes.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,7 @@ Section: Class 08 - Connection Exception
109109
08004 E ERRCODE_SQLSERVER_REJECTED_ESTABLISHMENT_OF_SQLCONNECTION sqlserver_rejected_establishment_of_sqlconnection
110110
08007 E ERRCODE_TRANSACTION_RESOLUTION_UNKNOWN transaction_resolution_unknown
111111
08P01 E ERRCODE_PROTOCOL_VIOLATION protocol_violation
112+
08P02 E ERRCODE_IDLE_SESSION_TIMEOUT idle_session_timeout
112113

113114
Section: Class 09 - Triggered Action Exception
114115

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ volatile sig_atomic_t QueryCancelPending = false;
3232
volatilesig_atomic_tProcDiePending= false;
3333
volatilesig_atomic_tClientConnectionLost= false;
3434
volatilesig_atomic_tIdleInTransactionSessionTimeoutPending= false;
35+
volatilesig_atomic_tIdleSessionTimeoutPending= false;
3536
volatilesig_atomic_tProcSignalBarrierPending= false;
3637
volatileuint32InterruptHoldoffCount=0;
3738
volatileuint32QueryCancelHoldoffCount=0;

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,7 @@ static void ShutdownPostgres(int code, Datum arg);
7272
staticvoidStatementTimeoutHandler(void);
7373
staticvoidLockTimeoutHandler(void);
7474
staticvoidIdleInTransactionSessionTimeoutHandler(void);
75+
staticvoidIdleSessionTimeoutHandler(void);
7576
staticboolThereIsAtLeastOneRole(void);
7677
staticvoidprocess_startup_options(Port*port,boolam_superuser);
7778
staticvoidprocess_settings(Oiddatabaseid,Oidroleid);
@@ -619,6 +620,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username,
619620
RegisterTimeout(LOCK_TIMEOUT,LockTimeoutHandler);
620621
RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
621622
IdleInTransactionSessionTimeoutHandler);
623+
RegisterTimeout(IDLE_SESSION_TIMEOUT,IdleSessionTimeoutHandler);
622624
}
623625

624626
/*
@@ -1233,6 +1235,14 @@ IdleInTransactionSessionTimeoutHandler(void)
12331235
SetLatch(MyLatch);
12341236
}
12351237

1238+
staticvoid
1239+
IdleSessionTimeoutHandler(void)
1240+
{
1241+
IdleSessionTimeoutPending= true;
1242+
InterruptPending= true;
1243+
SetLatch(MyLatch);
1244+
}
1245+
12361246
/*
12371247
* Returns true if at least one role is defined in this database cluster.
12381248
*/

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

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2509,7 +2509,7 @@ static struct config_int ConfigureNamesInt[] =
25092509

25102510
{
25112511
{"idle_in_transaction_session_timeout",PGC_USERSET,CLIENT_CONN_STATEMENT,
2512-
gettext_noop("Sets the maximum allowedduration of any idling transaction."),
2512+
gettext_noop("Sets the maximum allowedidle time between queries, when in a transaction."),
25132513
gettext_noop("A value of 0 turns off the timeout."),
25142514
GUC_UNIT_MS
25152515
},
@@ -2518,6 +2518,17 @@ static struct config_int ConfigureNamesInt[] =
25182518
NULL,NULL,NULL
25192519
},
25202520

2521+
{
2522+
{"idle_session_timeout",PGC_USERSET,CLIENT_CONN_STATEMENT,
2523+
gettext_noop("Sets the maximum allowed idle time between queries, when not in a transaction."),
2524+
gettext_noop("A value of 0 turns off the timeout."),
2525+
GUC_UNIT_MS
2526+
},
2527+
&IdleSessionTimeout,
2528+
0,0,INT_MAX,
2529+
NULL,NULL,NULL
2530+
},
2531+
25212532
{
25222533
{"vacuum_freeze_min_age",PGC_USERSET,CLIENT_CONN_STATEMENT,
25232534
gettext_noop("Minimum age at which VACUUM should freeze a table row."),

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -663,6 +663,7 @@
663663
#statement_timeout = 0# in milliseconds, 0 is disabled
664664
#lock_timeout = 0# in milliseconds, 0 is disabled
665665
#idle_in_transaction_session_timeout = 0# in milliseconds, 0 is disabled
666+
#idle_session_timeout = 0# in milliseconds, 0 is disabled
666667
#vacuum_freeze_min_age = 50000000
667668
#vacuum_freeze_table_age = 150000000
668669
#vacuum_multixact_freeze_min_age = 5000000

‎src/include/miscadmin.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,7 @@ extern PGDLLIMPORT volatile sig_atomic_t InterruptPending;
8282
externPGDLLIMPORTvolatilesig_atomic_tQueryCancelPending;
8383
externPGDLLIMPORTvolatilesig_atomic_tProcDiePending;
8484
externPGDLLIMPORTvolatilesig_atomic_tIdleInTransactionSessionTimeoutPending;
85+
externPGDLLIMPORTvolatilesig_atomic_tIdleSessionTimeoutPending;
8586
externPGDLLIMPORTvolatilesig_atomic_tProcSignalBarrierPending;
8687

8788
externPGDLLIMPORTvolatilesig_atomic_tClientConnectionLost;

‎src/include/storage/proc.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -378,6 +378,7 @@ extern PGDLLIMPORT int DeadlockTimeout;
378378
externPGDLLIMPORTintStatementTimeout;
379379
externPGDLLIMPORTintLockTimeout;
380380
externPGDLLIMPORTintIdleInTransactionSessionTimeout;
381+
externPGDLLIMPORTintIdleSessionTimeout;
381382
externboollog_lock_waits;
382383

383384

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp