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

On Thu, 30 Dec 2004, [ISO-8859-1] 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.
>

From the JDBC driver's perspective this doesn't meet the needs I'd like to
see in a connection reset.  In the initial connection setup a number of
GUC variables are tweaked to what the JDBC driver desires (DateStyle,
client_encoding).  When resetting we'd want to reset to this point, not
the default values.  Perhaps some kind of MARK command, kind of like a
savepoint to rollback to would better specify this.

Also I don't like the idea of cleaning up prepared statements.  While it
doesn't do so now, the JDBC driver would like to do statement pooling at
some point.  This means the same underlying server prepared statement can
be reused transparently from multiple callers.  In a connection pool where
a connection is grabbed and returned for virtually each sql execution this
is key to getting the performance boost from prepared statements.  We
don't want to have to reprepare on each connection and we don't want them
to disappear from underneath us, because the prepared statements are
generated transparently by the JDBC driver, not directly by a user
statement.

Kris Jurka

Kris,

I have seen that the JDBC driver is doing some GUC settings.
However, this does not prevent you from bad users who change GUCs for
some reason. It might very well happen that a user sets the DateStyle to
some different value temporarily. A different program would in this case
behave RANDOMLY depending on the connection assigned by the pool
The basic idea is that all GUCs are cleaned up because they might have
been changed.
Personally I expect a new connection to be clean.

The same applies to prepared statements - different programs (let's say
websites) might give plans the same name and this would lead to RANDOM
conflicts (depending on which connection you get from the pool).
However, they still might share the same connection pool.

As far as prepared statements are concerned: Maybe a tablefunction
pg_list_prepared_plans() might make sense - you could use that for your
purpose (the same applies to cursors).
Actually I was thinking of including DEALLOCATE ALL into this plan so
that just prepared plans can be deallocated as well.

I don't think a mark would make too much sense.
If partial resets are really desirable it is better to add RESET
CURSORS, RESET PREPARED STATEMENTS, ...
Fell free to add code :).

Best regards,

Hans


Kris Jurka wrote:

>On Thu, 30 Dec 2004, [ISO-8859-1] 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.
>>
>>
>>
>
From the JDBC driver's perspective this doesn't meet the needs I'd like to
>see in a connection reset.  In the initial connection setup a number of
>GUC variables are tweaked to what the JDBC driver desires (DateStyle,
>client_encoding).  When resetting we'd want to reset to this point, not
>the default values.  Perhaps some kind of MARK command, kind of like a
>savepoint to rollback to would better specify this.
>
>Also I don't like the idea of cleaning up prepared statements.  While it
>doesn't do so now, the JDBC driver would like to do statement pooling at
>some point.  This means the same underlying server prepared statement can
>be reused transparently from multiple callers.  In a connection pool where
>a connection is grabbed and returned for virtually each sql execution this
>is key to getting the performance boost from prepared statements.  We
>
>
>don't want to have to reprepare on each connection and we don't want them
>to disappear from underneath us, because the prepared statements are
>generated transparently by the JDBC driver, not directly by a user
>statement.
>
>Kris Jurka
>
>

On Mon, 3 Jan 2005, [UTF-8] Hans-Jürgen Schönig wrote:

> I have seen that the JDBC driver is doing some GUC settings.
> However, this does not prevent you from bad users who change GUCs for
> some reason.

Actually it does.  The V3 protocol provides a ParameterStatus message that
notifies us when certain GUC parameters are modified.  If someone changes
the DateStyle underneath us, we throw an Exception and destroy the
connection.

> The same applies to prepared statements - different programs (let's say
> websites) might give plans the same name and this would lead to RANDOM
> conflicts (depending on which connection you get from the pool).
> However, they still might share the same connection pool.

Let me explain a little more how this works from the JDBC driver's
perspective.  The API for getting a PreparedStatement object is:

PreparedStatement pstmt = Connection.prepareStatement(String sql);

The sql string may have placeholders to indicate where parameters go.
From this API the JDBC driver can do one of three things with the
PreparedStatement object when it is executed.

