Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3717dc1

Browse files
committed
Add amcheck extension to contrib.
This is the beginning of a collection of SQL-callable functions toverify the integrity of data files. For now it only contains code toverify B-Tree indexes.This adds two SQL-callable functions, validating B-Tree consistency toa varying degree. Check the, extensive, docs for details.The goal is to later extend the coverage of the module to furtheraccess methods, possibly including the heap. Once checks foradditional access methods exist, we'll likely add some "dispatch"functions that cover multiple access methods.Author: Peter Geoghegan, editorialized by Andres FreundReviewed-By: Andres Freund, Tomas Vondra, Thomas Munro, Anastasia Lubennikova, Robert Haas, Amit LangoteDiscussion: CAM3SWZQzLMhMwmBqjzK+pRKXrNUZ4w90wYMUWfkeV8mZ3Debvw@mail.gmail.com
1 parentfe797b4 commit3717dc1

File tree

13 files changed

+1728
-0
lines changed

13 files changed

+1728
-0
lines changed

‎contrib/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ include $(top_builddir)/src/Makefile.global
66

77
SUBDIRS =\
88
adminpack\
9+
amcheck\
910
auth_delay\
1011
auto_explain\
1112
bloom\

‎contrib/amcheck/Makefile

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
# contrib/amcheck/Makefile
2+
3+
MODULE_big= amcheck
4+
OBJS= verify_nbtree.o$(WIN32RES)
5+
6+
EXTENSION = amcheck
7+
DATA = amcheck--1.0.sql
8+
PGFILEDESC = "amcheck - function for verifying relation integrity"
9+
10+
REGRESS = check check_btree
11+
12+
ifdefUSE_PGXS
13+
PG_CONFIG = pg_config
14+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
15+
include$(PGXS)
16+
else
17+
subdir = contrib/amcheck
18+
top_builddir = ../..
19+
include$(top_builddir)/src/Makefile.global
20+
include$(top_srcdir)/contrib/contrib-global.mk
21+
endif

