On Thu, Feb 6, 2014 at 11:48 PM, Andres Freund <and...@2ndquadrant.com> wrote:
>
> That's not necessarily true. If e.g. the buffer mapping would change
> racily, the result write from the bgwriter could very well end up
> increasing the file size, leaving a hole inbetween its write and the
> original size.
a) the segment isn't sparse and b) there were whole segments full of
nuls between the end of the tables and the final blocks.
So the file was definitely extended by Postgres, not the OS and the
bgwriter passes EXTENSION_FAIL which means it wouldn't create those
intervening segments.
> Are there any other relations that are as big as the corrupted relations
> got extended to?
I was wondering the same thing. But no, the extended file is 39GB
larger than the next largest relation.
Also, the final block in the first relation is clearly a version of
the same block from the correct location. Look at the ctids and the
xids on the rows for lp 3 in the attached file for example. That
second copy is from the location in the xlog record.
--
greg
=# select * from (select relfilenode,(pg_relation_size(oid)/8192)::integer-1 as
blockno,((pg_relation_size(oid)/8192)::integer-1)/1024/128 as
segnum,((pg_relation_size(oid)/8192)::integer-1)%(1024*1024*128) as offset,
(page_header(get_raw_page(oid::regclass::text,'main',(pg_relation_size(oid)/8192)::integer-1))).*
from pg_class where pg_relation_size(oid) > 4*8192 and
(page_header(get_raw_page(oid::regclass::text,'main',(pg_relation_size(oid)/8192)::integer-2))).upper=0
order by lsn) as x ;
relfilenode | blockno | segnum | offset | lsn | tli | flags | lower
| upper | special | pagesize | version | prune_xid
-------------+----------+--------+----------+------------+-----+-------+-------+-------+---------+----------+---------+------------
473158 | 18090059 | 138 | 18090059 | EA1/625E28 | 6 | 0 | 144
| 896 | 8192 | 8192 | 4 | 1401029863
1366221 | 2208159 | 16 | 2208159 | EA1/62DDF0 | 6 | 0 | 1180
| 3552 | 8176 | 8192 | 4 | 0
1261982 | 7141472 | 54 | 7141472 | EA1/638988 | 6 | 0 | 1240
| 3312 | 8176 | 8192 | 4 | 0
1364767 | 3631161 | 27 | 3631161 | EA1/63A0A8 | 6 | 0 | 1180
| 3552 | 8176 | 8192 | 4 | 0
1519286 | 311808 | 2 | 311808 | EA1/708B08 | 6 | 1 | 312
| 3040 | 8192 | 8192 | 4 | 0
(5 rows)
=# select
(heap_page_items(get_raw_page(16531::regclass::text,'main',18090059))).*;
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 |
t_ctid | t_infomask2 | t_infomask | t_hoff |
t_bits | t_oid
----+--------+----------+--------+------------+------------+----------+--------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
1 | 7024 | 1 | 328 | 1358232426 | 1401029863 | 1 |
(9386399,3) | 49163 | 9475 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
2 | 1 | 2 | 0 | | | |
| | | |
|
3 | 6560 | 1 | 464 | 1401029863 | 0 | 1 |
(9386399,3) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
4 | 5 | 2 | 0 | | | |
| | | |
|
5 | 8008 | 1 | 184 | 2 | 0 | 0 |
(9386399,5) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
6 | 6416 | 1 | 144 | 1418089052 | 1418089053 | 0 |
(9386399,7) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
7 | 6272 | 1 | 144 | 1418089053 | 0 | 0 |
(9386399,7) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
8 | 6128 | 1 | 144 | 1418089056 | 1418089060 | 0 |
(9386399,11) | 16395 | 1283 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
9 | 10 | 2 | 0 | | | |
| | | |
|
10 | 7864 | 1 | 144 | 2 | 0 | 0 |
(9386399,10) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
11 | 4576 | 1 | 1552 | 1418089060 | 1418089062 | 1 |
(9386399,18) | 49163 | 8451 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
12 | 13 | 2 | 0 | | | |
| | | |
|
13 | 7680 | 1 | 184 | 2 | 0 | 0 |
(9386399,13) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
14 | 15 | 2 | 0 | | | |
| | | |
|
15 | 7496 | 1 | 184 | 2 | 0 | 0 |
(9386399,15) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
16 | 17 | 2 | 0 | | | |
| | | |
|
17 | 7352 | 1 | 144 | 2 | 0 | 0 |
(9386399,17) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
18 | 3024 | 1 | 1552 | 1418089062 | 0 | 1 |
(9386399,18) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
19 | 2840 | 1 | 184 | 1418089064 | 1418089067 | 0 |
(9386399,20) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
20 | 2656 | 1 | 184 | 1418089067 | 0 | 0 |
(9386399,20) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
21 | 2472 | 1 | 184 | 1418089072 | 1418089074 | 0 |
(9386399,22) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
22 | 2288 | 1 | 184 | 1418089074 | 0 | 0 |
(9386399,22) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
23 | 2104 | 1 | 184 | 1418089077 | 1418089078 | 0 |
(9386399,24) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
24 | 1920 | 1 | 184 | 1418089078 | 0 | 0 |
(9386399,24) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
25 | 1776 | 1 | 144 | 1418089087 | 1418089088 | 0 |
(9386399,26) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
26 | 1632 | 1 | 144 | 1418089088 | 0 | 0 |
(9386399,26) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
27 | 1448 | 1 | 184 | 1418089089 | 1418089092 | 0 |
(9386399,28) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
28 | 1264 | 1 | 184 | 1418089092 | 0 | 0 |
(9386399,28) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
29 | 1080 | 1 | 184 | 1418089145 | 1418089146 | 0 |
(9386399,30) | 16395 | 259 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
30 | 896 | 1 | 184 | 1418089146 | 0 | 0 |
(9386399,30) | 32779 | 10243 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
(30 rows)
=# select
(heap_page_items(get_raw_page(16531::regclass::text,'main',9386399))).*;
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid
| t_infomask2 | t_infomask | t_hoff | t_bits
| t_oid
----+--------+----------+--------+------------+--------+----------+--------------+-------------+------------+--------+--------------------------------------------------------------------------+-------
1 | 5536 | 1 | 144 | 1418089161 | 0 | 0 | (9386399,1)
| 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
2 | 3 | 2 | 0 | | | |
| | | |
|
3 | 6888 | 1 | 464 | 1401029863 | 0 | 1 | (9386399,3)
| 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
4 | 5 | 2 | 0 | | | |
| | | |
|
5 | 8008 | 1 | 184 | 2 | 0 | 0 | (9386399,5)
| 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
6 | 7 | 2 | 0 | | | |
| | | |
|
7 | 6744 | 1 | 144 | 1418089053 | 0 | 0 | (9386399,7)
| 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
8 | 0 | 3 | 0 | | | |
| | | |
|
9 | 10 | 2 | 0 | | | |
| | | |
|
10 | 7864 | 1 | 144 | 2 | 0 | 0 |
(9386399,10) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
11 | 0 | 0 | 0 | | | |
| | | |
|
12 | 13 | 2 | 0 | | | |
| | | |
|
13 | 7680 | 1 | 184 | 2 | 0 | 0 |
(9386399,13) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
14 | 15 | 2 | 0 | | | |
| | | |
|
15 | 7496 | 1 | 184 | 2 | 0 | 0 |
(9386399,15) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
16 | 17 | 2 | 0 | | | |
| | | |
|
17 | 7352 | 1 | 144 | 2 | 0 | 0 |
(9386399,17) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
18 | 0 | 0 | 0 | | | |
| | | |
|
19 | 20 | 2 | 0 | | | |
| | | |
|
20 | 6560 | 1 | 184 | 1418089067 | 0 | 0 |
(9386399,20) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
21 | 22 | 2 | 0 | | | |
| | | |
|
22 | 6376 | 1 | 184 | 1418089074 | 0 | 0 |
(9386399,22) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
23 | 24 | 2 | 0 | | | |
| | | |
|
24 | 6192 | 1 | 184 | 1418089078 | 0 | 0 |
(9386399,24) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
25 | 26 | 2 | 0 | | | |
| | | |
|
26 | 6048 | 1 | 144 | 1418089088 | 0 | 0 |
(9386399,26) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
27 | 28 | 2 | 0 | | | |
| | | |
|
28 | 5864 | 1 | 184 | 1418089092 | 0 | 0 |
(9386399,28) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
29 | 30 | 2 | 0 | | | |
| | | |
|
30 | 5680 | 1 | 184 | 1418089146 | 0 | 0 |
(9386399,30) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
31 | 1 | 2 | 0 | | | |
| | | |
|
32 | 33 | 2 | 0 | | | |
| | | |
|
33 | 5352 | 1 | 184 | 1418089169 | 0 | 0 |
(9386399,33) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
34 | 35 | 2 | 0 | | | |
| | | |
|
35 | 5168 | 1 | 184 | 1418089182 | 0 | 0 |
(9386399,35) | 32779 | 10499 | 32 |
111111111010000000000000000000000000000000000000000000000000000000000000 |
(35 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
