Обсуждение: Memory leak with CREATE TEMP TABLE ON COMMIT DROP?

# select version();
                                                              version
           

-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.4 on x86_64-apple-darwin13.2.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM
3.4svn),64-bit 
(1 row)

As best I can guess, Postgres has some kind of memory leak around (at least) temporary tables flagged to drop on
commit. It's fairly easy to reproduce: 

Terminal A
--------------
$ createdb leak
$ for i in $(seq 1 1000000) ; do echo "begin; create temp table foo() on commit drop; commit;"; done | psql leak >
/dev/null

Terminal B
--------------
$ while(true); do ps auwx | grep $PID_OF_POSTGRES_PROCESS_FROM_TERMINAL_A; sleep 1 ; done

And watch the RSS size continue to climb, fairly quickly.  This happens on both OS X and Linux (both x86_64).

We ran into this thanks to an update trigger that created a temporary table with on commit drop where we were literally
updatingmillions of rows as atomic transactions, across about 100 concurrent connections, firing the trigger for each
atomicupdate.  The server quickly ran out of memory. 

It took some time to find what appears to be the actual problem, but I think this is it.  We've since rewritten the
triggerto avoid using a temporary table (probably a good thing anyways) and all is well, but I was very shocked to see
Postgresbehaving badly here. 

Any thoughts?  And thanks for your time!

eric




PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS
The information contained in this communication is intended only for
the use of the addressee. Any other use is strictly prohibited.
Please notify the sender if you have received this message in error.
This communication is protected by applicable legal privileges and is
company confidential.

Eric Ridge <e_ridge@tcdi.com> writes:
> As best I can guess, Postgres has some kind of memory leak around (at least) temporary tables flagged to drop on
commit. It's fairly easy to reproduce: 

I don't see any memory leak with this example.

What I do see is the process's use of shared memory grows slowly until it
reaches 128MB (or whatever you've set shared_buffers to), and then stops.
This is normal behavior and has nothing to do with any memory leak: what
causes that is that top and related tools typically don't count shared
memory pages in a process's memory footprint until the process has
actually touched those pages.  So as the process gradually touches more
and more of the shared buffers, its memory usage appears to climb, but
that's completely an illusion of the viewing tool.  The important point
is the process's private memory usage, and that's not growing at all
AFAICS --- it's steady at a small number of megabytes.

Since you say you had an out-of-memory failure, you may well have had
some kind of leak in your original situation, but this test case isn't
reproducing it.

            regards, tom lane