The following bug has been logged online:

Bug reference: 1258
Logged by: Vadim Passynkov

Email address: Vadim.Passynkov (AT) pathcom (DOT) com

PostgreSQL version: 7.4.5

Operating system: FreeBSD 4.10-STABLE

Description: backend memory leak after massive 'CREATE/DROP USER'

Details:


log generated by create_drop_user.sh
================================================== ====
Fri Sep 17 18:16:47 EDT 2004
VSZ RSS COMMAND
16612 3988 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 18:24:27 EDT 2004
VSZ RSS COMMAND
17572 3904 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 18:29:54 EDT 2004
VSZ RSS COMMAND
18596 4292 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 18:37:34 EDT 2004
VSZ RSS COMMAND
20652 4980 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 18:43:02 EDT 2004
VSZ RSS COMMAND
20652 5648 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 18:50:47 EDT 2004
VSZ RSS COMMAND
20652 6388 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 18:56:16 EDT 2004
VSZ RSS COMMAND
24748 7064 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 19:04:00 EDT 2004
VSZ RSS COMMAND
24748 7796 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 19:09:31 EDT 2004
VSZ RSS COMMAND
24748 8504 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 19:17:13 EDT 2004
VSZ RSS COMMAND
24748 9200 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 19:22:41 EDT 2004
VSZ RSS COMMAND
24748 9904 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 19:30:23 EDT 2004
VSZ RSS COMMAND
24748 10604 /usr/local/bin/postmaster (postgres)
Dropping 30000 users
Fri Sep 17 19:35:54 EDT 2004
VSZ RSS COMMAND
32952 11312 /usr/local/bin/postmaster (postgres)
Creating 30000 users
Fri Sep 17 19:43:39 EDT 2004
VSZ RSS COMMAND
32952 12016 /usr/local/bin/postmaster (postgres)
Dropping 30000 users

================================================== ====
gcc -I/usr/local/include -L/usr/local/lib -lpq create_drop_user.c -o
create_drop_user

/* create_drop_user.c */
#include <sys/types.h>
#include <fcntl.h>
#include <stdio.h>

#include <libpq-fe.h>

int main ( int argc, char **argv ) {
int i, fd;
PGconn *pgconn;
char buff[1024];

if ( ( pgconn = PQsetdbLogin ( NULL, NULL, NULL, NULL, "template1",
"pgsql", NULL ) ) != NULL ) {
printf ( "Current PQ fd=%d\n", PQsocket ( pgconn ) );

for ( i = 0; i < 30000; i++ ) {
sprintf ( buff, "%s USER user%d", argv[1], i );
PQexec ( pgconn, buff );
printf ( "Current user%d\n", i );
}
PQexec ( pgconn, "VACUUM FULL" );
PQfinish ( pgconn );
}

return 0;
}
================================================== ====
create_drop_user.sh

#!/bin/sh

while ( true ); do
date
ps -ax -o vsz,rss,command | grep '/usr/local/bin/postm\|COMMAND$' | grep
-v grep
echo "Creating 30000 users"
./create_drop_user create > /dev/null
date
ps -ax -o vsz,rss,command | grep '/usr/local/bin/postm\|COMMAND$' | grep
-v grep
echo "Dropping 30000 users"
./create_drop_user drop > /dev/null
done
================================================== ====


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

wild guess says that its not really neccessarily a memory leak unless all
the shared_buffers are getting consumed and its *still* growing...

CREATE USER adds a row to a system table, and DROP USER deletes that row,
the table space will grow on disk until you do a VACUUM to release the free
rows. Doing this without any VACUUM's will likely continue to allocate
shared_buffers until they are used up (thats what they are for, after all).

have you run this process for hours and hours and many millions of users,
such that the memory finally grows so big it crashes or errors out ?



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
Actually postmaster memory leak, but good catch anyway --- thanks!
The patch against 7.4 is

Index: hba.c
================================================== =================
RCS file: /cvsroot/pgsql-server/src/backend/libpq/hba.c,v
retrieving revision 1.116.2.2
diff -c -r1.116.2.2 hba.c
*** hba.c 25 May 2004 19:11:26 -0000 1.116.2.2
--- hba.c 18 Sep 2004 01:16:18 -0000
***************
*** 168,173 ****
--- 168,176 ----
* Tokenize file and handle file inclusion and comma lists. We have
* to break apart the commas to expand any file names then
* reconstruct with commas.
+ *
+ * The result is always a palloc'd string. If it's zero-length then
+ * we have reached EOL.
*/
static char *
next_token_expand(FILE *file)
***************
*** 333,338 ****
--- 336,343 ----
{
/* we are at real or logical EOL, so force a new line List */
next_line = NIL;
+ /* Don't forget to pfree the next_token_expand result */
+ pfree(buf);
}

/* Advance line number whenever we reach EOL */


regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly