Обсуждение: BUG #1258: backend memory leak after massive 'CREATE/DROP USER'

The following bug has been logged online:

Bug reference:      1258
Logged by:          Vadim Passynkov

Email address:      Vadim.Passynkov@pathcom.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
======================================================

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

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 ?

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> Description:        backend memory leak after massive 'CREATE/DROP USER'

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