Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Implementing RESET CONNECTION ...
Bruce Momjian wrote: Hans-Jrgen Schnig 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Implementing RESET CONNECTION ...
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Implementing RESET CONNECTION ...
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Implementing RESET CONNECTION ...
(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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Implementing RESET CONNECTION ...
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-Jrgen Schnig 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Implementing RESET CONNECTION ...
On Mon, 3 Jan 2005, [UTF-8] Hans-Jrgen Schnig 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Implementing RESET CONNECTION ...
Kris Jurka [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Implementing RESET CONNECTION ...
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 [EMAIL PROTECTED] To: Hans-Jürgen Schönig [EMAIL PROTECTED] CC: [EMAIL PROTECTED], pgman@candle.pha.pa.us, [EMAIL PROTECTED], [EMAIL PROTECTED] References: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Implementing RESET CONNECTION ...
Tom Lane wrote: Kris Jurka [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Implementing RESET CONNECTION ...
Oliver Jowett [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Implementing RESET CONNECTION ...
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] 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
refnameRESET/refname
! refpurposerestore the value of a run-time parameter to the default value/refpurpose
/refnamediv
indexterm zone=sql-reset
--- 11,17
refnamediv
refnameRESET/refname
! refpurposereset 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=PARAMETERconnection/replaceable
RESET replaceable class=PARAMETERname/replaceable
RESET ALL
/synopsis
***
*** 52,57
--- 53,66
See the commandSET/ reference page for details on the
transaction behavior of commandRESET/.
/para
+
+ para
+commandRESET CONNECTION/command can be used to reset the entire
+backend. This includes temporary tables, open transactions, prepared
+statements, literalWITH HOLD/literal cursors runtime parameters
+as well as asynchronous backend settings.
+ /para
+
/refsect1
refsect1
***
*** 76,82
--- 85,103
/para
/listitem
/varlistentry
+
+varlistentry
+ termliteralCONNECTION/literal/term
+ listitem
+ para
+ Reset the entire backend including temporary tables, open transactions,
+ prepared statements, literalWITH 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.
---
