On 06/21/2014 08:23 PM, Kevin Grittner wrote:
> OK, so I think we want to see a patch based on v1 (FATAL approach)
> with a change of the name to idle_in_transaction_session_timeout
> and the units changed to milliseconds. I don't see why the
> remoteversion test shouldn't be changed to use 90500 now, too.
The attached patch, rebased to current master, addresses all of these
issues.
> I'll flip this to Waiting on Author for those changes.
And back to Needs Review.
--
Vik
*** a/contrib/postgres_fdw/connection.c
--- b/contrib/postgres_fdw/connection.c
***************
*** 343,348 **** configure_remote_session(PGconn *conn)
--- 343,355 ----
do_sql_command(conn, "SET extra_float_digits = 3");
else
do_sql_command(conn, "SET extra_float_digits = 2");
+
+ /*
+ * Ensure the remote server doesn't kill us off if we remain idle in a
+ * transaction for too long.
+ */
+ if (remoteversion >= 90500)
+ do_sql_command(conn, "SET idle_in_transaction_session_timeout = 0");
}
/*
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***************
*** 5545,5550 **** COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
--- 5545,5573 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-idle-in-transaction-session-timeout" xreflabel="idle_in_transaction_session_timeout">
+ <term><varname>idle_in_transaction_session_timeout</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>idle_in_transaction_session_timeout</> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ <para>
+ 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.
+ </para>
+ <para>
+ The default value of 0 means that such sessions will not be terminated.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-vacuum-freeze-table-age" xreflabel="vacuum_freeze_table_age">
<term><varname>vacuum_freeze_table_age</varname> (<type>integer</type>)
<indexterm>
*** a/doc/src/sgml/mvcc.sgml
--- b/doc/src/sgml/mvcc.sgml
***************
*** 676,682 **** ERROR: could not serialize access due to read/write dependencies among transact
<listitem>
<para>
Don't leave connections dangling <quote>idle in transaction</quote>
! longer than necessary.
</para>
</listitem>
<listitem>
--- 676,684 ----
<listitem>
<para>
Don't leave connections dangling <quote>idle in transaction</quote>
! longer than necessary. The configuration parameter
! <xref linkend="guc-idle-in-transaction-session-timeout"> may be used to
! automatically disconnect lingering sessions.
</para>
</listitem>
<listitem>
*** a/src/backend/storage/lmgr/proc.c
--- b/src/backend/storage/lmgr/proc.c
***************
*** 57,62 ****
--- 57,63 ----
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 */
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
***************
*** 3943,3948 **** PostgresMain(int argc, char *argv[],
--- 3943,3953 ----
{
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
{
***************
*** 3976,3982 **** PostgresMain(int argc, char *argv[],
DoingCommandRead = false;
/*
! * (5) check for any other interesting events that happened while we
* slept.
*/
if (got_SIGHUP)
--- 3981,3993 ----
DoingCommandRead = false;
/*
! * (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)
***************
*** 3986,3992 **** PostgresMain(int argc, char *argv[],
}
/*
! * (6) process the command. But ignore it if we're skipping till
* Sync.
*/
if (ignore_till_sync && firstchar != EOF)
--- 3997,4003 ----
}
/*
! * (7) process the command. But ignore it if we're skipping till
* Sync.
*/
if (ignore_till_sync && firstchar != EOF)
*** a/src/backend/utils/errcodes.txt
--- b/src/backend/utils/errcodes.txt
***************
*** 227,232 **** Section: Class 25 - Invalid Transaction State
--- 227,233 ----
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
*** a/src/backend/utils/init/postinit.c
--- b/src/backend/utils/init/postinit.c
***************
*** 68,73 **** static void InitCommunication(void);
--- 68,74 ----
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);
***************
*** 550,555 **** InitPostgres(const char *in_dbname, Oid dboid, const char *username,
--- 551,557 ----
RegisterTimeout(DEADLOCK_TIMEOUT, CheckDeadLock);
RegisterTimeout(STATEMENT_TIMEOUT, StatementTimeoutHandler);
RegisterTimeout(LOCK_TIMEOUT, LockTimeoutHandler);
+ RegisterTimeout(IDLE_IN_TRANSACTION_SESSION_TIMEOUT, IdleInTransactionSessionTimeoutHandler);
}
/*
***************
*** 1092,1097 **** LockTimeoutHandler(void)
--- 1094,1106 ----
kill(MyProcPid, SIGINT);
}
+ static void
+ IdleInTransactionSessionTimeoutHandler(void)
+ {
+ ereport(FATAL,
+ (errcode(ERRCODE_IDLE_IN_TRANSACTION_SESSION_TIMEOUT),
+ errmsg("terminating connection due to idle-in-transaction timeout")));
+ }
/*
* Returns true if at least one role is defined in this database cluster.
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
***************
*** 1948,1953 **** static struct config_int ConfigureNamesInt[] =
--- 1948,1964 ----
},
{
+ {"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
*** a/src/backend/utils/misc/postgresql.conf.sample
--- b/src/backend/utils/misc/postgresql.conf.sample
***************
*** 509,514 ****
--- 509,515 ----
#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
*** a/src/include/storage/proc.h
--- b/src/include/storage/proc.h
***************
*** 226,231 **** extern PGPROC *PreparedXactProcs;
--- 226,232 ----
extern int DeadlockTimeout;
extern int StatementTimeout;
extern int LockTimeout;
+ extern int IdleInTransactionSessionTimeout;
extern bool log_lock_waits;
*** a/src/include/utils/timeout.h
--- b/src/include/utils/timeout.h
***************
*** 29,34 **** typedef enum TimeoutId
--- 29,35 ----
STATEMENT_TIMEOUT,
STANDBY_DEADLOCK_TIMEOUT,
STANDBY_TIMEOUT,
+ IDLE_IN_TRANSACTION_SESSION_TIMEOUT,
/* First user-definable timeout reason */
USER_TIMEOUT,
/* Maximum number of timeout reasons */
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
