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"); }
                ;
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to