Re: [PATCHES] transaction idle timeout

[Tom Lane]

=?ISO-8859-1?Q?Szima_G=E1bor?= <[EMAIL PROTECTED]> writes:
> Here is a patch for backend to allow transaction idle timeout.

I don't think this is a good idea in the first place.  But even if it
were, this patch is a mess.  secure_read() has no business doing the
things you have made it do, either messing with the PS display or
directly calling AbortCurrentTransaction; the patch doesn't work for SSL
connections (and in fact probably actively breaks them); it breaks
handling of all I/O errors other than timeout; it significantly
increases the load imposed by an idle connection (since your
implementation causes a waiting backend to wake up once a second, even
if the feature isn't in use); and there's no documentation.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[PATCHES] transaction idle timeout

[Szima Gábor]

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.hSun 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 */
 intDeadlockTimeout = 1000;
+intTransIdleTimeout = 0;
 intStatementTimeout = 0;

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

backend/libpq/pqcomm.c:
--- postgresql-8.0.0beta2/src/backend/libpq/pqcomm.cSun 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.cWed 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"
+
+inttranstimeout = 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)", " 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html