PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15693
PG Version10.7
OSOS X, Ubuntu linux
Opened2019-03-13 19:11:32+00
Reported byilya b.
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15693
Logged by:          ilya b.
Email address:      (redacted)
PostgreSQL version: 10.7
Operating system:   OS X, Ubuntu linux
Description:        

Hi everyone!

I spent some time investigating an occurence of a supposed memory leak in
our app. The application is built using libpq and it's main purpose is to
listen on the network and put some of the received data to the database. 
To do that we are calling stored functions with string parameters. Most
requests are small (under 1K), but some are quite large, sometimes up to
hundred Ks. What I noticed is that some of those rare large requests made
libpq allocate and retain memory. Allocation happened at the time of stored
function call sending and it wasn't released until DB connection was done.
Please see Valgring report below:     

==2418== 524,288 bytes in 1 blocks are still reachable in loss record 633 of
635
==2418==    at 0x4C31D2F: realloc (in
/usr/lib/valgrind/vgpreload_memcheck-amd64-linux.so)
==2418==    by 0x9651E23: pqCheckOutBufferSpace (fe-misc.c:369)
==2418==    by 0x9651E74: pqPutMsgBytes (fe-misc.c:569)
==2418==    by 0x9651F95: pqPutnchar (fe-misc.c:250)
==2418==    by 0x964F556: PQsendQueryGuts (fe-exec.c:1559)
==2418==    by 0x964F92F: PQsendQueryPrepared (fe-exec.c:1401)
==2418==    by 0x96509A1: PQexecPrepared (fe-exec.c:1968)

The function responsible for memory allocation was indeed
pqCheckOutBufferSpace. Once it encountered a chunk that was larger than
available buffer size, it reallocated buffer to fit the chunk. The problem
here is that buffers stay in place until PGconn is released. Since our app
has a connection pool and we prefer to keep DB connections alive as long as
we can, idle memory usage becomes a problem. After some time every
connection aquires a good chunk of memory (somewhat proportional to the
largest call it made). 

My suggestion would be to add PGreclaimBuffers(PQconn *) function that would
realloc unused buffers back to their initial size. So in our case the usage
might be as follows: 

conn = PQconnectdb(...)
for(data) {
	res = PQexecPrepared(conn, data)
	// data processing
	PQclear(res)
	
	if (data.is_large) {
		PGreclaimBuffers(conn)
	}
}

My solution was to patch sources and rebuild libpq.so we use to link our app
with. But hopefully libpq API could be extended to include buffer reclaiming
specifically for the case of long lived connections and/or large requests.

Messages

DateAuthorSubject
2019-03-13 19:11:32+00PG Bug reporting formBUG #15693: Suggestion on libpq memory management