transaction idle timeout - Mailing list pgsql-patches

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