Re: check database integrity

=?ISO-8859-1?Q?Torsten_F=F6rtsch?= <torsten.foertsch@gmx.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