From c6dda1f48e573cc60fe25d8d470a05bfabed0252 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 16 Mar 2016 11:30:45 -0400 Subject: [PATCH] Add idle_in_transaction_session_timeout. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Vik Fearing, reviewed by Stéphane Schildknecht and me, and revised slightly by me. --- doc/src/sgml/config.sgml | 20 +++++++++ doc/src/sgml/mvcc.sgml | 4 +- src/backend/storage/lmgr/proc.c | 1 + src/backend/tcop/postgres.c | 42 ++++++++++++++++++- 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.c | 11 +++++ src/backend/utils/misc/postgresql.conf.sample | 1 + src/include/miscadmin.h | 1 + src/include/storage/proc.h | 1 + src/include/utils/timeout.h | 1 + 12 files changed, 91 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 6c73fb439c..da971f5fca 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -6063,6 +6063,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + idle_in_transaction_session_timeout (integer) + + idle_in_transaction_session_timeout configuration parameter + + + + + Terminate any session with an open transaction that has been idle for + longer than the specified duration in milliseconds. 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 + for more details about this. + + + The default value of 0 disables this feature. + + + + vacuum_freeze_table_age (integer) diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index dd3c775530..6352e12ee1 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -725,7 +725,9 @@ ERROR: could not serialize access due to read/write dependencies among transact Don't leave connections dangling idle in transaction - longer than necessary. + longer than necessary. The configuration parameter + may be used to + automatically disconnect lingering sessions. diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c index 74ef419798..a66e07b766 100644 --- a/src/backend/storage/lmgr/proc.c +++ b/src/backend/storage/lmgr/proc.c @@ -58,6 +58,7 @@ int DeadlockTimeout = 1000; int StatementTimeout = 0; int LockTimeout = 0; +int IdleInTransactionSessionTimeout = 0; bool log_lock_waits = false; /* Pointer to this process's PGPROC and PGXACT structs, if any */ diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 115166b6f6..68811f1f21 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -2978,6 +2978,18 @@ ProcessInterrupts(void) } } + if (IdleInTransactionSessionTimeoutPending) + { + /* Has the timeout setting changed since last we looked? */ + 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 (ParallelMessagePending) HandleParallelMessages(); } @@ -3553,6 +3565,7 @@ 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; /* Initialize startup process environment if necessary. */ if (!IsUnderPostmaster) @@ -3942,11 +3955,27 @@ PostgresMain(int argc, char *argv[], { set_ps_display("idle in transaction (aborted)", false); pgstat_report_activity(STATE_IDLEINTRANSACTION_ABORTED, NULL); + + /* Start the idle-in-transaction timer */ + if (IdleInTransactionSessionTimeout > 0) + { + disable_idle_in_transaction_timeout = true; + enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, + IdleInTransactionSessionTimeout); + } } else if (IsTransactionOrTransactionBlock()) { set_ps_display("idle in transaction", false); pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL); + + /* Start the idle-in-transaction timer */ + if (IdleInTransactionSessionTimeout > 0) + { + disable_idle_in_transaction_timeout = true; + enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, + IdleInTransactionSessionTimeout); + } } else { @@ -3987,7 +4016,16 @@ PostgresMain(int argc, char *argv[], DoingCommandRead = false; /* - * (5) check for any other interesting events that happened while we + * (5) turn off the idle-in-transaction timeout + */ + if (disable_idle_in_transaction_timeout) + { + disable_timeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, false); + disable_idle_in_transaction_timeout = false; + } + + /* + * (6) check for any other interesting events that happened while we * slept. */ if (got_SIGHUP) @@ -3997,7 +4035,7 @@ PostgresMain(int argc, char *argv[], } /* - * (6) process the command. But ignore it if we're skipping till + * (7) process the command. But ignore it if we're skipping till * Sync. */ if (ignore_till_sync && firstchar != EOF) diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index 04c9c00a27..1a920e8bd2 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -229,6 +229,7 @@ Section: Class 25 - Invalid Transaction State 25007 E ERRCODE_SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED schema_and_data_statement_mixing_not_supported 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 Section: Class 26 - Invalid SQL Statement Name diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index ccd9c8e7f8..597dab4301 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -30,6 +30,7 @@ volatile bool InterruptPending = false; volatile bool QueryCancelPending = false; volatile bool ProcDiePending = false; volatile bool ClientConnectionLost = false; +volatile bool IdleInTransactionSessionTimeoutPending = false; volatile uint32 InterruptHoldoffCount = 0; volatile uint32 QueryCancelHoldoffCount = 0; volatile uint32 CritSectionCount = 0; diff --git a/src/backend/utils/init/postinit.c b/src/backend/utils/init/postinit.c index 6b760d4265..b3f1bc46d9 100644 --- a/src/backend/utils/init/postinit.c +++ b/src/backend/utils/init/postinit.c @@ -70,6 +70,7 @@ static void InitCommunication(void); static void ShutdownPostgres(int code, Datum arg); static void StatementTimeoutHandler(void); static void LockTimeoutHandler(void); +static void IdleInTransactionSessionTimeoutHandler(void); static bool ThereIsAtLeastOneRole(void); static void process_startup_options(Port *port, bool am_superuser); static void process_settings(Oid databaseid, Oid roleid); @@ -597,6 +598,8 @@ InitPostgres(const char *in_dbname, Oid dboid, const char *username, RegisterTimeout(DEADLOCK_TIMEOUT, CheckDeadLockAlert); RegisterTimeout(STATEMENT_TIMEOUT, StatementTimeoutHandler); RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler); + RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, + IdleInTransactionSessionTimeoutHandler); } /* @@ -1178,6 +1181,13 @@ LockTimeoutHandler(void) kill(MyProcPid, SIGINT); } +static void +IdleInTransactionSessionTimeoutHandler(void) +{ + IdleInTransactionSessionTimeoutPending = true; + InterruptPending = true; + SetLatch(MyLatch); +} /* * Returns true if at least one role is defined in this database cluster. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index f0d4ec165e..be9d5cacc0 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2065,6 +2065,17 @@ static struct config_int ConfigureNamesInt[] = NULL, NULL, NULL }, + { + {"idle_in_transaction_session_timeout", PGC_USERSET, CLIENT_CONN_STATEMENT, + gettext_noop("Sets the maximum allowed duration of any idling transaction."), + gettext_noop("A value of 0 turns off the timeout."), + GUC_UNIT_MS + }, + &IdleInTransactionSessionTimeout, + 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."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index ee3d37863e..a6bb33557c 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -529,6 +529,7 @@ #session_replication_role = 'origin' #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 #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h index cc7833e6cd..9200f045a2 100644 --- a/src/include/miscadmin.h +++ b/src/include/miscadmin.h @@ -80,6 +80,7 @@ extern PGDLLIMPORT volatile bool InterruptPending; extern PGDLLIMPORT volatile bool QueryCancelPending; extern PGDLLIMPORT volatile bool ProcDiePending; +extern PGDLLIMPORT volatile bool IdleInTransactionSessionTimeoutPending; extern volatile bool ClientConnectionLost; diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 612fa052be..c3b462c949 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -257,6 +257,7 @@ extern PGPROC *PreparedXactProcs; extern int DeadlockTimeout; extern int StatementTimeout; extern int LockTimeout; +extern int IdleInTransactionSessionTimeout; extern bool log_lock_waits; diff --git a/src/include/utils/timeout.h b/src/include/utils/timeout.h index 14e9720c88..f64921e2d6 100644 --- a/src/include/utils/timeout.h +++ b/src/include/utils/timeout.h @@ -30,6 +30,7 @@ typedef enum TimeoutId STANDBY_DEADLOCK_TIMEOUT, STANDBY_TIMEOUT, STANDBY_LOCK_TIMEOUT, + IDLE_IN_TRANSACTION_SESSION_TIMEOUT, /* First user-definable timeout reason */ USER_TIMEOUT, /* Maximum number of timeout reasons */ -- 2.40.0