To: Nick <t32@2thebatcave.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>, pgsql-admin@postgresql.org,
	Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: autovacuum launcher process eating up 17G+ of ram? 
In-reply-to: <AANLkTi=Mh=pv7Ai+L17cbjgsExMbncHG5fMTvj5_wCAe@mail.gmail.com> 
References: <AANLkTimGvWugpjwN1Ki=qmd56nsc4gXH6yKT5765nFPK@mail.gmail.com>
	<4CC071670200002500036C8A@gw.wicourts.gov>
	<AANLkTimKs23hytr6oj1Kco53rbaPF2mKTZZYofr0Cr0+@mail.gmail.com>
	<4CC18EF60200002500036CF3@gw.wicourts.gov>
	<AANLkTi=Mh=pv7Ai+L17cbjgsExMbncHG5fMTvj5_wCAe@mail.gmail.com>
Comments: In-reply-to Nick <t32@2thebatcave.com>
	message dated "Mon, 25 Oct 2010 10:01:28 -0700"
Date: Mon, 25 Oct 2010 15:57:10 -0400
Message-ID: <4232.1288036630@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Nick <t32@2thebatcave.com> writes:
> It seems like the problem is a function of how many databases there are.

Yeah, I can reproduce this in HEAD.  Create a hundred or so databases,
and make sure there's a stats table entry for each, eg

create database d0;
\c d0
create table t1 as select generate_series(1,100000) x;
create database d1;
\c d1
create table t1 as select generate_series(1,100000) x;
create database d2;
\c d2
create table t1 as select generate_series(1,100000) x;
...

Set autovacuum_naptime to 1sec, and watch it bloat.  (It bloats faster
once it's run through the databases once and isn't mostly waiting for
children.)

It looks to me like the problem is that get_database_list()
intentionally pushes its result into a long-lived context, and then the
result is never cleaned up.  It doesn't help any that it does a lot of
leaky things like heap_beginscan with CurrentMemoryContext totally
unnecessarily pointing at the long-lived context.

I'm not sure what the intent was here, but it's pretty broken as-is.
Alvaro, what did you have in mind?  Should this be using a less
long-lived context, or do we need code to free an avw_dbase list?

			regards, tom lane