1) It can do the parameter substituition directly on the driver side and
send a simple query to the server.

2) It can use an unnamed statement to execute the query sending the
parameters separately.

3) It can use a named statement to execute the query sending the
parameters separately.

We are really only interested in the third case here, because this is the
only one that leaves a permanent server state.  The namespace for protocol
executed named statements is shared with sql executed PREPARE commands, so
this is applicable to the RESET command you've implemented.

Note that the user has never provided a name for this named statement.
The JDBC driver uses S_N where N is an incrementing number per connection,
so there will be no conflicts.  What we'd like the driver to eventually do
is detect the following condition:

PreparedStatement ps1 = conn.prepareStatement(sql);
PreparedStatement ps2 = conn.prepareStatement(sql);

Since both PreparedStatements are derived from the same sql string it
would be nice if they could use the same underlying S_N server named
statement instead of creating two identical ones.  Now consider this in a
connection pool:

Connection conn;
PreparedStatement ps;

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

This situation is slightly different because we may or may not have gotten
the same connection back, but we don't really care.  We only want to know
if whatever connection we currently have has already seen and prepared the
sql string we are looking for.  If we add the RESET you've implemented
then it will never have a pre-prepared statement for us to use, so we'll
have to create a new one every time.

Kris Jurka

Kris Jurka <books@ejurka.com> writes:
> On Thu, 30 Dec 2004, [ISO-8859-1] 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.

> From the JDBC driver's perspective this doesn't meet the needs I'd like to
> see in a connection reset.  In the initial connection setup a number of
> GUC variables are tweaked to what the JDBC driver desires (DateStyle,
> client_encoding).  When resetting we'd want to reset to this point, not
> the default values.

I haven't looked at the proposed patch, but I would've expected that it
duplicates the existing RESET ALL behavior for GUC settings.  And that
already works the way you want.  Values taken from the client connection
request packet establish the session defaults, ie, what RESET goes to.

> Also I don't like the idea of cleaning up prepared statements.

Hmm.  This seems a bit eye-of-the-beholder-ish, ie you could make a
legitimate argument either way.  Maybe the RESET CONNECTION command
should have an option whether to zap prepared statements or not?
Is there anything else falling in the category of "debatable"?

            regards, tom lane

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>
>>Also I don't like the idea of cleaning up prepared statements.
>
>
> Hmm.  This seems a bit eye-of-the-beholder-ish, ie you could make a
> legitimate argument either way.  Maybe the RESET CONNECTION command
> should have an option whether to zap prepared statements or not?

That doesn't really help the JDBC driver case. The problem is that there
are prepared statements that have been set up by the driver invisibly to
the user. Zapping them will make the driver break, and it's too easy for
the user code to do a full RESET CONNECTION and accidently zap them.

Yes, you can break the JDBC driver currently by doing explicit
DEALLOCATEs of its prepared statements -- but you have to do that quite
deliberately so it's less of a problem.

Having notification of either prepared statement deallocation or
connection reset (a la ParameterStatus for GUC changes) would help the
driver to recover from this case.

-O

Resending to the correctly-spelled list alias. Why does postgresql.org
not generate bounces for unknown addresses, anyway?

-------- Original Message --------
Subject: Re: [PATCHES] Implementing RESET CONNECTION ...
Date: Tue, 04 Jan 2005 13:58:44 +1300
From: Oliver Jowett <oliver@opencloud.com>
To: Hans-Jürgen Schönig <postgres@cybertec.at>
CC: pgsql-patche@postgresql.org,  pgman@candle.pha.pa.us,
eg@cybertec.at,  books@ejurka.com
References: <41D3F834.9090706@cybertec.at>

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.

Perhaps this should be done at the protocol level (as a new message
type), not as a SQL command, since it is dealing primarily with
per-connection protocol state.

As Kris has mentioned elsewhere in the thread, things like the JDBC
driver really don't want the connection state unexpectedly being changed
under them by user queries (and go to some lengths to detect and
complain about it if they do, e.g. by monitoring ParameterStatus messages).

