[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.
---
[PATCHES] Win32 version numbers not correct (again, but this one is easy)
Seems to be a neverending story :-) Version numbers don't update properly on postgres.exe when doing make clean/make. It works for all other exes and dlls. I beleive this is just a case of make clean not properly removing win32ver.o. See attached patch. //Magnus backend_clean.patch backend_clean.patch Description: backend_clean.patch ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Win32 version numbers not correct (again, but this one is easy)
Magnus Hagander [EMAIL PROTECTED] writes: Version numbers don't update properly on postgres.exe when doing make clean/make. It works for all other exes and dlls. I beleive this is just a case of make clean not properly removing win32ver.o. See attached patch. Hmm, is that the only place we missed it? Might be worth diffing a make distclean'd tree against a virgin one. regards, tom lane ---(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] [HACKERS] Bgwriter behavior
On Mon, 2004-12-27 at 22:21, Bruce Momjian wrote:
Should we consider at least adjusting the meaning of bgwriter_percent?
Yes. As things stand, this is the only change that seems safe.
Here's a very short patch that implements this change within BufferSync
in bufmgr.c
- No algorithm changes
- No error message changes
- Only change is the call to StrategyDirtyBufferList is made using the
maximum number of buffers that will be cleaned, rather than uselessly
trawling through all of shared_buffers
This changes the meaning of bgwriter_percent from percent of dirty
buffers to percent of shared_buffers. The default settings of 1% of
1000 buffers gives up to 10 dirty block writes every 250ms
Benefit: allows performance tuning by increases options for setting
bgwriter_delay which would otherwise have an ineffectually high minimum
setting
Risk: low
1-line doc patch to follow, if this is approved.
--
Best Regards, Simon Riggs
Index: src/backend/storage/buffer/bufmgr.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/storage/buffer/bufmgr.c,v
retrieving revision 1.182
diff -d -c -r1.182 bufmgr.c
*** src/backend/storage/buffer/bufmgr.c 24 Nov 2004 02:56:17 - 1.182
--- src/backend/storage/buffer/bufmgr.c 30 Dec 2004 23:52:24 -
***
*** 681,686
--- 681,687
{
BufferDesc **dirty_buffers;
BufferTag *buftags;
+ int dirty_buffers_maxlen = 1;
int num_buffer_dirty;
int i;
***
*** 688,717
if (percent == 0 || maxpages == 0)
return 0;
/*
! * Get a list of all currently dirty buffers and how many there are.
* We do not flush buffers that get dirtied after we started. They
! * have to wait until the next checkpoint.
*/
! dirty_buffers = (BufferDesc **) palloc(NBuffers * sizeof(BufferDesc *));
! buftags = (BufferTag *) palloc(NBuffers * sizeof(BufferTag));
LWLockAcquire(BufMgrLock, LW_EXCLUSIVE);
- num_buffer_dirty = StrategyDirtyBufferList(dirty_buffers, buftags,
- NBuffers);
! /*
! * If called by the background writer, we are usually asked to only
! * write out some portion of dirty buffers now, to prevent the IO
! * storm at checkpoint time.
! */
! if (percent 0)
! {
! Assert(percent = 100);
! num_buffer_dirty = (num_buffer_dirty * percent + 99) / 100;
! }
! if (maxpages 0 num_buffer_dirty maxpages)
! num_buffer_dirty = maxpages;
/* Make sure we can handle the pin inside the loop */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
--- 689,719
if (percent == 0 || maxpages == 0)
return 0;
+ /* Set number of buffers we will clean at LRUs of buffer lists */
+ if (percent 0 ) {
+ Assert(percent = 100);
+ dirty_buffers_maxlen = (NBuffers * percent + 99) / 100;
+ }
+ if (maxpages 0 dirty_buffers_maxlen maxpages)
+ dirty_buffers_maxlen = maxpages;
+
+ /* if checkpoint time */
+ if (percent == -1 maxpages == -1)
+ dirty_buffers_maxlen = NBuffers;
+
/*
! * Get a list of dirty buffers to clean and how many there are.
* We do not flush buffers that get dirtied after we started. They
! * have to wait until the next call of this function
*/
! dirty_buffers =
! (BufferDesc **) palloc(dirty_buffers_maxlen * sizeof(BufferDesc *));
! buftags = (BufferTag *) palloc(dirty_buffers_maxlen * sizeof(BufferTag));
LWLockAcquire(BufMgrLock, LW_EXCLUSIVE);
! num_buffer_dirty = StrategyDirtyBufferList(dirty_buffers, buftags,
! dirty_buffers_maxlen);
/* Make sure we can handle the pin inside the loop */
ResourceOwnerEnlargeBuffers(CurrentResourceOwner);
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] [HACKERS] Bgwriter behavior
Simon Riggs wrote: On Mon, 2004-12-27 at 22:21, Bruce Momjian wrote: Should we consider at least adjusting the meaning of bgwriter_percent? Yes. As things stand, this is the only change that seems safe. Here's a very short patch that implements this change within BufferSync in bufmgr.c - No algorithm changes - No error message changes - Only change is the call to StrategyDirtyBufferList is made using the maximum number of buffers that will be cleaned, rather than uselessly trawling through all of shared_buffers This changes the meaning of bgwriter_percent from percent of dirty buffers to percent of shared_buffers. The default settings of 1% of 1000 buffers gives up to 10 dirty block writes every 250ms Benefit: allows performance tuning by increases options for setting bgwriter_delay which would otherwise have an ineffectually high minimum setting Risk: low 1-line doc patch to follow, if this is approved. I am not objecting to the patch, but what value is there in having both bgwriter_percent and bgwriter_maxpages? Seems both are redundant and that one would be enough. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Win32 version numbers not correct (again, but this one is easy)
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: Hmm, is that the only place we missed it? Might be worth diffing a make distclean'd tree against a virgin one. I was under the impression we only wanted a new version number with a configure run, however, I can see that make clean perhaps should do it too. No, it's not the source code file he's complaining about. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PATCHES] reqd patch
Dear Sir, I am working as Teacher in college. I am teaching PostgreSQL to student. I am facing problem in running CURSOR. version of postgresql is 7.3.2 Cursor creation and fetching works fine. but , 1) refcursor datatype is not recognised by postgres 2) fetch curs1 into curs2 , is not working. pls can u help me . can u send me one example of cursur using above. regards ramesh phule. = Regards Ramesh __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] reqd patch
On Thu, Dec 30, 2004 at 09:26:48PM -0800, ramesh phule wrote: Subject: [PATCHES] reqd patch To: pgsql-patches@postgresql.org I'm not sure what reqd patch has to do with your question -- a subject with the word cursor would be more appropriate since that's the topic. Also, pgsql-patches is supposed to be for Patches for new features and bug fixes, so I'm copying pgsql-general and setting the Reply-To header to that list. Cursor creation and fetching works fine. but , 1) refcursor datatype is not recognised by postgres What do you mean not recognised? What exactly are you doing, what are you expecting to happen, and what actually does happen? It would be helpful if we could see the exact statements you're executing and the exact error message you're getting. 2) fetch curs1 into curs2 , is not working. Please be more specific than not working -- what are you trying to do and what happens? Is curs2 a cursor? I wouldn't expect that to work since the PL/pgSQL documentation says that the FETCH INTO target may be a row variable, a record variable, or a comma-separated list of simple variables. pls can u help me . can u send me one example of cursur The PostgreSQL documentation has examples in the Cursors section of the PL/pgSQL chapter. If those aren't helpful, then please describe what you're trying to do so we can provide an example that's more relevant to the problem. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
