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