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

Commit5ae2087

Browse files
committed
Teach contrib/amcheck to check the unique constraint violation
Add the 'checkunique' argument to bt_index_check() and bt_index_parent_check().When the flag is specified the procedures will check the unique constraintviolation for unique indexes. Only one heap entry for all equal keys inthe index should be visible (including posting list entries). Report an errorotherwise.pg_amcheck called with the --checkunique option will do the same check for allthe indexes it checks.Author: Anastasia Lubennikova <lubennikovaav@gmail.com>Author: Pavel Borisov <pashkin.elfe@gmail.com>Author: Maxim Orlov <orlovmg@gmail.com>Reviewed-by: Mark Dilger <mark.dilger@enterprisedb.com>Reviewed-by: Zhihong Yu <zyu@yugabyte.com>Reviewed-by: Peter Geoghegan <pg@bowt.ie>Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>Discussion:https://postgr.es/m/CALT9ZEHRn5xAM5boga0qnrCmPV52bScEK2QnQ1HmUZDD301JEg%40mail.gmail.com
1 parent8b62b44 commit5ae2087

File tree

13 files changed

+830
-23
lines changed

13 files changed

+830
-23
lines changed

‎contrib/amcheck/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ OBJS = \
77
verify_nbtree.o
88

99
EXTENSION = amcheck
10-
DATA = amcheck--1.2--1.3.sql amcheck--1.1--1.2.sql amcheck--1.0--1.1.sql amcheck--1.0.sql
10+
DATA = amcheck--1.3--1.4.sql amcheck--1.2--1.3.sql amcheck--1.1--1.2.sql amcheck--1.0--1.1.sql amcheck--1.0.sql
1111
PGFILEDESC = "amcheck - function for verifying relation integrity"
1212

1313
REGRESS = check check_btree check_heap

