Re: check database integrity - Mailing list pgsql-general

On 20/07/14 17:35, Tom Lane wrote:
> =?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.

Thanks, Tom. At least the patch description helped. I moved the
page_header() call to output column list and now it works perfectly.
I'll try the patch next weekend.

>> 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.

You are right here. When I wrote the email I restored the behaviour from
my faulty memory. Today I tried it again and the memory is indeed freed
at the end of the query.

Another question, just out of curiosity, for vm and main forks I use
pg_relation_size to figure out the highest page number. That does not
work for fsm. I have at least one fsm file that it 24 kb. Fetching page
0 works, page 1 and above gives an error:

db=# select page_header(get_raw_page(2836::oid::regclass::text, 'fsm', 0));
                 page_header
----------------------------------------------
 (114/23485F78,19084,0,24,8192,8192,8192,4,0)
(1 row)

db=# select page_header(get_raw_page(2836::oid::regclass::text, 'fsm',
1));
ERROR:  block number 1 is out of range for relation "pg_toast_1255"
db=# select pg_relation_size(2836::oid::regclass, 'fsm');

 pg_relation_size
------------------
            24576


For other relations it works:

db=# select page_header(get_raw_page(60966::oid::regclass::text, 'fsm',
i)) from generate_series(0,2) i;
                  page_header
-----------------------------------------------
 (11F/76884610,-4342,0,24,8192,8192,8192,4,0)
 (11F/768825C0,22465,0,24,8192,8192,8192,4,0)
 (11F/83E9EC38,-29015,0,24,8192,8192,8192,4,0)
(3 rows)

db=# select pg_relation_size(60966::oid::regclass, 'fsm');
 pg_relation_size
------------------
            24576


Is there a way to figure out the highest page number for fsm forks? Is
there perhaps a common way that works for all forks?


Thanks,
Torsten