If you do it at the protocol level, the semantics can be a bit more
obvious: a reset packet resets the backend and protocol state to "as if
just connected" state. Structure the reset packet like the initial
startup packet; don't allow changing the user or database. So you get
the same state as doing a disconnect/reconnect but presumably it is
cheaper as you don't need to do authentication/fork/initialization.

If this does go in at the SQL command level, can we have a
protocol-level notification that the connection has been reset please?
The JDBC driver needs to notice the reset to continue to operate
correctly after a reset. I can't see an obvious way to crowbar this into
the current protocol unless we overload something like ParameterStatus
which seems ugly.

-O

Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> Maybe the RESET CONNECTION command
>> should have an option whether to zap prepared statements or not?

> That doesn't really help the JDBC driver case. The problem is that there
> are prepared statements that have been set up by the driver invisibly to
> the user. Zapping them will make the driver break, and it's too easy for
> the user code to do a full RESET CONNECTION and accidently zap them.

Fair point, but you could make the same argument against *any* side
effect of RESET CONNECTION.  You're just complaining about PREPARE
because you can see immediately where that breaks JDBC.  Anything that
any driver does to set up per-connection state the way it wants will
be equally vulnerable.

> Having notification of either prepared statement deallocation or
> connection reset (a la ParameterStatus for GUC changes) would help the
> driver to recover from this case.

I'm inclined to think that we'd have to add a protocol message that
reports RESET CONNECTION to really answer objections of this type.
That seems to bring the thing into the category of "stuff that forces
a protocol version bump" :-(

Perhaps RESET CONNECTION should be a protocol-level operation instead
of a SQL command?  That would prevent user-level code from causing it
without the driver knowing.

            regards, tom lane

Tom Lane wrote:

> Fair point, but you could make the same argument against *any* side
> effect of RESET CONNECTION.  You're just complaining about PREPARE
> because you can see immediately where that breaks JDBC.  Anything that
> any driver does to set up per-connection state the way it wants will
> be equally vulnerable.

Yes, exactly.

> Perhaps RESET CONNECTION should be a protocol-level operation instead
> of a SQL command?  That would prevent user-level code from causing it
> without the driver knowing.

I just suggested as much in another email (our emails crossed).

-O

On Mon, 2005-01-03 at 20:27 -0500, Tom Lane wrote:

> I'm inclined to think that we'd have to add a protocol message that
> reports RESET CONNECTION to really answer objections of this type.
> That seems to bring the thing into the category of "stuff that forces
> a protocol version bump" :-(
>
> Perhaps RESET CONNECTION should be a protocol-level operation instead
> of a SQL command?  That would prevent user-level code from causing it
> without the driver knowing.

I still don't see a big difference between DEALLOCATE and RESET -- both
can break the JDBC driver. I'm not sure if we need prevent bad usage of
PG tools (JDBC in this case). The DEALLOCATE/RESET usage is under user's
full control and everything can be described in docs.

