Skip to content

Commit 5ae2087

Browse files
akorotkovakorotkov
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 constraint violation for unique indexes. Only one heap entry for all equal keys in the index should be visible (including posting list entries). Report an error otherwise. pg_amcheck called with the --checkunique option will do the same check for all the 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 parent 8b62b44 commit 5ae2087

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+
CREATE FUNCTION bt_index_parent_check(index regclass,
14+
heapallindexed boolean, rootdescend boolean, checkunique boolean)
15+
RETURNS VOID
16+
AS 'MODULE_PATHNAME', 'bt_index_parent_check'
17+
LANGUAGE C STRICT PARALLEL RESTRICTED;
18+
--
19+
-- bt_index_check()
20+
--
21+
CREATE FUNCTION bt_index_check(index regclass,
22+
heapallindexed boolean, checkunique boolean)
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 ALL ON FUNCTION bt_index_parent_check(regclass, boolean, boolean, boolean) FROM PUBLIC;
29+
REVOKE ALL ON 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+
CREATE TABLE bttest_unique_nulls (a serial, b int, c int UNIQUE);
146+
INSERT INTO bttest_unique_nulls VALUES (generate_series(1, 10000), 2, default);
147+
SELECT bt_index_check('bttest_unique_nulls_c_key', heapallindexed => true, checkunique => true);
148+
CREATE INDEX on bttest_unique_nulls (b,c);
149+
SELECT bt_index_check('bttest_unique_nulls_b_c_idx', heapallindexed => true, checkunique => true);
150+
138151
-- cleanup
139152
DROP TABLE bttest_a;
140153
DROP TABLE bttest_b;
141154
DROP TABLE bttest_multi;
142155
DROP TABLE delete_test_table;
143156
DROP TABLE toast_bug;
144157
DROP FUNCTION ifun(int8);
158+
DROP TABLE bttest_unique_nulls;
145159
DROP OWNED BY regress_bttest_role; -- permissions
146160
DROP ROLE regress_bttest_role;

‎contrib/amcheck/t/004_verify_nbtree_unique.pl

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)