transaction idle timeout

Hello,

Here is a patch for backend to allow transaction idle timeout.
Works with original 8.0.0beta2 (and 7.4.5 with some warnings).

TODO: SSL-connection

----8<----------------------------------------------------------------------------------

include/storage/proc.h:
--- postgresql-8.0.0beta2/src/include/storage/proc.h	Sun Aug 29 07:06:58 2004
+++ postgresql-8.0.0beta2_tito/src/include/storage/proc.h	Wed Sep 22 17:33:27 2004
@@ -115,6 +115,7 @@ typedef struct PROC_HDR

/* configurable options */
extern int DeadlockTimeout;
+extern int TransIdleTimeout;
extern int StatementTimeout;

backend/storage/lmgr/proc.c:
--- postgresql-8.0.0beta2/src/backend/storage/lmgr/proc.c	Sun Aug 29 07:06:48 2004
+++ postgresql-8.0.0beta2_tito/src/backend/storage/lmgr/proc.c	Wed Sep 22 17:54:04 2004
@@ -55,6 +55,7 @@

/* GUC variables */
int DeadlockTimeout = 1000;
+int TransIdleTimeout = 0;
int StatementTimeout = 0;

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

backend/libpq/pqcomm.c:
--- postgresql-8.0.0beta2/src/backend/libpq/pqcomm.c	Sun Aug 29 07:06:43 2004
+++ postgresql-8.0.0beta2_tito/src/backend/libpq/pqcomm.c	Wed Sep 22 17:33:27 2004
@@ -570,6 +570,16 @@ StreamConnection(int server_fd, Port *po
 			return STATUS_ERROR;
 		}
 	}
+	struct timeval to;
+
+	to.tv_sec = 1;
+	to.tv_usec = 0;
+
+	if ( setsockopt(port->sock, SOL_SOCKET, SO_RCVTIMEO, &to, sizeof(to)) < 0 )
+	{
+		elog(LOG, "setsockopt(SO_RCVTIMEO) failed: %m");
+		return STATUS_ERROR;
+	}

return STATUS_OK;
}

backend/libpq/be-secure.c:
--- postgresql-8.0.0beta2/src/backend/libpq/be-secure.c	Sun Aug 29 07:06:43 2004
+++ postgresql-8.0.0beta2_tito/src/backend/libpq/be-secure.c	Wed Sep 22 17:33:27 2004
@@ -246,6 +246,13 @@ secure_close(Port *port)
 #endif
 }

+#include "storage/proc.h"
+#include "access/xact.h"
+#include "utils/ps_status.h"
+#include "pgstat.h"
+
+int	transtimeout = 0;
+
 /*
  *	Read data from a secure connection.
  */
@@ -301,9 +308,27 @@ rloop:
 	}
 	else
 #endif
-		n = recv(port->sock, ptr, len, 0);
+    while ( 1 ) {
+	n = recv(port->sock, ptr, len, 0);
+	if ( n >= 0 ) {
+	    transtimeout = TransIdleTimeout;
+	    break;
+	}
+	if ( IsTransactionOrTransactionBlock() ) {
+	    if ( transtimeout > 0 ) {

-	return n;
+		char pbuff[128];
+
+		if ( transtimeout == 1 ) snprintf (pbuff, sizeof (pbuff), "%s (%s)", "idle in transaction", "aborted");
+		else snprintf (pbuff, sizeof (pbuff), "%s (%d)", "idle in transaction", transtimeout - 1);
+		set_ps_display(pbuff);
+		snprintf (pbuff, sizeof (pbuff), "%s (%d)", "<IDLE> in transaction", transtimeout - 1);
+		pgstat_report_activity(pbuff);
+		if ( --transtimeout == 0 ) AbortCurrentTransaction ();
+	    }
+	}
+    }
+    return n;
 }

/*

backend/utils/misc/guc.c:
--- postgresql-8.0.0beta2/src/backend/utils/misc/guc.c	Mon Aug 30 04:54:40 2004
+++ postgresql-8.0.0beta2_tito/src/backend/utils/misc/guc.c	Wed Sep 22 17:33:27 2004
@@ -911,7 +911,6 @@ static struct config_int ConfigureNamesI
 		&Geqo_generations,
 		0, 0, INT_MAX, NULL, NULL
 	},
-
 	{
 		{"deadlock_timeout", PGC_SIGHUP, LOCK_MANAGEMENT,
 			gettext_noop("The time in milliseconds to wait on lock before checking for deadlock."),
@@ -919,6 +918,14 @@ static struct config_int ConfigureNamesI
 		},
 		&DeadlockTimeout,
 		1000, 0, INT_MAX, NULL, NULL
+	},
+	{
+		{"transaction_idle_timeout", PGC_USERSET, LOCK_MANAGEMENT,
+			gettext_noop("The time in seconds to wait on transaction before checking for network problem."),
+			NULL
+		},
+		&TransIdleTimeout,
+		0, 0, INT_MAX, NULL, NULL
 	},

/*

----8<----------------------------------------------------------------------------------

-Sygma