The following bug has been logged online:
Bug reference: 1258
Logged by: Vadim Passynkov
Email address: [EMAIL PROTECTED]
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
