Re: Implementing RESET CONNECTION ...

[Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>]

Attached patch applied, with slight modifications.  Thanks.

---------------------------------------------------------------------------

Hans-Jürgen Schönig wrote:
> We have implemented a patch which can be used by connection pools for 
> instance.
> RESECT CONNECTION cleans up a backend so that it can be reused.
> Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open 
> transactions, prepared statements and GUCs are cleaned up.
> I hope we have not missed important per-backend information.
> 
> test=# BEGIN;
> BEGIN
> test=# RESET CONNECTION;
> RESET
> test=# COMMIT;
> WARNING:  there is no transaction in progress
> COMMIT
> test=# PREPARE myplan(int, int) AS SELECT $1 + $2;
> PREPARE
> test=# RESET CONNECTION;
> RESET
> test=# EXECUTE myplan(1, 2);
> ERROR:  prepared statement "myplan" does not exist
> test=#
> test=# DECLARE mycur CURSOR WITH HOLD FOR SELECT relname FROM pg_class;
> DECLARE CURSOR
> test=# FETCH NEXT FROM mycur;
>   relname
> ---------
>   views
> (1 row)
> 
> test=# RESET CONNECTION;
> RESET
> test=# FETCH NEXT FROM mycur;
> ERROR:  cursor "mycur" does not exist
> test=# CREATE TEMP TABLE mytmp (id int4);
> CREATE TABLE
> test=# RESET CONNECTION;
> RESET
> test=# INSERT INTO mytmp VALUES (10);
> ERROR:  relation "mytmp" does not exist
> 
> 
> All regression tests passed.
> It would be nice if we had this in 8.1.
> 
> 	Best regards,
> 
> 		Hans
> 
> 
> -- 
> Cybertec Geschwinde u Schoenig
> Schoengrabern 134, A-2020 Hollabrunn, Austria
> Tel: +43/660/816 40 77
> www.cybertec.at, www.postgresql.at
> 


-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Index: doc/src/sgml/ref/reset.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/reset.sgml,v
retrieving revision 1.26
diff -c -c -r1.26 reset.sgml
*** doc/src/sgml/ref/reset.sgml	14 Dec 2003 00:15:03 -0000	1.26
--- doc/src/sgml/ref/reset.sgml	25 Apr 2006 14:00:45 -0000
***************
*** 11,17 ****
  
   <refnamediv>
    <refname>RESET</refname>
!   <refpurpose>restore the value of a run-time parameter to the default value</refpurpose>
   </refnamediv>
  
   <indexterm zone="sql-reset">
--- 11,18 ----
  
   <refnamediv>
    <refname>RESET</refname>
!   <refpurpose>restore the value of a run-time parameter to the default value,
!   or reset all aspects of a session</refpurpose>
   </refnamediv>
  
   <indexterm zone="sql-reset">
***************
*** 22,27 ****
--- 23,29 ----
  <synopsis>
  RESET <replaceable class="PARAMETER">name</replaceable>
  RESET ALL
+ RESET CONNECTION
  </synopsis>
   </refsynopsisdiv>
    
***************
*** 50,57 ****
  
    <para>
     See the <command>SET</> reference page for details on the
!    transaction behavior of <command>RESET</>.
    </para>
   </refsect1>
  
   <refsect1>
--- 52,62 ----
  
    <para>
     See the <command>SET</> reference page for details on the
!    transaction behavior of <command>RESET</>.  <command>RESET
!    CONNECTION</command> can be used to reset all aspects of
!    a session, not just variable values.
    </para>
+ 
   </refsect1>
  
   <refsect1>
***************
*** 76,82 ****
--- 81,100 ----
       </para>
      </listitem>
     </varlistentry>
+ 
+    <varlistentry>
+     <term><literal>CONNECTION</literal></term>
+     <listitem>
+      <para>
+       Reset the all aspects of a session, including runtime parameters,
+       transaction status, temporary tables, <literal>WITH HOLD</literal>
+       cursors, prepared statements, and <command>LISTEN</command>
+       registrations.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
+ 
   </refsect1>
  
   <refsect1>