‎contrib/amcheck/amcheck--1.0.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
/* contrib/amcheck/amcheck--1.0.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION amcheck" to load this file. \quit
5+
6+
--
7+
-- bt_index_check()
8+
--
9+
CREATEFUNCTIONbt_index_check(index regclass)
10+
RETURNS VOID
11+
AS'MODULE_PATHNAME','bt_index_check'
12+
LANGUAGE C STRICT PARALLEL RESTRICTED;
13+
14+
--
15+
-- bt_index_parent_check()
16+
--
17+
CREATEFUNCTIONbt_index_parent_check(index regclass)
18+
RETURNS VOID
19+
AS'MODULE_PATHNAME','bt_index_parent_check'
20+
LANGUAGE C STRICT PARALLEL RESTRICTED;
21+
22+
-- Don't want these to be available to public
23+
REVOKE ALLON FUNCTION bt_index_check(regclass)FROM PUBLIC;
24+
REVOKE ALLON FUNCTION bt_index_parent_check(regclass)FROM PUBLIC;

‎contrib/amcheck/amcheck.control

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
# amcheck extension
2+
comment = 'functions for verifying relation integrity'
3+
default_version = '1.0'
4+
module_pathname = '$libdir/amcheck'
5+
relocatable = true

‎contrib/amcheck/expected/check.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
CREATE EXTENSION amcheck;
Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,90 @@
1+
-- minimal test, basically just verifying that amcheck
2+
CREATE TABLE bttest_a(id int8);
3+
CREATE TABLE bttest_b(id int8);
4+
INSERT INTO bttest_a SELECT * FROM generate_series(1, 100000);
5+
INSERT INTO bttest_b SELECT * FROM generate_series(100000, 1, -1);
6+
CREATE INDEX bttest_a_idx ON bttest_a USING btree (id);
7+
CREATE INDEX bttest_b_idx ON bttest_b USING btree (id);
8+
CREATE ROLE bttest_role;
9+
-- verify permissions are checked (error due to function not callable)
10+
SET ROLE bttest_role;
11+
SELECT bt_index_check('bttest_a_idx'::regclass);
12+
ERROR: permission denied for function bt_index_check
13+
SELECT bt_index_parent_check('bttest_a_idx'::regclass);
14+
ERROR: permission denied for function bt_index_parent_check
15+
RESET ROLE;
16+
-- we, intentionally, don't check relation permissions - it's useful
17+
-- to run this cluster-wide with a restricted account, and as tested
18+
-- above explicit permission has to be granted for that.
19+
GRANT EXECUTE ON FUNCTION bt_index_check(regclass) TO bttest_role;
20+
GRANT EXECUTE ON FUNCTION bt_index_parent_check(regclass) TO bttest_role;
21+
SET ROLE bttest_role;
22+
SELECT bt_index_check('bttest_a_idx');
23+
bt_index_check
24+
----------------
25+
26+
(1 row)
27+
28+
SELECT bt_index_parent_check('bttest_a_idx');
29+
bt_index_parent_check
30+
-----------------------
31+
32+
(1 row)
33+
34+
RESET ROLE;
35+
-- verify plain tables are rejected (error)
36+
SELECT bt_index_check('bttest_a');
37+
ERROR: "bttest_a" is not an index
38+
SELECT bt_index_parent_check('bttest_a');
39+
ERROR: "bttest_a" is not an index
40+
-- verify non-existing indexes are rejected (error)
41+
SELECT bt_index_check(17);
42+
ERROR: could not open relation with OID 17
43+
SELECT bt_index_parent_check(17);
44+
ERROR: could not open relation with OID 17
45+
-- verify wrong index types are rejected (error)
46+
BEGIN;
47+
CREATE INDEX bttest_a_brin_idx ON bttest_a USING brin(id);
48+
SELECT bt_index_parent_check('bttest_a_brin_idx');
49+
ERROR: only B-Tree indexes are supported as targets for verification
50+
DETAIL: Relation "bttest_a_brin_idx" is not a B-Tree index.
51+
ROLLBACK;
52+
-- normal check outside of xact
53+
SELECT bt_index_check('bttest_a_idx');
54+
bt_index_check
55+
----------------
56+
57+
(1 row)
58+
59+
-- more expansive test
60+
SELECT bt_index_parent_check('bttest_b_idx');
61+
bt_index_parent_check
62+
-----------------------
63+
64+
(1 row)
65+
66+
BEGIN;
67+
SELECT bt_index_check('bttest_a_idx');
68+
bt_index_check
69+
----------------
70+
71+
(1 row)
72+
73+
SELECT bt_index_parent_check('bttest_b_idx');
74+
bt_index_parent_check
75+
-----------------------
76+
77+
(1 row)
78+
79+
-- make sure we don't have any leftover locks
80+
SELECT * FROM pg_locks WHERE relation IN ('bttest_a_idx'::regclass, 'bttest_b_idx'::regclass);
81+
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
82+
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------
83+
(0 rows)
84+
85+
COMMIT;
86+
-- cleanup
87+
DROP TABLE bttest_a;
88+
DROP TABLE bttest_b;
89+
DROP OWNED BY bttest_role; -- permissions
90+
DROP ROLE bttest_role;

‎contrib/amcheck/sql/check.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
CREATE EXTENSION amcheck;

‎contrib/amcheck/sql/check_btree.sql

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
-- minimal test, basically just verifying that amcheck
2+
CREATETABLEbttest_a(id int8);
3+
CREATETABLEbttest_b(id int8);
4+
5+
INSERT INTO bttest_aSELECT*FROM generate_series(1,100000);
6+
INSERT INTO bttest_bSELECT*FROM generate_series(100000,1,-1);
7+
8+
CREATEINDEXbttest_a_idxON bttest_a USING btree (id);
9+
CREATEINDEXbttest_b_idxON bttest_b USING btree (id);
10+
11+
CREATE ROLE bttest_role;
12+
13+
-- verify permissions are checked (error due to function not callable)
14+
SET ROLE bttest_role;
15+
SELECT bt_index_check('bttest_a_idx'::regclass);
16+
SELECT bt_index_parent_check('bttest_a_idx'::regclass);
17+
RESET ROLE;
18+
19+
-- we, intentionally, don't check relation permissions - it's useful
20+
-- to run this cluster-wide with a restricted account, and as tested
21+
-- above explicit permission has to be granted for that.
22+
GRANT EXECUTEON FUNCTION bt_index_check(regclass) TO bttest_role;
23+
GRANT EXECUTEON FUNCTION bt_index_parent_check(regclass) TO bttest_role;
24+
SET ROLE bttest_role;
25+
SELECT bt_index_check('bttest_a_idx');
26+
SELECT bt_index_parent_check('bttest_a_idx');
27+
RESET ROLE;
28+
29+
-- verify plain tables are rejected (error)
30+
SELECT bt_index_check('bttest_a');
31+
SELECT bt_index_parent_check('bttest_a');
32+
33+
-- verify non-existing indexes are rejected (error)
34+
SELECT bt_index_check(17);
35+
SELECT bt_index_parent_check(17);
36+
37+
-- verify wrong index types are rejected (error)
38+
BEGIN;
39+
CREATEINDEXbttest_a_brin_idxON bttest_a USING brin(id);
40+
SELECT bt_index_parent_check('bttest_a_brin_idx');
41+
ROLLBACK;
42+
43+
-- normal check outside of xact
44+
SELECT bt_index_check('bttest_a_idx');
45+
-- more expansive test
46+
SELECT bt_index_parent_check('bttest_b_idx');
47+
48+
BEGIN;
49+
SELECT bt_index_check('bttest_a_idx');
50+
SELECT bt_index_parent_check('bttest_b_idx');
51+
-- make sure we don't have any leftover locks
52+
SELECT*FROM pg_locksWHERE relationIN ('bttest_a_idx'::regclass,'bttest_b_idx'::regclass);
53+
COMMIT;
54+
55+
-- cleanup
56+
DROPTABLE bttest_a;
57+
DROPTABLE bttest_b;
58+
DROP OWNED BY bttest_role;-- permissions
59+
DROP ROLE bttest_role;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp