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"); }
;
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