Index: src/backend/catalog/namespace.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/namespace.c,v
retrieving revision 1.82
diff -c -c -r1.82 namespace.c
*** src/backend/catalog/namespace.c	5 Mar 2006 15:58:22 -0000	1.82
--- src/backend/catalog/namespace.c	25 Apr 2006 14:00:47 -0000
***************
*** 134,140 ****
  /* Local functions */
  static void recomputeNamespacePath(void);
  static void InitTempTableNamespace(void);
- static void RemoveTempRelations(Oid tempNamespaceId);
  static void RemoveTempRelationsCallback(int code, Datum arg);
  static void NamespaceCallback(Datum arg, Oid relid);
  
--- 134,139 ----
***************
*** 1729,1735 ****
   * in order to clean out any relations that might have been created by
   * a crashed backend.
   */
! static void
  RemoveTempRelations(Oid tempNamespaceId)
  {
  	ObjectAddress object;
--- 1728,1734 ----
   * in order to clean out any relations that might have been created by
   * a crashed backend.
   */
! void
  RemoveTempRelations(Oid tempNamespaceId)
  {
  	ObjectAddress object;
Index: src/backend/commands/async.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/async.c,v
retrieving revision 1.129
diff -c -c -r1.129 async.c
*** src/backend/commands/async.c	5 Mar 2006 15:58:23 -0000	1.129
--- src/backend/commands/async.c	25 Apr 2006 14:00:48 -0000
***************
*** 127,133 ****
  bool		Trace_notify = false;
  
  
- static void Async_UnlistenAll(void);
  static void Async_UnlistenOnExit(int code, Datum arg);
  static void ProcessIncomingNotify(void);
  static void NotifyMyFrontEnd(char *relname, int32 listenerPID);
--- 127,132 ----
***************
*** 335,341 ****
   *
   *--------------------------------------------------------------
   */
! static void
  Async_UnlistenAll(void)
  {
  	Relation	lRel;
--- 334,340 ----
   *
   *--------------------------------------------------------------
   */
! void
  Async_UnlistenAll(void)
  {
  	Relation	lRel;
Index: src/backend/commands/prepare.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/prepare.c,v
retrieving revision 1.50
diff -c -c -r1.50 prepare.c
*** src/backend/commands/prepare.c	22 Apr 2006 01:25:58 -0000	1.50
--- src/backend/commands/prepare.c	25 Apr 2006 14:00:49 -0000
***************
*** 33,39 ****
  #include "utils/hsearch.h"
  #include "utils/memutils.h"
  
- 
  /*
   * The hash table in which prepared queries are stored. This is
   * per-backend: query plans are not shared between backends.
--- 33,38 ----
***************
*** 548,553 ****
--- 547,576 ----
  }
  
  /*
+  * Remove all prepared plans from the backend.
+  */
+ void
+ DropAllPreparedStatements(void)
+ {
+ 	PreparedStatement	*prep_statement;
+ 	HASH_SEQ_STATUS         status;
+ 
+ 	if	(!prepared_queries)
+ 		return;
+ 
+ 	hash_seq_init(&status, prepared_queries);
+ 
+ 	while ((prep_statement = (PreparedStatement *) hash_seq_search(&status)))
+ 	{
+ 		DropDependentPortals(prep_statement->context);
+ 
+ 		/* Flush the context holding the subsidiary data */
+ 		MemoryContextDelete(prep_statement->context);
+                 hash_search(prepared_queries, prep_statement->stmt_name, HASH_REMOVE, NULL);
+ 	}
+ }
+ 
+ /*
   * Internal version of DEALLOCATE
   *
   * If showError is false, dropping a nonexistent statement is a no-op.
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.540
diff -c -c -r2.540 gram.y
*** src/backend/parser/gram.y	24 Apr 2006 22:59:19 -0000	2.540
--- src/backend/parser/gram.y	25 Apr 2006 14:00:56 -0000
***************
*** 1244,1249 ****
--- 1244,1255 ----
  					n->name = $2;
  					$$ = (Node *) n;
  				}
+ 			| RESET CONNECTION
+ 				{
+ 					VariableResetStmt *n = makeNode(VariableResetStmt);
+ 					n->name = "connection";
+ 					$$ = (Node *) n;
+ 				}
  			| RESET TIME ZONE
  				{
  					VariableResetStmt *n = makeNode(VariableResetStmt);
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.315
diff -c -c -r1.315 guc.c
*** src/backend/utils/misc/guc.c	10 Apr 2006 21:53:38 -0000	1.315
--- src/backend/utils/misc/guc.c	25 Apr 2006 14:01:08 -0000
***************
*** 32,37 ****
--- 32,38 ----
  #include "catalog/namespace.h"
  #include "catalog/pg_type.h"
  #include "commands/async.h"
+ #include "commands/prepare.h"
  #include "commands/variable.h"
  #include "commands/vacuum.h"
  #include "executor/executor.h"
***************
*** 53,58 ****
--- 54,60 ----
  #include "postmaster/bgwriter.h"
  #include "postmaster/syslogger.h"
  #include "postmaster/postmaster.h"
+ #include "storage/backendid.h"
  #include "storage/bufmgr.h"
  #include "storage/fd.h"
  #include "storage/freespace.h"
***************
*** 61,71 ****
  #include "tcop/tcopprot.h"
  #include "utils/array.h"
  #include "utils/builtins.h"
  #include "utils/memutils.h"
  #include "utils/pg_locale.h"
  #include "pgstat.h"
  
- 
  #ifndef PG_KRB_SRVTAB
  #define PG_KRB_SRVTAB ""
  #endif
--- 63,75 ----
  #include "tcop/tcopprot.h"
  #include "utils/array.h"
  #include "utils/builtins.h"
+ #include "utils/hsearch.h"
  #include "utils/memutils.h"
  #include "utils/pg_locale.h"
+ #include "utils/portal.h"
+ #include "utils/syscache.h"
  #include "pgstat.h"
  
  #ifndef PG_KRB_SRVTAB
  #define PG_KRB_SRVTAB ""
  #endif
***************
*** 4649,4656 ****
--- 4653,4685 ----
  void
  ResetPGVariable(const char *name)
  {
+ 	char			namespaceName[NAMEDATALEN];
+ 	Oid			namespaceId;
+ 
  	if (pg_strcasecmp(name, "all") == 0)
+ 		/* resetting all GUC variables */
  		ResetAllOptions();
+ 	else if	(pg_strcasecmp(name, "connection") == 0)
+ 	{
+ 		ResetAllOptions();
+ 
+ 		/* Clean temp-tables */
+ 		snprintf(namespaceName, sizeof(namespaceName), "pg_temp_%d",
+ 				 MyBackendId);
+ 		namespaceId = GetSysCacheOid(NAMESPACENAME,
+ 									 CStringGetDatum(namespaceName), 0, 0, 0);
+ 		RemoveTempRelations(namespaceId);
+ 
+ 		DropAllPreparedStatements();
+ 
+ 		Async_UnlistenAll();
+ 
+ 		/* Delete cursors, including WITH HOLD */
+ 		PortalHashTableDeleteAll();
+ 
+ 		if (IsTransactionBlock())
+ 			UserAbortTransactionBlock();
+ 	}
  	else
  		set_config_option(name,
  						  NULL,
Index: src/backend/utils/mmgr/portalmem.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/mmgr/portalmem.c,v
retrieving revision 1.85
diff -c -c -r1.85 portalmem.c
*** src/backend/utils/mmgr/portalmem.c	5 Mar 2006 15:58:49 -0000	1.85
--- src/backend/utils/mmgr/portalmem.c	25 Apr 2006 14:01:09 -0000
***************
*** 402,407 ****
--- 402,410 ----
  	HASH_SEQ_STATUS status;
  	PortalHashEnt *hentry;
  
+ 	if (PortalHashTable == NULL)
+ 		return;
+ 
  	hash_seq_init(&status, PortalHashTable);
  
  	while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
***************
*** 413,418 ****
--- 416,445 ----
  	}
  }
  
+ /*
+  * Delete all WITH HOLD cursors, used by RESET CONNECTION
+  */
+ void
+ PortalHashTableDeleteAll(void)
+ {
+ 	HASH_SEQ_STATUS status;
+ 	PortalHashEnt *hentry;
+ 
+ 	if (PortalHashTable == NULL)
+ 		return;
+ 
+ 	hash_seq_init(&status, PortalHashTable);
+ 
+ 	while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
+ 	{
+ 		Portal		portal = hentry->portal;
+ 
+ 		if	((portal->cursorOptions & CURSOR_OPT_HOLD) &&
+ 			 portal->status != PORTAL_ACTIVE)
+ 			PortalDrop(portal, false);
+ 	}
+ }
+ 
  
  /*
   * Pre-commit processing for portals.
Index: src/include/catalog/namespace.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/namespace.h,v
retrieving revision 1.39
diff -c -c -r1.39 namespace.h
*** src/include/catalog/namespace.h	5 Mar 2006 15:58:54 -0000	1.39
--- src/include/catalog/namespace.h	25 Apr 2006 14:01:13 -0000
***************
*** 74,79 ****
--- 74,81 ----
  extern Oid	FindConversionByName(List *conname);
  extern Oid	FindDefaultConversionProc(int4 for_encoding, int4 to_encoding);
  
+ extern void RemoveTempRelations(Oid tempNamespaceId);
+ 
  /* initialization & transaction cleanup code */
  extern void InitializeSearchPath(void);
  extern void AtEOXact_Namespace(bool isCommit);
Index: src/include/commands/async.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/async.h,v
retrieving revision 1.31
diff -c -c -r1.31 async.h
*** src/include/commands/async.h	5 Mar 2006 15:58:55 -0000	1.31
--- src/include/commands/async.h	25 Apr 2006 14:01:13 -0000
***************
*** 19,24 ****
--- 19,25 ----
  extern void Async_Notify(const char *relname);
  extern void Async_Listen(const char *relname);
  extern void Async_Unlisten(const char *relname);
+ extern void Async_UnlistenAll(void);
  
  /* perform (or cancel) outbound notify processing at transaction commit */
  extern void AtCommit_Notify(void);
Index: src/include/commands/prepare.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/prepare.h,v
retrieving revision 1.18
diff -c -c -r1.18 prepare.h
*** src/include/commands/prepare.h	5 Mar 2006 15:58:55 -0000	1.18
--- src/include/commands/prepare.h	25 Apr 2006 14:01:15 -0000
***************
*** 62,67 ****
--- 62,68 ----
  extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
  					   bool throwError);
  extern void DropPreparedStatement(const char *stmt_name, bool showError);
+ extern void DropAllPreparedStatements(void);
  extern List *FetchPreparedStatementParams(const char *stmt_name);
  extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
  extern bool PreparedStatementReturnsTuples(PreparedStatement *stmt);
Index: src/include/utils/portal.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/portal.h,v
retrieving revision 1.59
diff -c -c -r1.59 portal.h
*** src/include/utils/portal.h	5 Mar 2006 15:59:07 -0000	1.59
--- src/include/utils/portal.h	25 Apr 2006 14:01:21 -0000
***************
*** 200,205 ****
--- 200,206 ----
  extern void AtSubCleanup_Portals(SubTransactionId mySubid);
  extern Portal CreatePortal(const char *name, bool allowDup, bool dupSilent);
  extern Portal CreateNewPortal(void);
+ extern void PortalHashTableDeleteAll(void);
  extern void PortalDrop(Portal portal, bool isTopCommit);
  extern void DropDependentPortals(MemoryContext queryContext);
  extern Portal GetPortalByName(const char *name);
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.321
diff -c -c -r1.321 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y	7 Mar 2006 01:00:19 -0000	1.321
--- src/interfaces/ecpg/preproc/preproc.y	25 Apr 2006 14:01:30 -0000
***************
*** 1196,1201 ****
--- 1196,1203 ----
  			{ $$ = make_str("reset transaction isolation level"); }
  		| RESET SESSION AUTHORIZATION
  			{ $$ = make_str("reset session authorization"); }
+ 		| RESET CONNECTION
+ 			{ $$ = make_str("reset connection"); }
  		| RESET ALL
  			{ $$ = make_str("reset all"); }
  		;