8000 Teach contrib/amcheck to check the unique constraint violation · postgrespro/postgres@5ae2087 · GitHub
[go: up one dir, main page]
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
  • 005_opclass_damage.pl

    • 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)
    0