Re: check database integrity

=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten(dot)foertsch(at)gmx(dot)net> writes:
> Then I remembered about the pageinspect extension. The following select
> is a bit too verbose but it seems to do the job for everything except
> fsm files.

> SELECT c.oid::regclass::text as rel,
> f.fork,
> ser.i as blocknr,
> pg.*
> FROM pg_class c
> CROSS JOIN (values ('main'::text), ('vm'::text)) f(fork)
> CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
> CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
> CROSS JOIN page_header(get_raw_page(c.oid::regclass::text,
> f.fork,
> ser.i)) pg
> WHERE sz.sz>0

> The problem with the select above is that either page_header() or
> get_raw_page() seems to allocate the memory for the page without freeing
> it again.

Probably commit 45b0f3572 will help you with that.

> I ran this query in a separate transaction. The memory was freed only
> when the backend process exited.

AFAIK such memory is released at end of query, even without the patch.
Are you sure you aren't looking at shared-buffer usage? Or maybe you're
on a platform where libc doesn't release freed memory back to the OS.

regards, tom lane