Re: check database integrity

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

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