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.

6 files changed, 198 insertions, 0 deletions

diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out
index ca1b582630..484cc356e7 100644
--- a/src/test/regress/expected/guc.out
+++ b/src/test/regress/expected/guc.out
@@ -425,3 +425,103 @@ SELECT '2006-08-13 12:34:56'::timestamptz;
  Sun Aug 13 12:34:56 2006 PDT
 (1 row)
 
+--
+-- Test RESET TEMP
+--
+CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+  relname   
+------------
+ reset_test
+(1 row)
+
+RESET TEMP;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+ relname 
+---------
+(0 rows)
+
+--
+-- Test RESET SESSION
+--
+-- do changes
+DECLARE foo CURSOR WITH HOLD FOR SELECT 1;
+PREPARE foo AS SELECT 1;
+LISTEN foo_event;
+SET vacuum_cost_delay = 13;
+CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS;
+CREATE ROLE temp_reset_user;
+SET SESSION AUTHORIZATION temp_reset_user;
+-- look changes
+SELECT relname FROM pg_listener;
+  relname  
+-----------
+ foo_event
+(1 row)
+
+SELECT name FROM pg_prepared_statements;
+ name 
+------
+ foo
+(1 row)
+
+SELECT name FROM pg_cursors;
+ name 
+------
+ foo
+(1 row)
+
+SHOW vacuum_cost_delay;
+ vacuum_cost_delay 
+-------------------
+ 13ms
+(1 row)
+
+SELECT relname from pg_class where relname = 'tmp_foo';
+ relname 
+---------
+ tmp_foo
+(1 row)
+
+SELECT current_user = 'temp_reset_user';
+ ?column? 
+----------
+ t
+(1 row)
+
+-- big RESET
+RESET SESSION;
+-- look again
+SELECT relname FROM pg_listener;
+ relname 
+---------
+(0 rows)
+
+SELECT name FROM pg_prepared_statements;
+ name 
+------
+(0 rows)
+
+SELECT name FROM pg_cursors;
+ name 
+------
+(0 rows)
+
+SHOW vacuum_cost_delay;
+ vacuum_cost_delay 
+-------------------
+ 0
+(1 row)
+
+SELECT relname from pg_class where relname = 'tmp_foo';
+ relname 
+---------
+(0 rows)
+
+SELECT current_user = 'temp_reset_user';
+ ?column? 
+----------
+ f
+(1 row)
+
+DROP ROLE temp_reset_user;
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index ab62ac2705..9b22aa4dc7 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -869,3 +869,33 @@ EXECUTE cprep;
  c2   | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t           | f         | f
 (1 row)
 
+-- test CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+ name 
+------
+ c2
+(1 row)
+
+CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+ name 
+------
+(0 rows)
+
+BEGIN;
+DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1;
+DECLARE foo2 CURSOR WITHOUT HOLD FOR SELECT 1;
+SELECT name FROM pg_cursors ORDER BY 1;
+ name 
+------
+ foo1
+ foo2
+(2 rows)
+
+CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+ name 
+------
+(0 rows)
+
+COMMIT;
diff --git a/src/test/regress/expected/prepare.out b/src/test/regress/expected/prepare.out
index 73d38c1ca1..695042f633 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -166,3 +166,11 @@ SELECT name, statement, parameter_types FROM pg_prepared_statements
       :     SELECT * FROM road WHERE thepath = $1;                         
 (5 rows)
 
+-- test DEALLOCATE ALL;
+DEALLOCATE ALL;
+SELECT name, statement, parameter_types FROM pg_prepared_statements
+    ORDER BY name;
+ name | statement | parameter_types 
+------+-----------+-----------------
+(0 rows)
+
diff --git a/src/test/regress/sql/guc.sql b/src/test/regress/sql/guc.sql
index 6d17a18241..274007a2c3 100644
--- a/src/test/regress/sql/guc.sql
+++ b/src/test/regress/sql/guc.sql
@@ -123,3 +123,42 @@ SELECT '2006-08-13 12:34:56'::timestamptz;
 RESET datestyle;
 SHOW datestyle;
 SELECT '2006-08-13 12:34:56'::timestamptz;
+
+--
+-- Test RESET TEMP
+--
+CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+RESET TEMP;
+SELECT relname FROM pg_class WHERE relname = 'reset_test';
+
+--
+-- Test RESET SESSION
+--
+
+-- do changes
+DECLARE foo CURSOR WITH HOLD FOR SELECT 1;
+PREPARE foo AS SELECT 1;
+LISTEN foo_event;
+SET vacuum_cost_delay = 13;
+CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS;
+CREATE ROLE temp_reset_user;
+SET SESSION AUTHORIZATION temp_reset_user;
+-- look changes
+SELECT relname FROM pg_listener;
+SELECT name FROM pg_prepared_statements;
+SELECT name FROM pg_cursors;
+SHOW vacuum_cost_delay;
+SELECT relname from pg_class where relname = 'tmp_foo';
+SELECT current_user = 'temp_reset_user';
+-- big RESET
+RESET SESSION;
+-- look again
+SELECT relname FROM pg_listener;
+SELECT name FROM pg_prepared_statements;
+SELECT name FROM pg_cursors;
+SHOW vacuum_cost_delay;
+SELECT relname from pg_class where relname = 'tmp_foo';
+SELECT current_user = 'temp_reset_user';
+DROP ROLE temp_reset_user;
+
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index 43cbf05a90..278ad64b38 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -303,3 +303,18 @@ ROLLBACK;
 PREPARE cprep AS
   SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 EXECUTE cprep;
+
+-- test CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+BEGIN;
+DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1;
+DECLARE foo2 CURSOR WITHOUT HOLD FOR SELECT 1;
+SELECT name FROM pg_cursors ORDER BY 1;
+CLOSE ALL;
+SELECT name FROM pg_cursors ORDER BY 1;
+COMMIT;
+
+
+
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index d42b7a0879..c7b5b13634 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -68,3 +68,9 @@ PREPARE q7(unknown) AS
 
 SELECT name, statement, parameter_types FROM pg_prepared_statements
     ORDER BY name;
+
+-- test DEALLOCATE ALL;
+DEALLOCATE ALL;
+SELECT name, statement, parameter_types FROM pg_prepared_statements
+    ORDER BY name;
+