I think each PG command returns some status. For example in libpq it's
possible check by PQcmdStatus(). I think JDBC can checks this status (by
own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
RESETED" it can deallocate internal stuff. This solution doesn't require
touch the protocol.

    Karel

--
Karel Zak <zakkr@zf.jcu.cz>

I completely agree with Karel. I think it is a bad idea to change the
protocol for such a minor feature - i tend to call it overkill.
I want to add one point to this discussion: There is not just JDBC -
other connection pools or clients might want different behaviour (which
can from my point of view only lead to a complete reset).

If the JDBC driver prefers different behaviour (maybe for prepared
statements) we should discuss further options for RESET.
Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
(cleaning GUCS only) and RESET some_guc.
Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
Personally I think this is not a good idea.

    Regards,

        Hans



Karel Zak wrote:
> On Mon, 2005-01-03 at 20:27 -0500, Tom Lane wrote:
>
>
>>I'm inclined to think that we'd have to add a protocol message that
>>reports RESET CONNECTION to really answer objections of this type.
>>That seems to bring the thing into the category of "stuff that forces
>>a protocol version bump" :-(
>>
>>Perhaps RESET CONNECTION should be a protocol-level operation instead
>>of a SQL command?  That would prevent user-level code from causing it
>>without the driver knowing.
>
>
> I still don't see a big difference between DEALLOCATE and RESET -- both
> can break the JDBC driver. I'm not sure if we need prevent bad usage of
> PG tools (JDBC in this case). The DEALLOCATE/RESET usage is under user's
> full control and everything can be described in docs.
>
> I think each PG command returns some status. For example in libpq it's
> possible check by PQcmdStatus(). I think JDBC can checks this status (by
> own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> RESETED" it can deallocate internal stuff. This solution doesn't require
> touch the protocol.
>
>     Karel
>


--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at

On Tue, 4 Jan 2005, [ISO-8859-1] Hans-J�rgen Sch�nig wrote:

> I completely agree with Karel. I think it is a bad idea to change the
> protocol for such a minor feature - i tend to call it overkill.

I agree.  I don't think it's imperative to prevent or detect this
condition.  The only real caller of this should be the driver itself.  If
the end user does call this and breaks things I wouldn't consider it our
problem.  Making this available at the protocol level only would certainly
solve that, but it's not really compelling to make a protocol level jump.

> I want to add one point to this discussion: There is not just JDBC -
> other connection pools or clients might want different behaviour (which
> can from my point of view only lead to a complete reset).

Right, I am speaking from the JDBC driver perspective, but I think any
higher level interface should desire to do statement pooling, which will
have this problem.  You have not stated what client interface you are
targetting, but I believe anything written to a higher level than libpq
will need to be aware of this.  Perhaps -patches isn't the right place to
solicit input for this.

Kris Jurka

Karel Zak wrote:

> I still don't see a big difference between DEALLOCATE and RESET -- both
> can break the JDBC driver.

You have to go out of your way to break the driver via DEALLOCATE,
explicitly finding a statement name that the driver is using. There's
also a reasonably simple fix: make the protocol-level and
PREPARE/DEALLOCATE namespaces separate. There's been some discussion
about doing this in the past.

In contrast RESET CONNECTION, by design, resets many things without
needing to explicitly list them. The user could easily reset connection
state that a driver is relying on without realizing it.

> I think each PG command returns some status. For example in libpq it's
> possible check by PQcmdStatus(). I think JDBC can checks this status (by
> own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> RESETED" it can deallocate internal stuff. This solution doesn't require
> touch the protocol.

That could work. It's a bit ugly, though, as currently drivers don't
need to parse command status strings (unless they want an insert OID)

-O

Hans-Jürgen Schönig wrote:

> If the JDBC driver prefers different behaviour (maybe for prepared
> statements) we should discuss further options for RESET.
> Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
> (cleaning GUCS only) and RESET some_guc.
> Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
> Personally I think this is not a good idea.

It doesn't help, either, if user code can still issue RESET CONNECTION.
(the scenario is user code, not the driver itself, originating the RESET..)

-O

On Wed, 2005-01-05 at 01:33 +1300, Oliver Jowett wrote:
> Karel Zak wrote:
> > I think each PG command returns some status. For example in libpq it's
> > possible check by PQcmdStatus(). I think JDBC can checks this status (by
> > own PQcmdStatus() implementation) and if PG returns string "CONNECTION-
> > RESETED" it can deallocate internal stuff. This solution doesn't require
> > touch the protocol.
>
> That could work. It's a bit ugly, though, as currently drivers don't
> need to parse command status strings (unless they want an insert OID)

I think command status is common and nice feedback for client. I think
it's more simple change something in JDBC than change protocol that is
shared between more tools.

We need some common way how detect on client what's happen on server --
a way that doesn't mean change protocol always when we add some
feature/command to backend. The command status is possible use for this.

    Karel

--
Karel Zak <zakkr@zf.jcu.cz>

(cc'ing -hackers)

Karel Zak wrote:

> I think command status is common and nice feedback for client. I think
> it's more simple change something in JDBC than change protocol that is
> shared between more tools.

There is a bit of a queue of changes that would be nice to have but
require a protocol version change. If we're going to change the protocol
for any of those we might as well handle RESET CONNECTION cleanly too.

> We need some common way how detect on client what's happen on server --
> a way that doesn't mean change protocol always when we add some
> feature/command to backend. The command status is possible use for this.

Command status only works if commands are directly executed. If you can
execute the command indirectly, e.g. via a PL, then you'll miss the
notification. Making RESET a top-level-only command isn't unreasonable,
but using command status won't work as a general approach for notifying
clients.

We have a mechanism for GUC changes that uses a separate message
(ParameterStatus). Perhaps that should be generalized to report
different sorts of connection-related changes.

-O

Hans-J�rgen Sch�nig wrote:
> I completely agree with Karel. I think it is a bad idea to change the
> protocol for such a minor feature - i tend to call it overkill.
> I want to add one point to this discussion: There is not just JDBC -
> other connection pools or clients might want different behaviour (which
> can from my point of view only lead to a complete reset).
>
> If the JDBC driver prefers different behaviour (maybe for prepared
> statements) we should discuss further options for RESET.
> Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
> (cleaning GUCS only) and RESET some_guc.
> Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
> Personally I think this is not a good idea.

I think autocommit is a good example for comparison.  One big problem
was that some users had autocommit in their server configs on startup
and that caused scripts to fail.  I don't imagine anyone would add RESET
CONNECTION in their startup script.

However, I can imagine someone doing RESET CONNECTION from JDBC and the
interface should continue working.  Should we add something like SET
CONNECTION that would set the reset values for RESET CONNECTION?  JDBC
could then use SET CONNECTION and then any RESET CONNECTION would reset
back to that point.  RESET has a similar capability where when you RESET
you reset to the connection defaults, not to the defaults from
postgresql.conf.

Also, let me mention PHP uses connection pooling and wants to use RESET
CONNECTION too.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Hans-J�rgen Sch�nig wrote:
>
>>I completely agree with Karel. I think it is a bad idea to change the
>>protocol for such a minor feature - i tend to call it overkill.
>>I want to add one point to this discussion: There is not just JDBC -
>>other connection pools or clients might want different behaviour (which
>>can from my point of view only lead to a complete reset).
>>
>>If the JDBC driver prefers different behaviour (maybe for prepared
>>statements) we should discuss further options for RESET.
>>Now there is: RESET CONNECTION (cleaning entire connection), RESET ALL
>>(cleaning GUCS only) and RESET some_guc.
>>Maybe we want RESET LISTENER, RESET PREPARED, RESET CURSORS.
>>Personally I think this is not a good idea.
>
>
> I think autocommit is a good example for comparison.  One big problem
> was that some users had autocommit in their server configs on startup
> and that caused scripts to fail.  I don't imagine anyone would add RESET
> CONNECTION in their startup script.
>
> However, I can imagine someone doing RESET CONNECTION from JDBC and the
> interface should continue working.  Should we add something like SET
> CONNECTION that would set the reset values for RESET CONNECTION?  JDBC
> could then use SET CONNECTION and then any RESET CONNECTION would reset
> back to that point.  RESET has a similar capability where when you RESET
> you reset to the connection defaults, not to the defaults from
> postgresql.conf.
>
> Also, let me mention PHP uses connection pooling and wants to use RESET
> CONNECTION too.
>


Shouldn't these be default values set in postgresql.conf rather than
copied settings inside the backend?

There is always a way to modify a GUC or to change the SET CONNECTION
value (maybe a SELECT statement "SELECT change_value"). I have seen
people doing that millions of time (I know, it is a bad idea).

Personally I'd expect a fully cleanup backend rather than a half-cleaned
up backend. Setting two or three parameters when JDBC provides a
recycled connection should not be a problem.

As far as prepared plans are concerned: There used to be a discussion
about storing prepared plans accross connections (I recall even seeing
some working code by Joe doing exactly that). It was rejected due to
issues related to plan stability. This is exactly the same scenario now
- keeping prepared plans actually means storing them accross "connection
boundaries" (from a logical point of view it is a new connection - even
is the backend is an old one).

If we want the behaviour proposed by JDBC we shouldn't call it RESET
CONNECTION - maybe RESET STATUS or something like that). To me it is a
totally different thing.

    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 <pgman@candle.pha.pa.us> writes:
> Hans-J�rgen Sch�nig wrote:
>> I completely agree with Karel. I think it is a bad idea to change the
>> protocol for such a minor feature - i tend to call it overkill.

> I think autocommit is a good example for comparison.

Indeed, it is an *excellent* example for comparison.  The real problem
with autocommit was that it changed the interface semantics without
making that change sufficiently visible at all levels.

If we try to pretend that RESET CONNECTION isn't a protocol change
then we will silently break code that needs to know about it.  Which is
pretty much exactly what happened with autocommit.

> Should we add something like SET
> CONNECTION that would set the reset values for RESET CONNECTION?

This is an even bigger compatibility-breaker, as now anyone who can
issue SET CONNECTION can not only break code layers that were trying to
track backend state, he can break code layers that thought they knew
what RESET CONNECTION would accomplish.  I definitely recommend against
this idea.

            regards, tom lane

What did we decide on RESET CONNECTION.  Do we want an SQL command or
something only the protocol can do?

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

Oliver Jowett wrote:
> (cc'ing -hackers)
>
> Karel Zak wrote:
>
> > I think command status is common and nice feedback for client. I think
> > it's more simple change something in JDBC than change protocol that is
> > shared between more tools.
>
> There is a bit of a queue of changes that would be nice to have but
> require a protocol version change. If we're going to change the protocol
> for any of those we might as well handle RESET CONNECTION cleanly too.
>
> > We need some common way how detect on client what's happen on server --
> > a way that doesn't mean change protocol always when we add some
> > feature/command to backend. The command status is possible use for this.
>
> Command status only works if commands are directly executed. If you can
> execute the command indirectly, e.g. via a PL, then you'll miss the
> notification. Making RESET a top-level-only command isn't unreasonable,
> but using command status won't work as a general approach for notifying
> clients.
>
> We have a mechanism for GUC changes that uses a separate message
> (ParameterStatus). Perhaps that should be generalized to report
> different sorts of connection-related changes.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

What would be absolutely ideal is a reset connection command, plus some
way of knowing via the protocol if it's needed or not.

Chris

Bruce Momjian wrote:
> What did we decide on RESET CONNECTION.  Do we want an SQL command or
> something only the protocol can do?
>
> ---------------------------------------------------------------------------
>
> Oliver Jowett wrote:
>
>>(cc'ing -hackers)
>>
>>Karel Zak wrote:
>>
>>
>>>I think command status is common and nice feedback for client. I think
>>>it's more simple change something in JDBC than change protocol that is
>>>shared between more tools.
>>
>>There is a bit of a queue of changes that would be nice to have but
>>require a protocol version change. If we're going to change the protocol
>>for any of those we might as well handle RESET CONNECTION cleanly too.
>>
>>
>>>We need some common way how detect on client what's happen on server --
>>>a way that doesn't mean change protocol always when we add some
>>>feature/command to backend. The command status is possible use for this.
>>
>>Command status only works if commands are directly executed. If you can
>>execute the command indirectly, e.g. via a PL, then you'll miss the
>>notification. Making RESET a top-level-only command isn't unreasonable,
>>but using command status won't work as a general approach for notifying
>>clients.
>>
>>We have a mechanism for GUC changes that uses a separate message
>>(ParameterStatus). Perhaps that should be generalized to report
>>different sorts of connection-related changes.
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>
>

Christopher Kings-Lynne wrote:
> What would be absolutely ideal is a reset connection command, plus some
> way of knowing via the protocol if it's needed or not.

And a way of notifying the client that a reset has happened.

-O

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

I  have backed out the patch until there is more discussion.

I now see that the CC list had an incorrect entry for the patches list,
so I am unsure if others have seen this patch thoroughly.

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

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. +