contrib/amcheck/t/004_verify_nbtree_unique.pl

   1
   2 # Copyright (c) 2023-2025, 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 FATAL => 'all';
   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 public.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 public.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 public.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 public.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 public.ok_cmp($1, $2);
  80         $$;
  81
  82         CREATE FUNCTION bad_cmp3 (int4, int4)
  83         RETURNS int LANGUAGE sql AS
  84         $$
  85                 SELECT public.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();