Re: Implementing RESET CONNECTION ... - Mailing list pgsql-patches

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"); }
          ;