Implementing RESET CONNECTION ...

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

*** ./doc/src/sgml/ref/reset.sgml.orig    Thu Dec 30 12:29:14 2004
--- ./doc/src/sgml/ref/reset.sgml    Thu Dec 30 12:58:41 2004
***************
*** 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,17 ----

   <refnamediv>
    <refname>RESET</refname>
!   <refpurpose>reset connection or restore the value of a run-time parameter to the default value</refpurpose>
   </refnamediv>

   <indexterm zone="sql-reset">
***************
*** 20,25 ****
--- 20,26 ----

   <refsynopsisdiv>
  <synopsis>
+ RESET <replaceable class="PARAMETER">connection</replaceable>
  RESET <replaceable class="PARAMETER">name</replaceable>
  RESET ALL
  </synopsis>
***************
*** 52,57 ****
--- 53,66 ----
     See the <command>SET</> reference page for details on the
     transaction behavior of <command>RESET</>.
    </para>
+
+   <para>
+    <command>RESET CONNECTION</command> can be used to reset the entire
+    backend. This includes temporary tables, open transactions, prepared
+    statements, <literal>WITH HOLD</literal> cursors runtime parameters
+    as well as asynchronous backend settings.
+   </para>
+
   </refsect1>

   <refsect1>
***************
*** 76,82 ****
--- 85,103 ----
       </para>
      </listitem>
     </varlistentry>
+
+    <varlistentry>
+     <term><literal>CONNECTION</literal></term>
+     <listitem>
+      <para>
+       Reset the entire backend including temporary tables, open transactions,
+       prepared statements, <literal>WITH HOLD</literal> cursors runtime
+       parameters as well as asynchronous backend settings.
+      </para>
+     </listitem>
+    </varlistentry>
    </variablelist>
+
   </refsect1>

   <refsect1>
*** ./src/backend/catalog/namespace.c.orig    Tue Dec 28 11:13:08 2004
--- ./src/backend/catalog/namespace.c    Tue Dec 28 12:05:37 2004
***************
*** 135,141 ****
  /* 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);

--- 135,140 ----
***************
*** 1772,1778 ****
   * in order to clean out any relations that might have been created by
   * a crashed backend.
   */
