Re: clearing the buffer cache

On Fri, 13 Feb 2009, Sam Mason wrote:

> I was hoping for a function I could call, or maybe some variable I write
> to, that would cause the contents to be invalidated.

For most people, just restarting the server is acceptable overhead, which
is why nobody has bothered to write such a thing. It wouldn't be too
difficult for someone familiar with the basic internals involved to hack
in such a function, getting it accepted into the standard distribution
would be quite another problem though.

As you noticed, just "select * from t" where t is a dummy table doesn't
help here anymore. What you could do is write a quick PL/pgSQL function
that simulates that in a way that isn't optimized away. Create a table
that's bigger than shared_buffers with a primary key, then iterate over
that table record at a time using that key until you've scanned records to
wipe out the cache. You can use contrib/pg_buffercache to confirm the
function is doing what you expect.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD