Thread: Information about user`s procces:username and his sql-request?
Enybody knows as to receive the information about the users transmitting inquiries as: - username, - string of sql-request, transmitted by him. Thank you for your answer.
Re: [HACKERS] Information about user`s procces:username and his sql-request?
From
Bruce Momjian
Date:
> > Enybody knows as to receive the information > about the users transmitting inquiries as: > - username, > - string of sql-request, transmitted by him. > > Thank you for your answer. > > > > Not sure if that shows the username too. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Hi all.
As nobody answered my previous post, I decided to correct PostgreSQL
buffer leaks caused by large objects methods.
Theses buffer leaks are caused by indexes that are kept open between
calls. Outside a transaction, the backend detects them as buffer leaks; it
sends a NOTICE, and frees them. This sometimes cause a segmentation fault
(at least on Linux). These indexes are initialized on the first
lo_read/lo_write/lo_tell call, and (normally) closed on a lo_close call.
Thus the buffer leaks appear when lo direct access functions are used, and
not with lo_import/lo_export functions (libpq version calls lo_close
before ending the command, and the backend version uses another path).
The included patches (against recent snapshot, and against 6.3.2) cause
indexes to be closed on transaction end (that is on explicit 'END'
statment, or on command termination outside trasaction blocks), thus
preventing the buffer leaks while increasing performance inside
transactions. Some (all?) 'classic' memory leaks are also removed.
I hope it will be ok.
---
Pascal ANDRE, graduated from Ecole Centrale Paris
andre@via.ecp.fr
"Use the source, Luke. Be one with the Code." -- Linus Torvalds
diff -Nru pgsql/src/backend/access/transam/xact.c pgsql-mod/src/backend/access/transam/xact.c
--- pgsql/src/backend/access/transam/xact.c Sat Jun 20 09:00:27 1998
+++ pgsql-mod/src/backend/access/transam/xact.c Fri Jul 17 11:22:29 1998
@@ -137,6 +137,11 @@
*-------------------------------------------------------------------------
*/
+/*
+ * Large object clean up added in CommitTransaction() to prevent buffer leaks.
+ * [PA, 7/17/98]
+ * [PA] is Pascal André <andre@via.ecp.fr>
+ */
#include <postgres.h>
#include <access/xact.h>
@@ -151,6 +156,9 @@
#include <commands/async.h>
#include <commands/sequence.h>
+/* included for _lo_commit [PA, 7/17/98] */
+#include <libpq/be-fsstubs.h>
+
static void AbortTransaction(void);
static void AtAbort_Cache(void);
static void AtAbort_Locks(void);
@@ -889,6 +897,10 @@
* do commit processing
* ----------------
*/
+
+ /* handle commit for large objects [ PA, 7/17/98 ] */
+ _lo_commit();
+
CloseSequences();
DestroyTempRels();
AtEOXact_portals();
diff -Nru pgsql/src/backend/libpq/be-fsstubs.c pgsql-mod/src/backend/libpq/be-fsstubs.c
--- pgsql/src/backend/libpq/be-fsstubs.c Sat Jun 20 09:00:32 1998
+++ pgsql-mod/src/backend/libpq/be-fsstubs.c Fri Jul 17 11:11:14 1998
@@ -41,6 +41,8 @@
#include <storage/large_object.h>
#include <libpq/be-fsstubs.h>
+/* [PA] is Pascal André <andre@via.ecp.fr> */
+
/*#define FSDB 1*/
#define MAX_LOBJ_FDS 256
@@ -52,7 +54,6 @@
static int newLOfd(LargeObjectDesc *lobjCookie);
static void deleteLOfd(int fd);
-
/*****************************************************************************
* File Interfaces for Large Objects
*****************************************************************************/
@@ -365,6 +366,26 @@
close(fd);
return 1;
+}
+
+/*
+ * lo_commit -
+ * prepares large objects for transaction commit [PA, 7/17/98]
+ */
+void
+_lo_commit(void)
+{
+ int i;
+ MemoryContext currentContext;
+
+ currentContext = MemoryContextSwitchTo((MemoryContext) fscxt);
+
+ for (i = 0; i < MAX_LOBJ_FDS; i++) {
+ if (cookies[i] != NULL) inv_cleanindex(cookies[i]);
+ }
+
+ MemoryContextSwitchTo(currentContext);
+
}
diff -Nru pgsql/src/backend/storage/large_object/inv_api.c pgsql-mod/src/backend/storage/large_object/inv_api.c
--- pgsql/src/backend/storage/large_object/inv_api.c Sat Jun 20 09:00:43 1998
+++ pgsql-mod/src/backend/storage/large_object/inv_api.c Fri Jul 17 11:30:27 1998
@@ -60,6 +60,19 @@
* for data.
*/
+/*
+ * In order to prevent buffer leak on transaction commit, large object
+ * scan index handling has been modified. Indexes are persistant inside
+ * a transaction but may be closed between two calls to this API (when
+ * transaction is committed while object is opened, or when no
+ * transaction is active). Scan indexes are thus now reinitialized using
+ * the object current offset. [PA]
+ *
+ * Some cleanup has been also done for non freed memory.
+ *
+ * For subsequent notes, [PA] is Pascal André <andre@via.ecp.fr>
+ */
+
#define IFREESPC(p) (PageGetFreeSpace(p) - sizeof(HeapTupleData) - sizeof(struct varlena) - sizeof(int32))
#define IMAXBLK 8092
#define IMINBLK 512
@@ -261,8 +274,11 @@
{
Assert(PointerIsValid(obj_desc));
- if (obj_desc->iscan != (IndexScanDesc) NULL)
+ if (obj_desc->iscan != (IndexScanDesc) NULL) {
index_endscan(obj_desc->iscan);
+ pfree(obj_desc->iscan);
+ obj_desc->iscan = NULL;
+ }
heap_close(obj_desc->heap_r);
index_close(obj_desc->index_r);
@@ -547,6 +563,28 @@
}
/*
+ * inv_cleanindex --
+ * Clean opened indexes for large objects, and clears current result.
+ * This is necessary on transaction commit in order to prevent buffer
+ * leak.
+ * This function must be called for each opened large object.
+ * [ PA, 7/17/98 ]
+ */
+void
+inv_cleanindex(LargeObjectDesc *obj_desc)
+{
+ Assert(PointerIsValid(obj_desc));
+
+ if (obj_desc->iscan == (IndexScanDesc) NULL) return;
+
+ index_endscan(obj_desc->iscan);
+ pfree(obj_desc->iscan);
+ obj_desc->iscan = (IndexScanDesc) NULL;
+
+ ItemPointerSetInvalid(&(obj_desc->htid));
+}
+
+/*
* inv_fetchtup -- Fetch an inversion file system block.
*
* This routine finds the file system block containing the offset
@@ -591,8 +629,13 @@
{
ScanKeyData skey;
+ /*
+ * As scan index may be prematurely closed (on commit),
+ * we must use object current offset (was 0) to
+ * reinitialize the entry [ PA ].
+ */
ScanKeyEntryInitialize(&skey, 0x0, 1, F_INT4GE,
- Int32GetDatum(0));
+ Int32GetDatum(obj_desc->offset));
obj_desc->iscan =
index_beginscan(obj_desc->index_r,
(bool) 0, (uint16) 1,
@@ -622,7 +665,7 @@
/* remember this tid -- we may need it for later reads/writes */
ItemPointerCopy(&(res->heap_iptr), &(obj_desc->htid));
-
+ pfree(res);
}
else
{
@@ -1179,6 +1222,7 @@
if (res == (RetrieveIndexResult) NULL)
{
index_endscan(iscan);
+ pfree(iscan);
return (0);
}
@@ -1192,11 +1236,13 @@
ReleaseBuffer(buf);
htup = heap_fetch(hreln, false, &(res->heap_iptr), &buf);
+ pfree(res);
} while (!HeapTupleIsValid(htup));
/* don't need the index scan anymore */
index_endscan(iscan);
+ pfree(iscan);
/* get olastbyte attribute */
d = heap_getattr(htup, 1, hdesc, &isNull);
diff -Nru pgsql/src/include/libpq/be-fsstubs.h pgsql-mod/src/include/libpq/be-fsstubs.h
--- pgsql/src/include/libpq/be-fsstubs.h Mon Sep 8 23:52:33 1997
+++ pgsql-mod/src/include/libpq/be-fsstubs.h Fri Jul 17 11:11:43 1998
@@ -37,4 +37,9 @@
extern struct varlena *loread(int fd, int len);
extern int lowrite(int fd, struct varlena * wbuf);
+/*
+ * Added for buffer leak prevention [ Pascal André <andre@via.ecp.fr> ]
+ */
+extern void _lo_commit(void);
+
#endif /* BE_FSSTUBS_H */
diff -Nru pgsql/src/include/storage/large_object.h pgsql-mod/src/include/storage/large_object.h
--- pgsql/src/include/storage/large_object.h Mon Sep 8 23:54:29 1997
+++ pgsql-mod/src/include/storage/large_object.h Fri Jul 17 11:13:21 1998
@@ -54,4 +54,7 @@
extern int inv_read(LargeObjectDesc *obj_desc, char *buf, int nbytes);
extern int inv_write(LargeObjectDesc *obj_desc, char *buf, int nbytes);
+/* added for buffer leak prevention [ PA ] */
+extern void inv_cleanindex(LargeObjectDesc *obj_desc);
+
#endif /* LARGE_OBJECT_H */
diff -Nru postgresql-6.3.2/src/backend/access/transam/xact.c postgresql-6.3.2-mod/src/backend/access/transam/xact.c
--- postgresql-6.3.2/src/backend/access/transam/xact.c Wed Jan 7 22:02:24 1998
+++ postgresql-6.3.2-mod/src/backend/access/transam/xact.c Sat Jul 18 11:21:38 1998
@@ -137,6 +137,11 @@
*-------------------------------------------------------------------------
*/
+/*
+ * Large object clean up added in CommitTransaction() to prevent buffer leaks.
+ * [PA, 7/17/98]
+ * [PA] is Pascal André <andre@via.ecp.fr>
+ */
#include <postgres.h>
#include <access/xact.h>
@@ -151,6 +156,9 @@
#include <commands/async.h>
#include <commands/sequence.h>
+/* included for _lo_commit [PA, 7/17/98] */
+#include <libpq/be-fsstubs.h>
+
static void AbortTransaction(void);
static void AtAbort_Cache(void);
static void AtAbort_Locks(void);
@@ -889,6 +897,10 @@
* do commit processing
* ----------------
*/
+
+ /* handle commit for large objects [ PA, 7/17/98 ] */
+ _lo_commit();
+
CloseSequences();
DestroyTempRels();
AtEOXact_portals();
diff -Nru postgresql-6.3.2/src/backend/libpq/be-fsstubs.c postgresql-6.3.2-mod/src/backend/libpq/be-fsstubs.c
--- postgresql-6.3.2/src/backend/libpq/be-fsstubs.c Wed Jan 7 22:03:12 1998
+++ postgresql-6.3.2-mod/src/backend/libpq/be-fsstubs.c Sat Jul 18 11:21:38 1998
@@ -41,6 +41,8 @@
#include <storage/large_object.h>
#include <libpq/be-fsstubs.h>
+/* [PA] is Pascal André <andre@via.ecp.fr> */
+
/*#define FSDB 1*/
#define MAX_LOBJ_FDS 256
@@ -52,7 +54,6 @@
static int newLOfd(LargeObjectDesc *lobjCookie);
static void deleteLOfd(int fd);
-
/*****************************************************************************
* File Interfaces for Large Objects
*****************************************************************************/
@@ -369,6 +370,26 @@
close(fd);
return 1;
+}
+
+/*
+ * lo_commit -
+ * prepares large objects for transaction commit [PA, 7/17/98]
+ */
+void
+_lo_commit(void)
+{
+ int i;
+ MemoryContext currentContext;
+
+ currentContext = MemoryContextSwitchTo((MemoryContext) fscxt);
+
+ for (i = 0; i < MAX_LOBJ_FDS; i++) {
+ if (cookies[i] != NULL) inv_cleanindex(cookies[i]);
+ }
+
+ MemoryContextSwitchTo(currentContext);
+
}
diff -Nru postgresql-6.3.2/src/backend/storage/large_object/inv_api.c
postgresql-6.3.2-mod/src/backend/storage/large_object/inv_api.c
--- postgresql-6.3.2/src/backend/storage/large_object/inv_api.c Tue Feb 10 05:02:05 1998
+++ postgresql-6.3.2-mod/src/backend/storage/large_object/inv_api.c Sat Jul 18 11:25:38 1998
@@ -60,6 +60,19 @@
* for data.
*/
+/*
+ * In order to prevent buffer leak on transaction commit, large object
+ * scan index handling has been modified. Indexes are persistant inside
+ * a transaction but may be closed between two calls to this API (when
+ * transaction is committed while object is opened, or when no
+ * transaction is active). Scan indexes are thus now reinitialized using
+ * the object current offset. [PA]
+ *
+ * Some cleanup has been also done for non freed memory.
+ *
+ * For subsequent notes, [PA] is Pascal André <andre@via.ecp.fr>
+ */
+
#define IFREESPC(p) (PageGetFreeSpace(p) - sizeof(HeapTupleData) - sizeof(struct varlena) - sizeof(int32))
#define IMAXBLK 8092
#define IMINBLK 512
@@ -261,8 +274,11 @@
{
Assert(PointerIsValid(obj_desc));
- if (obj_desc->iscan != (IndexScanDesc) NULL)
+ if (obj_desc->iscan != (IndexScanDesc) NULL) {
index_endscan(obj_desc->iscan);
+ pfree(obj_desc->iscan);
+ obj_desc->iscan = NULL;
+ }
heap_close(obj_desc->heap_r);
index_close(obj_desc->index_r);
@@ -549,6 +565,28 @@
}
/*
+ * inv_cleanindex --
+ * Clean opened indexes for large objects, and clears current result.
+ * This is necessary on transaction commit in order to prevent buffer
+ * leak.
+ * This function must be called for each opened large object.
+ * [ PA, 7/17/98 ]
+ */
+void
+inv_cleanindex(LargeObjectDesc *obj_desc)
+{
+ Assert(PointerIsValid(obj_desc));
+
+ if (obj_desc->iscan == (IndexScanDesc) NULL) return;
+
+ index_endscan(obj_desc->iscan);
+ pfree(obj_desc->iscan);
+ obj_desc->iscan = (IndexScanDesc) NULL;
+
+ ItemPointerSetInvalid(&(obj_desc->htid));
+}
+
+/*
* inv_fetchtup -- Fetch an inversion file system block.
*
* This routine finds the file system block containing the offset
@@ -593,8 +631,13 @@
{
ScanKeyData skey;
- ScanKeyEntryInitialize(&skey, 0x0, 1, INT4GE_PROC_OID,
- Int32GetDatum(0));
+ /*
+ * As scan index may be prematurely closed (on commit),
+ * we must use object current offset (was 0) to
+ * reinitialize the entry [ PA ].
+ */
+ ScanKeyEntryInitialize(&skey, 0x0, 1, INT4GE_PROC_OID,
+ Int32GetDatum(obj_desc->offset));
obj_desc->iscan =
index_beginscan(obj_desc->index_r,
(bool) 0, (uint16) 1,
@@ -624,7 +667,7 @@
/* remember this tid -- we may need it for later reads/writes */
ItemPointerCopy(&(res->heap_iptr), &(obj_desc->htid));
-
+ pfree(res);
}
else
{
@@ -1183,6 +1226,7 @@
if (res == (RetrieveIndexResult) NULL)
{
index_endscan(iscan);
+ pfree(iscan);
return (0);
}
@@ -1196,11 +1240,13 @@
ReleaseBuffer(buf);
htup = heap_fetch(hreln, false, &(res->heap_iptr), &buf);
+ pfree(res);
} while (!HeapTupleIsValid(htup));
/* don't need the index scan anymore */
index_endscan(iscan);
+ pfree(iscan);
/* get olastbyte attribute */
d = heap_getattr(htup, 1, hdesc, &isNull);
diff -Nru postgresql-6.3.2/src/include/libpq/be-fsstubs.h postgresql-6.3.2-mod/src/include/libpq/be-fsstubs.h
--- postgresql-6.3.2/src/include/libpq/be-fsstubs.h Mon Sep 8 23:52:33 1997
+++ postgresql-6.3.2-mod/src/include/libpq/be-fsstubs.h Sat Jul 18 11:21:38 1998
@@ -37,4 +37,9 @@
extern struct varlena *loread(int fd, int len);
extern int lowrite(int fd, struct varlena * wbuf);
+/*
+ * Added for buffer leak prevention [ Pascal André <andre@via.ecp.fr> ]
+ */
+extern void _lo_commit(void);
+
#endif /* BE_FSSTUBS_H */
diff -Nru postgresql-6.3.2/src/include/storage/large_object.h postgresql-6.3.2-mod/src/include/storage/large_object.h
--- postgresql-6.3.2/src/include/storage/large_object.h Mon Sep 8 23:54:29 1997
+++ postgresql-6.3.2-mod/src/include/storage/large_object.h Sat Jul 18 11:21:38 1998
@@ -54,4 +54,7 @@
extern int inv_read(LargeObjectDesc *obj_desc, char *buf, int nbytes);
extern int inv_write(LargeObjectDesc *obj_desc, char *buf, int nbytes);
+/* added for buffer leak prevention [ PA ] */
+extern void inv_cleanindex(LargeObjectDesc *obj_desc);
+
#endif /* LARGE_OBJECT_H */
Patch applied. > > Hi all. > > As nobody answered my previous post, I decided to correct PostgreSQL > buffer leaks caused by large objects methods. > > Theses buffer leaks are caused by indexes that are kept open between > calls. Outside a transaction, the backend detects them as buffer leaks; it > sends a NOTICE, and frees them. This sometimes cause a segmentation fault > (at least on Linux). These indexes are initialized on the first > lo_read/lo_write/lo_tell call, and (normally) closed on a lo_close call. > Thus the buffer leaks appear when lo direct access functions are used, and > not with lo_import/lo_export functions (libpq version calls lo_close > before ending the command, and the backend version uses another path). > > The included patches (against recent snapshot, and against 6.3.2) cause > indexes to be closed on transaction end (that is on explicit 'END' > statment, or on command termination outside trasaction blocks), thus > preventing the buffer leaks while increasing performance inside > transactions. Some (all?) 'classic' memory leaks are also removed. > > I hope it will be ok. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)