作者
digoal
日期
2020-10-24
标签
PostgreSQL , amcheck , 数据文件逻辑错误
背景
amcheck是PG的数据和索引逻辑错误检测插件.
https://www.postgresql.org/docs/devel/amcheck.html
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=866e24d47db1743dfcff5bd595b57e3a143f2cb1
```
Extend amcheck to check heap pages.
Mark Dilger, reviewed by Peter Geoghegan, Andres Freund, Álvaro Herrera,
Michael Paquier, Amul Sul, and by me. Some last-minute cosmetic
revisions by me.
Discussion: http://postgr.es/m/12ED3DA8-25F0-4B68-937D-D907CFBF08E7@enterprisedb.com
```
PG 14新增了heap表的数据页逻辑错误检测功能
verify_heapam(relation regclass, on_error_stop boolean, check_toast boolean, skip cstring, startblock bigint, endblock bigint, blkno OUT bigint, offnum OUT integer, attnum OUT integer, msg OUT text) returns record
Checks a table for structural corruption, where pages in the relation contain data that is invalidly formatted, and for logical corruption, where pages are structurally valid but inconsistent with the rest of the database cluster. Example usage:
错误例子
test=# select * from verify_heapam('mytable', check_toast := true);
blkno | offnum | attnum | msg
-------+--------+--------+--------------------------------------------------------------------------------------------------
17 | 12 | | xmin 4294967295 precedes relation freeze threshold 17:1134217582
960 | 4 | | data begins at offset 152 beyond the tuple length 58
960 | 4 | | tuple data should begin at byte 24, but actually begins at byte 152 (3 attributes, no nulls)
960 | 5 | | tuple data should begin at byte 24, but actually begins at byte 27 (3 attributes, no nulls)
960 | 6 | | tuple data should begin at byte 24, but actually begins at byte 16 (3 attributes, no nulls)
960 | 7 | | tuple data should begin at byte 24, but actually begins at byte 21 (3 attributes, no nulls)
1147 | 2 | | number of attributes 2047 exceeds maximum expected for table 3
1147 | 10 | | tuple data should begin at byte 280, but actually begins at byte 24 (2047 attributes, has nulls)
1147 | 15 | | number of attributes 67 exceeds maximum expected for table 3
1147 | 16 | 1 | attribute 1 with length 4294967295 ends at offset 416848000 beyond total tuple length 58
1147 | 18 | 2 | final toast chunk number 0 differs from expected value 6
1147 | 19 | 2 | toasted value for attribute 2 missing from toast table
1147 | 21 | | tuple is marked as only locked, but also claims key columns were updated
1147 | 22 | | multitransaction ID 1775655 is from before relation cutoff 2355572
(14 rows)
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
