RESET SESSION, plus related new DDL commands. Patch from Marko Kreen,

reviewed by Neil Conway. This patch adds the following DDL command
variants: RESET SESSION, RESET TEMP, RESET PLANS, CLOSE ALL, and
DEALLOCATE ALL. RESET SESSION is intended for use by connection
pool software and the like, in order to reset a client session
to something close to its initial state.

Note that while most of these command variants can be executed
inside a transaction block (but are not transaction-aware!),
RESET SESSION cannot. While this is inconsistent, it is intended
to catch programmer mistakes: RESET SESSION in an open transaction
block is probably unintended.

3 files changed, 90 insertions, 12 deletions

diff --git a/doc/src/sgml/ref/close.sgml b/doc/src/sgml/ref/close.sgml
index f3f37d95f66..2e5309ff0f2 100644
--- a/doc/src/sgml/ref/close.sgml
+++ b/doc/src/sgml/ref/close.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.24 2006/09/16 00:30:17 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/close.sgml,v 1.25 2007/04/12 06:53:45 neilc Exp $
 PostgreSQL documentation
 -->
 
@@ -25,7 +25,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CLOSE <replaceable class="PARAMETER">name</replaceable>
+CLOSE { <replaceable class="PARAMETER">name</replaceable> | ALL }
 </synopsis>
  </refsynopsisdiv>
 
@@ -44,10 +44,10 @@ CLOSE <replaceable class="PARAMETER">name</replaceable>
    transaction is terminated by <command>COMMIT</command> or
    <command>ROLLBACK</command>.  A holdable cursor is implicitly
    closed if the transaction that created it aborts via
-   <command>ROLLBACK</command>.  If the creating transaction successfully
-   commits, the holdable
-   cursor remains open until an explicit <command>CLOSE</command> is
-   executed, or the client disconnects.
+   <command>ROLLBACK</command>.  If the creating transaction
+   successfully commits, the holdable cursor remains open until an
+   explicit <command>CLOSE</command> is executed, or the client
+   disconnects.
   </para>
  </refsect1>
   
@@ -63,6 +63,16 @@ CLOSE <replaceable class="PARAMETER">name</replaceable>
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><literal>ALL</literal></term>
+    <listitem>
+     <para>
+      Close all open cursors.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
@@ -98,7 +108,9 @@ CLOSE liahona;
   <title>Compatibility</title>
   
   <para>
-   <command>CLOSE</command> is fully conforming with the SQL standard.
+   <command>CLOSE</command> is fully conforming with the SQL
+   standard. <command>CLOSE ALL</> is a <productname>PostgreSQL</>
+   extension.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/deallocate.sgml b/doc/src/sgml/ref/deallocate.sgml
index 2038767c0d4..26fee6aa06b 100644
--- a/doc/src/sgml/ref/deallocate.sgml
+++ b/doc/src/sgml/ref/deallocate.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.9 2006/09/16 00:30:18 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/deallocate.sgml,v 1.10 2007/04/12 06:53:46 neilc Exp $
 PostgreSQL documentation
 -->
 
@@ -25,7 +25,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-DEALLOCATE [ PREPARE ] <replaceable class="parameter">name</replaceable>
+DEALLOCATE [ PREPARE ] { <replaceable class="parameter">name</replaceable> | ALL }
 </synopsis>
  </refsynopsisdiv>
 
@@ -65,6 +65,15 @@ DEALLOCATE [ PREPARE ] <replaceable class="parameter">name</replaceable>
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><literal>ALL</literal></term>
+    <listitem>
+     <para>
+      Deallocate all prepared statements.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/reset.sgml b/doc/src/sgml/ref/reset.sgml
index 84426d2cb6a..e94ef0b8d57 100644
--- a/doc/src/sgml/ref/reset.sgml
+++ b/doc/src/sgml/ref/reset.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/reset.sgml,v 1.32 2006/09/16 00:30:19 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/reset.sgml,v 1.33 2007/04/12 06:53:46 neilc Exp $
 PostgreSQL documentation
 -->
 
@@ -22,9 +22,10 @@ PostgreSQL documentation
 <synopsis>
 RESET <replaceable class="PARAMETER">configuration_parameter</replaceable>
 RESET ALL
+RESET { PLANS | SESSION | TEMP | TEMPORARY }
 </synopsis>
  </refsynopsisdiv>
-  
+
  <refsect1>
   <title>Description</title>
 
@@ -41,7 +42,7 @@ SET <replaceable class="parameter">configuration_parameter</replaceable> TO DEFA
 
   <para>
    The default value is defined as the value that the parameter would
-   have had, had no <command>SET</> ever been issued for it in the
+   have had, if no <command>SET</> ever been issued for it in the
    current session.  The actual source of this value might be a
    compiled-in default, the configuration file, command-line options,
    or per-database or per-user default settings.  See <xref
@@ -52,6 +53,15 @@ SET <replaceable class="parameter">configuration_parameter</replaceable> TO DEFA
    See the <command>SET</> reference page for details on the
    transaction behavior of <command>RESET</>.
   </para>
+
+  <para>
+   <command>RESET</> can also be used to release internal resources
+   that are usually released at the end of session.  <command>RESET
+   TEMP</> drops all temporary tables created in the current session.
+   <command>RESET PLANS</> releases all internally cached plans.
+   <command>RESET SESSION</> releases all externally visible temporary
+   resources associated with the current session.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -76,10 +86,57 @@ SET <replaceable class="parameter">configuration_parameter</replaceable> TO DEFA
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><literal>TEMP, TEMPORARY</literal></term>
+    <listitem>
+     <para>
+      Drops all temporary tables created in the current session.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>PLANS</literal></term>
+    <listitem>
+     <para>
+      Releases all cached query plans.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>SESSION</literal></term>
+    <listitem>
+     <para>
+      Releases all temporary resources associated with the current
+      session. This has the same effect as executing the following
+      command sequence:
+<synopsis>
+SET SESSION AUTHORIZATION DEFAULT;
+RESET ALL;
+DEALLOCATE ALL;
+CLOSE ALL;
+UNLISTEN *;
+RESET PLANS;
+RESET TEMP;
+</synopsis>
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
  <refsect1>
+  <title>Notes</title>
+
+   <para>
+    <command>RESET SESSION</> cannot be executed inside a transaction block.
+   </para>
+ </refsect1>
+
+ <refsect1>
   <title>Examples</title>
 
   <para>