‎contrib/amcheck/amcheck--1.3--1.4.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
/* contrib/amcheck/amcheck--1.3--1.4.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"ALTER EXTENSION amcheck UPDATE TO '1.4'" to load this file. \quit
5+
6+
-- In order to avoid issues with dependencies when updating amcheck to 1.4,
7+
-- create new, overloaded versions of the 1.2 bt_index_parent_check signature,
8+
-- and 1.1 bt_index_check signature.
9+
10+
--
11+
-- bt_index_parent_check()
12+
--
13+
CREATEFUNCTIONbt_index_parent_check(index regclass,
14+
heapallindexedboolean, rootdescendboolean, checkuniqueboolean)
15+
RETURNS VOID
16+
AS'MODULE_PATHNAME','bt_index_parent_check'
17+
LANGUAGE C STRICT PARALLEL RESTRICTED;
18+
--
19+
-- bt_index_check()
20+
--
21+
CREATEFUNCTIONbt_index_check(index regclass,
22+
heapallindexedboolean, checkuniqueboolean)
23+
RETURNS VOID
24+
AS'MODULE_PATHNAME','bt_index_check'
25+
LANGUAGE C STRICT PARALLEL RESTRICTED;
26+
27+
-- We don't want this to be available to public
28+
REVOKE ALLON FUNCTION bt_index_parent_check(regclass,boolean,boolean,boolean)FROM PUBLIC;
29+
REVOKE ALLON FUNCTION bt_index_check(regclass,boolean,boolean)FROM PUBLIC;

‎contrib/amcheck/amcheck.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# amcheck extension
22
comment = 'functions for verifying relation integrity'
3-
default_version = '1.3'
3+
default_version = '1.4'
44
module_pathname = '$libdir/amcheck'
55
relocatable = true

‎contrib/amcheck/expected/check_btree.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,12 +199,54 @@ SELECT bt_index_check('bttest_a_expr_idx', true);
199199

200200
(1 row)
201201

202+
-- UNIQUE constraint check
203+
SELECT bt_index_check('bttest_a_idx', heapallindexed => true, checkunique => true);
204+
bt_index_check
205+
----------------
206+
207+
(1 row)
208+
209+
SELECT bt_index_check('bttest_b_idx', heapallindexed => false, checkunique => true);
210+
bt_index_check
211+
----------------
212+
213+
(1 row)
214+
215+
SELECT bt_index_parent_check('bttest_a_idx', heapallindexed => true, rootdescend => true, checkunique => true);
216+
bt_index_parent_check
217+
-----------------------
218+
219+
(1 row)
220+
221+
SELECT bt_index_parent_check('bttest_b_idx', heapallindexed => true, rootdescend => false, checkunique => true);
222+
bt_index_parent_check
223+
-----------------------
224+
225+
(1 row)
226+
227+
-- Check that null values in an unique index are not treated as equal
228+
CREATE TABLE bttest_unique_nulls (a serial, b int, c int UNIQUE);
229+
INSERT INTO bttest_unique_nulls VALUES (generate_series(1, 10000), 2, default);
230+
SELECT bt_index_check('bttest_unique_nulls_c_key', heapallindexed => true, checkunique => true);
231+
bt_index_check
232+
----------------
233+
234+
(1 row)
235+
236+
CREATE INDEX on bttest_unique_nulls (b,c);
237+
SELECT bt_index_check('bttest_unique_nulls_b_c_idx', heapallindexed => true, checkunique => true);
238+
bt_index_check
239+
----------------
240+
241+
(1 row)
242+
202243
-- cleanup
203244
DROP TABLE bttest_a;
204245
DROP TABLE bttest_b;
205246
DROP TABLE bttest_multi;
206247
DROP TABLE delete_test_table;
207248
DROP TABLE toast_bug;
208249
DROP FUNCTION ifun(int8);
250+
DROP TABLE bttest_unique_nulls;
209251
DROP OWNED BY regress_bttest_role; -- permissions
210252
DROP ROLE regress_bttest_role;

‎contrib/amcheck/meson.build

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ install_data(
2323
'amcheck--1.0--1.1.sql',
2424
'amcheck--1.1--1.2.sql',
2525
'amcheck--1.2--1.3.sql',
26+
'amcheck--1.3--1.4.sql',
2627
kwargs: contrib_data_args,
2728
)
2829

@@ -42,6 +43,7 @@ tests += {
4243
't/001_verify_heapam.pl',
4344
't/002_cic.pl',
4445
't/003_cic_2pc.pl',
46+
't/004_verify_nbtree_unique.pl',
4547
],
4648
},
4749
}

‎contrib/amcheck/sql/check_btree.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,12 +135,26 @@ CREATE INDEX bttest_a_expr_idx ON bttest_a ((ifun(id) + ifun(0)))
135135

136136
SELECT bt_index_check('bttest_a_expr_idx', true);
137137

138+
-- UNIQUE constraint check
139+
SELECT bt_index_check('bttest_a_idx', heapallindexed=> true, checkunique=> true);
140+
SELECT bt_index_check('bttest_b_idx', heapallindexed=> false, checkunique=> true);
141+
SELECT bt_index_parent_check('bttest_a_idx', heapallindexed=> true, rootdescend=> true, checkunique=> true);
142+
SELECT bt_index_parent_check('bttest_b_idx', heapallindexed=> true, rootdescend=> false, checkunique=> true);
143+
144+
-- Check that null values in an unique index are not treated as equal
145+
CREATETABLEbttest_unique_nulls (aserial, bint, cint UNIQUE);
146+
INSERT INTO bttest_unique_nullsVALUES (generate_series(1,10000),2, default);
147+
SELECT bt_index_check('bttest_unique_nulls_c_key', heapallindexed=> true, checkunique=> true);
148+
CREATEINDEXon bttest_unique_nulls (b,c);
149+
SELECT bt_index_check('bttest_unique_nulls_b_c_idx', heapallindexed=> true, checkunique=> true);
150+
138151
-- cleanup
139152
DROPTABLE bttest_a;
140153
DROPTABLE bttest_b;
141154
DROPTABLE bttest_multi;
142155
DROPTABLE delete_test_table;
143156
DROPTABLE toast_bug;
144157
DROPFUNCTION ifun(int8);
158+
DROPTABLE bttest_unique_nulls;
145159
DROP OWNED BY regress_bttest_role;-- permissions
146160
DROP ROLE regress_bttest_role;
Lines changed: 244 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,244 @@
1+
2+
# Copyright (c) 2023, PostgreSQL Global Development Group
3+
4+
# This regression test checks the behavior of the btree validation in the
5+
# presence of breaking sort order changes.
6+
#
7+
use strict;
8+
use warnings;
9+
use PostgreSQL::Test::Cluster;
10+
use PostgreSQL::Test::Utils;
11+
use Test::More;
12+
13+
my$node = PostgreSQL::Test::Cluster->new('test');
14+
$node->init;
15+
$node->append_conf('postgresql.conf','autovacuum = off');
16+
$node->start;
17+
18+
# Create a custom operator class and an index which uses it.
19+
$node->safe_psql(
20+
'postgres',q(
21+
CREATE EXTENSION amcheck;
22+
23+
CREATE FUNCTION ok_cmp (int4, int4)
24+
RETURNS int LANGUAGE sql AS
25+
$$
26+
SELECT
27+
CASE WHEN $1 < $2 THEN -1
28+
WHEN $1 > $2 THEN 1
29+
ELSE 0
30+
END;
31+
$$;
32+
33+
---
34+
--- Check 1: uniqueness violation.
35+
---
36+
CREATE FUNCTION ok_cmp1 (int4, int4)
37+
RETURNS int LANGUAGE sql AS
38+
$$
39+
SELECT ok_cmp($1, $2);
40+
$$;
41+
42+
---
43+
--- Make values 768 and 769 look equal.
44+
---
45+
CREATE FUNCTION bad_cmp1 (int4, int4)
46+
RETURNS int LANGUAGE sql AS
47+
$$
48+
SELECT
49+
CASE WHEN ($1 = 768 AND $2 = 769) OR
50+
($1 = 769 AND $2 = 768) THEN 0
51+
ELSE ok_cmp($1, $2)
52+
END;
53+
$$;
54+
55+
---
56+
--- Check 2: uniqueness violation without deduplication.
57+
---
58+
CREATE FUNCTION ok_cmp2 (int4, int4)
59+
RETURNS int LANGUAGE sql AS
60+
$$
61+
SELECT ok_cmp($1, $2);
62+
$$;
63+
64+
CREATE FUNCTION bad_cmp2 (int4, int4)
65+
RETURNS int LANGUAGE sql AS
66+
$$
67+
SELECT
68+
CASE WHEN $1 = $2 AND $1 = 400 THEN -1
69+
ELSE ok_cmp($1, $2)
70+
END;
71+
$$;
72+
73+
---
74+
--- Check 3: uniqueness violation with deduplication.
75+
---
76+
CREATE FUNCTION ok_cmp3 (int4, int4)
77+
RETURNS int LANGUAGE sql AS
78+
$$
79+
SELECT ok_cmp($1, $2);
80+
$$;
81+
82+
CREATE FUNCTION bad_cmp3 (int4, int4)
83+
RETURNS int LANGUAGE sql AS
84+
$$
85+
SELECT bad_cmp2($1, $2);
86+
$$;
87+
88+
---
89+
--- Create data.
90+
---
91+
CREATE TABLE bttest_unique1 (i int4);
92+
INSERT INTO bttest_unique1
93+
(SELECT * FROM generate_series(1, 1024) gs);
94+
95+
CREATE TABLE bttest_unique2 (i int4);
96+
INSERT INTO bttest_unique2(i)
97+
(SELECT * FROM generate_series(1, 400) gs);
98+
INSERT INTO bttest_unique2
99+
(SELECT * FROM generate_series(400, 1024) gs);
100+
101+
CREATE TABLE bttest_unique3 (i int4);
102+
INSERT INTO bttest_unique3
103+
SELECT * FROM bttest_unique2;
104+
105+
CREATE OPERATOR CLASS int4_custom_ops1 FOR TYPE int4 USING btree AS
106+
OPERATOR 1 < (int4, int4), OPERATOR 2 <= (int4, int4),
107+
OPERATOR 3 = (int4, int4), OPERATOR 4 >= (int4, int4),
108+
OPERATOR 5 > (int4, int4), FUNCTION 1 ok_cmp1(int4, int4);
109+
CREATE OPERATOR CLASS int4_custom_ops2 FOR TYPE int4 USING btree AS
110+
OPERATOR 1 < (int4, int4), OPERATOR 2 <= (int4, int4),
111+
OPERATOR 3 = (int4, int4), OPERATOR 4 >= (int4, int4),
112+
OPERATOR 5 > (int4, int4), FUNCTION 1 bad_cmp2(int4, int4);
113+
CREATE OPERATOR CLASS int4_custom_ops3 FOR TYPE int4 USING btree AS
114+
OPERATOR 1 < (int4, int4), OPERATOR 2 <= (int4, int4),
115+
OPERATOR 3 = (int4, int4), OPERATOR 4 >= (int4, int4),
116+
OPERATOR 5 > (int4, int4), FUNCTION 1 bad_cmp3(int4, int4);
117+
118+
CREATE UNIQUE INDEX bttest_unique_idx1
119+
ON bttest_unique1
120+
USING btree (i int4_custom_ops1)
121+
WITH (deduplicate_items = off);
122+
CREATE UNIQUE INDEX bttest_unique_idx2
123+
ON bttest_unique2
124+
USING btree (i int4_custom_ops2)
125+
WITH (deduplicate_items = off);
126+
CREATE UNIQUE INDEX bttest_unique_idx3
127+
ON bttest_unique3
128+
USING btree (i int4_custom_ops3)
129+
WITH (deduplicate_items = on);
130+
));
131+
132+
my ($result,$stdout,$stderr);
133+
134+
#
135+
# Test 1.
136+
# - insert seq values
137+
# - create unique index
138+
# - break cmp function
139+
# - amcheck finds the uniqueness violation
140+
#
141+
142+
# We have not yet broken the index, so we should get no corruption
143+
$result =$node->safe_psql(
144+
'postgres',q(
145+
SELECT bt_index_check('bttest_unique_idx1', true, true);
146+
));
147+
is($result,'','run amcheck on non-broken bttest_unique_idx1');
148+
149+
# Change the operator class to use a function which considers certain different
150+
# values to be equal.
151+
$node->safe_psql(
152+
'postgres',q(
153+
UPDATE pg_catalog.pg_amproc SET
154+
amproc = 'bad_cmp1'::regproc
155+
WHERE amproc = 'ok_cmp1'::regproc;
156+
));
157+
158+
($result,$stdout,$stderr) =$node->psql(
159+
'postgres',q(
160+
SELECT bt_index_check('bttest_unique_idx1', true, true);
161+
));
162+
ok($stderr =~/index uniqueness is violated for index "bttest_unique_idx1"/,
163+
'detected uniqueness violation for index "bttest_unique_idx1"');
164+
165+
#
166+
# Test 2.
167+
# - break cmp function
168+
# - insert seq values with duplicates
169+
# - create unique index
170+
# - make cmp function correct
171+
# - amcheck finds the uniqueness violation
172+
#
173+
174+
# Due to bad cmp function we expect amcheck to detect item order violation,
175+
# but no uniqueness violation.
176+
($result,$stdout,$stderr) =$node->psql(
177+
'postgres',q(
178+
SELECT bt_index_check('bttest_unique_idx2', true, true);
179+
));
180+
ok($stderr =~/item order invariant violated for index "bttest_unique_idx2"/,
181+
'detected item order invariant violation for index "bttest_unique_idx2"');
182+
183+
$node->safe_psql(
184+
'postgres',q(
185+
UPDATE pg_catalog.pg_amproc SET
186+
amproc = 'ok_cmp2'::regproc
187+
WHERE amproc = 'bad_cmp2'::regproc;
188+
));
189+
190+
($result,$stdout,$stderr) =$node->psql(
191+
'postgres',q(
192+
SELECT bt_index_check('bttest_unique_idx2', true, true);
193+
));
194+
ok($stderr =~/index uniqueness is violated for index "bttest_unique_idx2"/,
195+
'detected uniqueness violation for index "bttest_unique_idx2"');
196+
197+
#
198+
# Test 3.
199+
# - same as Test 2, but with index deduplication
200+
#
201+
# Then uniqueness violation is detected between different posting list
202+
# entries inside one index entry.
203+
#
204+
205+
# Due to bad cmp function we expect amcheck to detect item order violation,
206+
# but no uniqueness violation.
207+
($result,$stdout,$stderr) =$node->psql(
208+
'postgres',q(
209+
SELECT bt_index_check('bttest_unique_idx3', true, true);
210+
));
211+
ok($stderr =~/item order invariant violated for index "bttest_unique_idx3"/,
212+
'detected item order invariant violation for index "bttest_unique_idx3"');
213+
214+
# For unique index deduplication is possible only for same values, but
215+
# with different visibility.
216+
$node->safe_psql(
217+
'postgres',q(
218+
DELETE FROM bttest_unique3 WHERE 380 <= i AND i <= 420;
219+
INSERT INTO bttest_unique3 (SELECT * FROM generate_series(380, 420));
220+
INSERT INTO bttest_unique3 VALUES (400);
221+
DELETE FROM bttest_unique3 WHERE 380 <= i AND i <= 420;
222+
INSERT INTO bttest_unique3 (SELECT * FROM generate_series(380, 420));
223+
INSERT INTO bttest_unique3 VALUES (400);
224+
DELETE FROM bttest_unique3 WHERE 380 <= i AND i <= 420;
225+
INSERT INTO bttest_unique3 (SELECT * FROM generate_series(380, 420));
226+
INSERT INTO bttest_unique3 VALUES (400);
227+
));
228+
229+
$node->safe_psql(
230+
'postgres',q(
231+
UPDATE pg_catalog.pg_amproc SET
232+
amproc = 'ok_cmp3'::regproc
233+
WHERE amproc = 'bad_cmp3'::regproc;
234+
));
235+
236+
($result,$stdout,$stderr) =$node->psql(
237+
'postgres',q(
238+
SELECT bt_index_check('bttest_unique_idx3', true, true);
239+
));
240+
ok($stderr =~/index uniqueness is violated for index "bttest_unique_idx3"/,
241+
'detected uniqueness violation for index "bttest_unique_idx3"');
242+
243+
$node->stop;
244+
done_testing();

0 commit comments

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