diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 392eb70..9cccf0e 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5908,6 +5908,29 @@ 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 to
+ be released and the connection slot to be reused.
+
+
+ Sessions in the state "idle in transaction (aborted)" occupy a connection
+ slot but because they hold no locks, they are not considered by this
+ parameter.
+
+
+ The default value of 0 means that such sessions will not be terminated.
+
+
+
+
vacuum_freeze_table_age (integer)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index dd3c775..6352e12 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 3690753..b711da4 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -57,6 +57,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 390816b..7f03d8e 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2978,6 +2978,11 @@ ProcessInterrupts(void)
}
}
+ if (IdleInTransactionTimeoutSessionPending)
+ ereport(FATAL,
+ (errcode(ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT),
+ errmsg("terminating connection due to idle-in-transaction timeout")));
+
if (ParallelMessagePending)
HandleParallelMessages();
}
@@ -3947,6 +3952,11 @@ PostgresMain(int argc, char *argv[],
{
set_ps_display("idle in transaction", false);
pgstat_report_activity(STATE_IDLEINTRANSACTION, NULL);
+
+ /* Start the idle-in-transaction timer */
+ if (IdleInTransactionSessionTimeout > 0)
+ enable_timeout_after(IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
+ IdleInTransactionSessionTimeout);
}
else
{
@@ -3987,7 +3997,13 @@ 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 (IdleInTransactionSessionTimeout > 0)
+ disable_timeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, false);
+
+ /*
+ * (6) check for any other interesting events that happened while we
* slept.
*/
if (got_SIGHUP)
@@ -3997,7 +4013,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 04c9c00..1a920e8 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 ccd9c8e..0069255 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 IdleInTransactionTimeoutSessionPending = 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 e22d4db..66c1fea 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,7 @@ 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 +1180,13 @@ LockTimeoutHandler(void)
kill(MyProcPid, SIGINT);
}
+static void
+IdleInTransactionSessionTimeoutHandler(void)
+{
+ IdleInTransactionTimeoutSessionPending = 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 38ba82f..d7322c2 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2052,6 +2052,17 @@ 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("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."),
NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 029114f..c7044da 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -527,6 +527,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 cc7833e..3dd70f1 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 IdleInTransactionTimeoutSessionPending;
extern volatile bool ClientConnectionLost;
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index 3441288..d31273c 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -241,6 +241,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 723b475..59a4afe 100644
--- a/src/include/utils/timeout.h
+++ b/src/include/utils/timeout.h
@@ -29,6 +29,7 @@ typedef enum TimeoutId
STATEMENT_TIMEOUT,
STANDBY_DEADLOCK_TIMEOUT,
STANDBY_TIMEOUT,
+ IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
/* First user-definable timeout reason */
USER_TIMEOUT,
/* Maximum number of timeout reasons */