! static void
  RemoveTempRelations(Oid tempNamespaceId)
  {
      ObjectAddress object;
--- 1771,1777 ----
   * in order to clean out any relations that might have been created by
   * a crashed backend.
   */
! void
  RemoveTempRelations(Oid tempNamespaceId)
  {
      ObjectAddress object;
*** ./src/backend/commands/async.c.orig    Mon Dec 27 21:36:10 2004
--- ./src/backend/commands/async.c    Mon Dec 27 21:38:04 2004
***************
*** 128,134 ****
  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);
--- 128,133 ----
***************
*** 345,351 ****
   *
   *--------------------------------------------------------------
   */
! static void
  Async_UnlistenAll(void)
  {
      Relation    lRel;
--- 344,350 ----
   *
   *--------------------------------------------------------------
   */
! void
  Async_UnlistenAll(void)
  {
      Relation    lRel;
*** ./src/backend/commands/prepare.c.orig    Tue Dec 28 12:45:58 2004
--- ./src/backend/commands/prepare.c    Tue Dec 28 20:22:06 2004
***************
*** 28,34 ****
  #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.
--- 28,33 ----
***************
*** 452,457 ****
--- 451,484 ----
  }

  /*
+  * Remove all prepared plans from the backend.
+  */
+ void
+ DropAllPreparedStatements(void)
+ {
+     PreparedStatement    *prep_statement;
+     HASH_SEQ_STATUS         status;
+
+     hash_seq_init(&status, prepared_queries);
+
+     /* we have to check for an empty hash here because
+      * otherwise the backend won't be able to loop through
+      * the hash */
+     if    (!prepared_queries)
+         return;
+
+     /* we will quit one plan after the other */
+     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.
*** ./src/backend/parser/gram.y.orig    Mon Dec 27 18:53:10 2004
--- ./src/backend/parser/gram.y    Mon Dec 27 18:54:26 2004
***************
*** 341,348 ****
      CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
!     CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
      CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
--- 341,348 ----
      CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONNECTION CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY
!     CREATE CREATEDB CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
      CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
***************
*** 1075,1080 ****
--- 1075,1086 ----
                      n->name = $2;
                      $$ = (Node *) n;
                  }
+             | RESET CONNECTION
+                 {
+                     VariableResetStmt *n = makeNode(VariableResetStmt);
+                     n->name = "connection";
+                     $$ = (Node *) n;
+                 }
              | RESET TIME ZONE
                  {
                      VariableResetStmt *n = makeNode(VariableResetStmt);
*** ./src/backend/utils/misc/guc.c.orig    Mon Dec 27 19:13:40 2004
--- ./src/backend/utils/misc/guc.c    Thu Dec 30 11:29:08 2004
***************
*** 28,33 ****
--- 28,34 ----
  #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"
***************
*** 55,65 ****
  #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
--- 56,68 ----
  #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
***************
*** 4302,4309 ****
  void
  ResetPGVariable(const char *name)
  {
!     if (pg_strcasecmp(name, "all") == 0)
          ResetAllOptions();
      else
          set_config_option(name,
                            NULL,
--- 4305,4350 ----
  void
  ResetPGVariable(const char *name)
  {
!     char            namespaceName[NAMEDATALEN];
!     Oid            namespaceId;
!
!     /* resetting all GUC variables */
!     if     (pg_strcasecmp(name, "all") == 0)
!         ResetAllOptions();
!
!     /* in case of connection pools it can be desirable to reset the
!      * entire connection not just a single GUC variable.
!      * what we do is: reset GUCs, remove temp tables, UNLISTEN *,
!      * remove prepared plans as well as open cursors */
!     else if    (pg_strcasecmp(name, "connection") == 0)
!     {
!         /* resetting those GUC values */
          ResetAllOptions();
+
+         /* cleaning temp-tables:
+          * what we do here is to find our namespace. then we can simply
+          * delete those temp tables with the help of onboard
+          * functions */
+         snprintf(namespaceName, sizeof(namespaceName), "pg_temp_%d", MyBackendId);
+         namespaceId = GetSysCacheOid(NAMESPACENAME,
+             CStringGetDatum(namespaceName), 0, 0, 0);
+         RemoveTempRelations(namespaceId);
+
+         /* now we will cleanup the prepared queries stored inside our backend */
+         DropAllPreparedStatements();
+
+         /* UNLISTEN * */
+         Async_UnlistenAll();
+
+         /* now we can delete all open cursors. we simply delete all
+          * open cursors because none WITH HOLD cursors would go away at the
+          * end of the transaction anyway */
+         PortalHashTableDeleteAll();
+
+         /* checking for transaction blocks */
+         if    (IsTransactionBlock())
+             UserAbortTransactionBlock();
+     }
      else
          set_config_option(name,
                            NULL,
*** ./src/backend/utils/mmgr/portalmem.c.orig    Wed Dec 29 16:13:21 2004
--- ./src/backend/utils/mmgr/portalmem.c    Thu Dec 30 11:33:17 2004
***************
*** 399,404 ****
--- 399,407 ----
      HASH_SEQ_STATUS status;
      PortalHashEnt *hentry;

+     if    (PortalHashTable == NULL)
+         return;
+
      hash_seq_init(&status, PortalHashTable);

      while ((hentry = (PortalHashEnt *) hash_seq_search(&status)) != NULL)
***************
*** 410,415 ****
--- 413,445 ----
      }
  }

+ /*
+  * Delete all WITH HOLD cursors
+  *
+  * This function is used to reset the backend in the case of
+  * RESET CONNECTION statements.
+  */
+ void
+ PortalHashTableDeleteAll(void)
+ {
+     HASH_SEQ_STATUS status;
+     PortalHashEnt *hentry;
+
+     hash_seq_init(&status, PortalHashTable);
+
+     if    (PortalHashTable == NULL)
+         return;
+
+     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.
*** ./src/bin/psql/sql_help.h.orig    Thu Dec 30 13:07:05 2004
--- ./src/bin/psql/sql_help.h    Thu Dec 30 13:08:28 2004
***************
*** 363,369 ****

      { "RESET",
        N_("restore the value of a run-time parameter to the default value"),
!       N_("RESET name\nRESET ALL") },

      { "REVOKE",
        N_("remove access privileges"),
--- 363,369 ----

      { "RESET",
        N_("restore the value of a run-time parameter to the default value"),
!       N_("RESET name\nRESET ALL\nRESET CONNECTION") },

      { "REVOKE",
        N_("remove access privileges"),
*** ./src/include/catalog/namespace.h.orig    Tue Dec 28 11:13:38 2004
--- ./src/include/catalog/namespace.h    Tue Dec 28 11:15:01 2004
***************
*** 88,93 ****
--- 88,95 ----
  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);
*** ./src/include/commands/async.h.orig    Mon Dec 27 21:36:20 2004
--- ./src/include/commands/async.h    Mon Dec 27 21:36:43 2004
***************
*** 19,24 ****
--- 19,25 ----
  extern void Async_Notify(char *relname);
  extern void Async_Listen(char *relname, int pid);
  extern void Async_Unlisten(char *relname, int pid);
+ extern void Async_UnlistenAll(void);

  /* perform (or cancel) outbound notify processing at transaction commit */
  extern void AtCommit_Notify(void);
*** ./src/include/commands/prepare.h.orig    Thu Dec 30 13:20:29 2004
--- ./src/include/commands/prepare.h    Thu Dec 30 13:20:05 2004
***************
*** 55,60 ****
--- 55,61 ----
                         List *argtype_list);
  extern PreparedStatement *FetchPreparedStatement(const char *stmt_name,
                         bool throwError);
+ extern void DropAllPreparedStatements(void);
  extern void DropPreparedStatement(const char *stmt_name, bool showError);
  extern List *FetchPreparedStatementParams(const char *stmt_name);
  extern TupleDesc FetchPreparedStatementResultDesc(PreparedStatement *stmt);
*** ./src/include/utils/portal.h.orig    Wed Dec 29 16:14:47 2004
--- ./src/include/utils/portal.h    Wed Dec 29 16:16:17 2004
***************
*** 194,199 ****
--- 194,200 ----
  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);
*** ./src/interfaces/ecpg/preproc/preproc.y.orig    Thu Dec 30 11:40:28 2004
--- ./src/interfaces/ecpg/preproc/preproc.y    Thu Dec 30 12:01:33 2004
***************
*** 359,365 ****
          CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY
          CREATE CREATEDB CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
          CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

--- 359,365 ----
          CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONNECTION CONVERT COPY
          CREATE CREATEDB CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
          CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

***************
*** 1157,1162 ****
--- 1157,1164 ----
              { $$ = 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"); }
          ;