Обсуждение: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.

> On Feb 8, 2021, at 2:46 AM, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>
> 0002 - is a temporary hack for testing. It will allow inserting duplicates in a table even if an index with the exact
name"idx" has a unique constraint (generally it is prohibited to insert). Then a new amcheck will tell us about these
duplicates.It's pity but testing can not be done automatically, as it needs a core recompile. For testing I'd recommend
aprotocol similar to the following: 
>
> - Apply patch 0002
> - Set autovaccum = off in postgresql.conf

Thanks Pavel and Anastasia for working on this!

Updating pg_catalog directly is ugly, but the following seems a simpler way to set up a regression test than having to
recompile. What do you think? 

CREATE TABLE junk (t text);
CREATE UNIQUE INDEX junk_idx ON junk USING btree (t);
INSERT INTO junk (t) VALUES ('fee'), ('fi'), ('fo'), ('fum');
UPDATE pg_catalog.pg_index
    SET indisunique = false
    WHERE indrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'junk');
INSERT INTO junk (t) VALUES ('fee'), ('fi'), ('fo'), ('fum');
UPDATE pg_catalog.pg_index
    SET indisunique = true
    WHERE indrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'junk');
SELECT * FROM junk;
  t
-----
 fee
 fi
 fo
 fum
 fee
 fi
 fo
 fum
(8 rows)

\d junk
              Table "public.junk"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 t      | text |           |          |
Indexes:
    "junk_idx" UNIQUE, btree (t)

\d junk_idx
      Index "public.junk_idx"
 Column | Type | Key? | Definition
--------+------+------+------------
 t      | text | yes  | t
unique, btree, for table "public.junk"

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 3/15/21 11:11 AM, Mark Dilger wrote:
> 
>> On Mar 2, 2021, at 6:08 AM, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>>
>> I completely agree that checking uniqueness requires looking at the heap, but I don't agree that every caller of
bt_index_checkon an index wants that particular check to be performed.  There are multiple ways in which an index might
becorrupt, and Peter wrote the code to only check some of them by default, with options to expand the checks to other
things. This is why heapallindexed is optional.  If you don't want to pay the price of checking all entries in the heap
againstthe btree, you don't have to.
 
>>
>> I've got the idea and revised the patch accordingly. Thanks!
>> Pfa v4 of a patch. I've added an optional argument to allow uniqueness checks for the unique indexes.
>> Also, I added a test variant to make them work on 32-bit systems. Unfortunately, converting the regression test to
TAPwould be a pain for me. Hope it can be used now as a 2-variant regression test for 32 and 64 bit systems.
 
>>
>> Thank you for your consideration!
>>
>> -- 
>> Best regards,
>> Pavel Borisov
>>
>> Postgres Professional: http://postgrespro.com
>> <v4-0001-Make-amcheck-checking-UNIQUE-constraint-for-btree.patch>
> 
> Looking over v4, here are my review comments...

This patch appears to need some work and has not been updated in several 
weeks, so marking Returned with Feedback.

Please submit to the next CF when you have a new patch.

Regards,
-- 
-David
david@pgmasters.net

> On Dec 20, 2021, at 7:37 AM, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>
> The patch is pgindented and rebased on the current PG master code.

Thank you, Pavel.


The tests in check_btree.sql no longer create a bttest_unique table, so the DROP TABLE is surplusage:

+DROP TABLE bttest_unique;
+ERROR:  table "bttest_unique" does not exist


The changes in pg_amcheck.c to pass the new checkunique parameter will likely need to be based on a amcheck version
check. The implementation of prepare_btree_command() in pg_amcheck.c should be kept compatible with older versions of
amcheck,because it connects to remote servers and you can't know in advance that the remote servers are as up-to-date
asthe machine where pg_amcheck is installed.  I'm thinking specifically about this change: 

@@ -871,7 +877,8 @@ prepare_btree_command(PQExpBuffer sql, RelationInfo *rel, PGconn *conn)
    if (opts.parent_check)
        appendPQExpBuffer(sql,
                          "SELECT %s.bt_index_parent_check("
-                         "index := c.oid, heapallindexed := %s, rootdescend := %s)"
+                         "index := c.oid, heapallindexed := %s, rootdescend := %s, "
+                         "checkunique := %s)"
                          "\nFROM pg_catalog.pg_class c, pg_catalog.pg_index i "
                          "WHERE c.oid = %u "
                          "AND c.oid = i.indexrelid "

If the user calls pg_amcheck with --checkunique, and one or more remote servers have an amcheck version < 1.4, at a
minimumyou'll need to avoid calling bt_index_parent_check with that parameter, and probably also you'll either need to
raisea warning or perhaps an error telling the user that such a check cannot be performed. 


You've forgotten to include contrib/amcheck/amcheck--1.3--1.4.sql in the v5 patch, resulting in a failed install:

/usr/bin/install -c -m 644 ./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
'/Users/mark.dilger/hydra/unique_review.5/tmp_install/Users/mark.dilger/pgtest/test_install/share/postgresql/extension/'
install: ./amcheck--1.3--1.4.sql: No such file or directory
make[2]: *** [install] Error 71
make[1]: *** [checkprep] Error 2

Using the one from the v4 patch fixes the problem.  Please include this file in v6, to simplify the review process.


The changes to t/005_opclass_damage.pl look ok.  The creation of a new table for the new test seems unnecessary, but
onlyproblematic in that it makes the test slightly longer to read.  I recommend changing the test to use the same table
thatthe prior test uses, but that is just a recommendation, not a requirement. 

You should add coverage for --checkunique to t/003_check.pl.

You should add coverage for multiple PostgreSQL::Test::Cluster instances running differing versions of amcheck, perhaps
someon version 1.3 and some on version 1.4.  Then test that the --checkunique option works adequately. 


I have not reviewed the changes to verify_nbtree.c.  I'll leave that to Peter.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> On Dec 22, 2021, at 12:01 AM, Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>
> Thank you, Mark!
>
> In v6 (PFA) I've made the changes on your advice i.e.
>
> - pg_amcheck with --checkunique option will ignore uniqueness check (with a warning) if amcheck version in a db is
<1.4and doesn't support the feature. 

Ok.

+           int         vmaj = 0,
+                       vmin = 0,
+                       vrev = 0;
+           const char *amcheck_version = pstrdup(PQgetvalue(result, 0, 1));
+
+           sscanf(amcheck_version, "%d.%d.%d", &vmaj, &vmin, &vrev);

The pstrdup is unnecessary but harmless.

> - fixed unnecessary drop table in regression

Ok.

> - use the existing table for uniqueness check in 005_opclass_damage.pl

It appears you still create a new table, bttest_unique, rather than using the existing table int4tbl.  That's fine.

> - added tests into 003_check.pl . It is only smoke test that just verifies new functions.

+
+$node->command_checks_all(
+   [
+       @cmd, '-s', 's1', '-i', 't1_btree', '--parent-check',
+       '--checkunique', 'db1'
+   ],
+   2,
+   [$index_missing_relation_fork_re],
+   [$no_output_re],
+   'pg_amcheck smoke test --parent-check');
+
+$node->command_checks_all(
+   [
+       @cmd, '-s', 's1', '-i', 't1_btree', '--heapallindexed',
+       '--rootdescend', '--checkunique',  'db1'
+   ],
+   2,
+   [$index_missing_relation_fork_re],
+   [$no_output_re],
+   'pg_amcheck smoke test --heapallindexed --rootdescend');
+
+$node->command_checks_all(
+   [ @cmd, '--checkunique', '-d', 'db1', '-d', 'db2', '-d', 'db3', '-S', 's*' ],
+   0, [$no_output_re], [$no_output_re],
+   'pg_amcheck excluding all corrupt schemas');
+

You have borrowed the existing tests but forgot to change their names.  (The last line of each check is the test name,
suchas 'pg_amcheck smoke test --parent-check'.)  Please make each test name unique. 

> - added test contrib/amcheck/t/004_verify_nbtree_unique.pl it is more extensive test based on opclass damage which
wasintended to be main test for amcheck, but which I've forgotten to add to commit in v5. 
> 005_opclass_damage.pl test, which you've seen in v5 is largely based on first part of 004_verify_nbtree_unique.pl
(withthe later calling pg_amcheck, and the former calling bt_index_check(), bt_index_parent_check() ) 

Ok.

> - added forgotten upgrade script amcheck--1.3--1.4.sql (from v4)

Ok.


—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

On Fri, Dec 24, 2021 at 2:06 AM Максим Орлов <orlovmg@gmail.com> wrote:
>
> Thanks for your review! Fixed all these remaining things from patch v6.
> PFA v7 patch.

The cfbot reports that you have mixed declarations and code
(https://cirrus-ci.com/task/6407449413419008):

[17:21:26.926] pg_amcheck.c: In function ‘main’:
[17:21:26.926] pg_amcheck.c:634:4: error: ISO C90 forbids mixed
declarations and code [-Werror=declaration-after-statement]
[17:21:26.926] 634 | int vmaj = 0,
[17:21:26.926] | ^~~

This patch was broken by d16773cdc86210493a2874cb0cf93f3883fcda73 "Add
macros in hash and btree AMs to get the special area of their pages"

If it's really just a few macros it should be easy enough to merge but
it would be good to do a rebase given the number of other commits
since February anyways.

On Mon, 21 Feb 2022 at 09:14, Maxim Orlov <orlovmg@gmail.com> wrote:
>
> I've updated the patch due to recent changes by Daniel Gustafsson (549ec201d6132b7).
>
> --
> Best regards,
> Maxim Orlov.



-- 
greg

Hi Pavel,

> Rebased. PFA v13.
> Your reviews are very much welcome!

I noticed that this patch is in "Needs Review" state and it has been
stuck for some time now, so I decided to take a look.

```
+SELECT bt_index_parent_check('bttest_a_idx', true, true, true);
+SELECT bt_index_parent_check('bttest_b_idx', true, false, true);
``

1. This "true, false, true" sequence is difficult to read. I suggest
we use named arguments here.

2. I believe there are some minor issues with the comments. E.g. instead of:

- First key on next page is same
- Make values 768 and 769 looks equal

I would write:

- The first key on the next page is the same
- Make values 768 and 769 look equal

There are many little errors like these.

```
+# Copyright (c) 2021, PostgreSQL Global Development Group
```

3. Oh no. The copyright has expired!

```
+      <literal>true</literal>.  When <parameter>checkunique</parameter>
+      is <literal>true</literal> <function>bt_index_check</function> will
```

4. This piece of documentation was copy-pasted between two functions
without change of the function name.

Other than that to me the patch looks in pretty good shape. Here is
v14 where I fixed my own nitpicks, with the permission of Pavel given
offlist.

If no one sees any other defects I'm going to change the status of the
patch to "Ready to Committer" in a short time.

--
Best regards,
Aleksander Alekseev

Hi!

I would make two cosmetic changes.

1. I suggest replace description of function bt_report_duplicate() from
```
/*
  * Prepare and print an error message for unique constrain violation in
  * a btree index under WARNING level. Also set a flag to report ERROR
  * at the end of the check.
  */
```
to
```
/*
  * Prepare an error message for unique constrain violation in
  * a btree index and report ERROR.
  */
```

2. I think will be better to change test 004_verify_nbtree_unique.pl - 
replace
```
use Test::More tests => 6;
```
to
```
use Test::More;
...
done_testing();
```
(same as in the other three tests).

-- 
With best regards,
Dmitry Koval

Postgres Professional: http://postgrespro.com

Hi,

On 2022-05-20 17:46:38 +0400, Pavel Borisov wrote:
> CFbot says v12 patch does not apply.
> Rebased. PFA v13.
> Your reviews are very much welcome!

Due to the merge of the meson based build this patch needs to be
adjusted: https://cirrus-ci.com/build/6350479973154816

Looks like you need to add amcheck--1.3--1.4.sql to the list of files to be
installed and t/004_verify_nbtree_unique.pl to the tests.

Greetings,

Andres Freund

Hi hackers,

> I think, this patch was marked as "Waiting on Author", probably, by mistake. Since recent changes were done without
anysignificant code changes and CF bot how happy again.
 
>
> I'm going to move it to RfC, could I? If not, please tell why.

I restored the "Ready for Committer" state. I don't think it's a good
practice to change the state every time the patch has a slight
conflict or something. This is not helpful at all. Such things happen
quite regularly and typically are fixed in a couple of days.

-- 
Best regards,
Aleksander Alekseev

On Wed, Sep 28, 2022 at 11:44 AM Aleksander Alekseev
<aleksander@timescale.com> wrote:
> > I think, this patch was marked as "Waiting on Author", probably, by mistake. Since recent changes were done without
anysignificant code changes and CF bot how happy again. 
> >
> > I'm going to move it to RfC, could I? If not, please tell why.
>
> I restored the "Ready for Committer" state. I don't think it's a good
> practice to change the state every time the patch has a slight
> conflict or something. This is not helpful at all. Such things happen
> quite regularly and typically are fixed in a couple of days.

This patch seems useful to me.  I went through the thread, it seems
that all the critics are addressed.

I've rebased this patch.   Also, I've run perltidy for tests, split
long errmsg() into errmsg(), errdetail() and errhint(), and do other
minor enchantments.

I think this patch is ready to go.  I'm going to push it if there are
no objections.

------
Regards,
Alexander Korotkov

Hi, Alexander!


On Wed, 25 Oct 2023 at 00:13, Alexander Korotkov <aekorotkov@gmail.com> wrote:
>
> On Wed, Sep 28, 2022 at 11:44 AM Aleksander Alekseev
> <aleksander@timescale.com> wrote:
> > > I think, this patch was marked as "Waiting on Author", probably, by mistake. Since recent changes were done
withoutany significant code changes and CF bot how happy again. 
> > >
> > > I'm going to move it to RfC, could I? If not, please tell why.
> >
> > I restored the "Ready for Committer" state. I don't think it's a good
> > practice to change the state every time the patch has a slight
> > conflict or something. This is not helpful at all. Such things happen
> > quite regularly and typically are fixed in a couple of days.
>
> This patch seems useful to me.  I went through the thread, it seems
> that all the critics are addressed.
>
> I've rebased this patch.   Also, I've run perltidy for tests, split
> long errmsg() into errmsg(), errdetail() and errhint(), and do other
> minor enchantments.
>
> I think this patch is ready to go.  I'm going to push it if there are
> no objections.
>
> ------
> Regards,
> Alexander Korotkov

It's very good that this long-standing patch is finally committed. Thanks a lot!

Regards,
Pavel Borisov

On Mon, Oct 30, 2023 at 11:29:04AM +0400, Pavel Borisov wrote:
> On Wed, 25 Oct 2023 at 00:13, Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > I think this patch is ready to go.  I'm going to push it if there are
> > no objections.

> It's very good that this long-standing patch is finally committed. Thanks a lot!

Agreed.  I gave this feature (commit 5ae2087) a try.  Thanks for implementing
it.  Could I get your input on two topics?


==== 1. Cross-page comparison at "first key on the next page" only

Cross-page comparisons got this discussion upthread:

On Tue, Mar 02, 2021 at 07:10:32PM -0800, Peter Geoghegan wrote:
> On Mon, Feb 8, 2021 at 2:46 AM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
> > Caveat: if the first entry on the next index page has a key equal to the key on a previous page AND all heap tid's
correspondingto this entry are invisible, currently cross-page check can not detect unique constraint violation between
previousindex page entry and 2nd, 3d and next current index page entries. In this case, there would be a message that
recommendsdoing VACUUM to remove the invisible entries from the index and repeat the check. (Generally, it is
recommendedto do vacuum before the check, but for the testing purpose I'd recommend turning it off to check the
detectionof visible-invisible-visible duplicates scenarios)
 

> You're going to have to "couple" buffer locks in the style of
> _bt_check_unique() (as well as keeping a buffer lock on "the first
> leaf page a duplicate might be on" throughout) if you need the test to
> work reliably.

The amcheck feature has no lock coupling at its "first key on the next page"
check.  I think that's fine, because amcheck takes one snapshot at the
beginning and looks for pairs of visible-to-that-snapshot heap tuples with the
same scan key.  _bt_check_unique(), unlike amcheck, must catch concurrent
inserts.  If amcheck "checkunique" wanted to detect duplicates that would
appear when all transactions commit, it would need lock coupling.  (I'm not
suggesting it do that.)  Do you see a problem with the lack of lock coupling
at "first key on the next page"?

> But why bother with that? The tool doesn't have to be
> 100% perfect at detecting corruption (nothing can be), and it's rather
> unlikely that it will matter for this test. A simple test that doesn't
> handle cross-page duplicates is still going to be very effective.

I agree, but perhaps the "first key on the next page" code is more complex
than general-case code would be.  If the lack of lock coupling is fine, then I
think memory context lifecycle is the only obstacle making index page
boundaries special.  Are there factors beyond that?  We already have
state->lowkey kept across pages via MemoryContextAlloc().  Similar lines of
code could preserve the scan key for checkunique, making the "first key on the
next page" code unnecessary.


==== 2. Raises runtime by 476% despite no dead tuples

I used the following to create a table larger than RAM, 17GB table and 10GB
index on a system with 12GB RAM:

\set count 500000000
begin;
set maintenance_work_mem = '1GB';
set client_min_messages = debug1;  -- debug2 is per-block spam
create temp table t as select n from generate_series(1,:count) t(n);
create unique index t_idx on t(n);
\dt+ t
\di+ t_idx
create extension amcheck;
select bt_index_check('t_idx', heapallindexed => false, checkunique => false);
select bt_index_check('t_idx', heapallindexed => false, checkunique => true);

Adding checkunique raised runtime from 58s to 276s, because it checks
visibility for every heap tuple.  It could do the heap fetch and visibility
check lazily, when the index yields two heap TIDs for one scan key.  That
should give zero visibility checks for this particular test case, and it
doesn't add visibility checks to bloated-table cases.  Pseudo-code:

    /*---
     * scan_key is the last uniqueness-relevant scan key observed as
     * bt_check_level_from_leftmost() moves right to traverse the leaf level.
     * Will be NULL if the next tuple can't be the second tuple of a
     * uniqueness violation, because any of the following apply:
     * - we're evaluating the first leaf tuple of the entire index
     * - last scan key had anynullkeys (never forms a uniqueness violation w/
     *   any other scan key)
     */
    scan_key = NULL;
    /*
     * scan_key_known_visible==true indicates that scan_key_heap_tid is the
     * last _visible_ heap TID observed for scan_key.  Otherwise,
     * scan_key_heap_tid is the last heap TID observed for scan_key, and we've
     * not yet checked its visibility.
     */
    bool scan_key_known_visible;
    scan_key_heap_tid;
    foreach itup (leftmost_leaf_level_tup .. rightmost_leaf_level_tup) {
        if (itup.anynullkeys)
            scan_key = NULL;
        else if (scan_key != NULL &&
                 _bt_compare(scan_key, itup.key) == 0 &&
                 (scan_key_known_visible ||
                  (scan_key_known_visible = visible(scan_key_heap_tid))))
        {
            if (visible(itup.tid))
                elog(ERROR, "duplicate in unique index");
        }
        else
        {
            /*
             * No prior uniqueness-relevant key, or key changed, or we just
             * learned scan_key_heap_tid was invisible.  Make itup the
             * standard by which we judge future index tuples as we move
             * right.
             */
            scan_key = itup.key;
            scan_key_known_visible = false;
            scan_key_heap_tid = itup.tid;
        }
    }

On Sun, Mar 24, 2024 at 10:03 PM Noah Misch <noah@leadboat.com> wrote:
> > You're going to have to "couple" buffer locks in the style of
> > _bt_check_unique() (as well as keeping a buffer lock on "the first
> > leaf page a duplicate might be on" throughout) if you need the test to
> > work reliably.
>
> The amcheck feature has no lock coupling at its "first key on the next page"
> check.  I think that's fine, because amcheck takes one snapshot at the
> beginning and looks for pairs of visible-to-that-snapshot heap tuples with the
> same scan key.  _bt_check_unique(), unlike amcheck, must catch concurrent
> inserts.  If amcheck "checkunique" wanted to detect duplicates that would
> appear when all transactions commit, it would need lock coupling.  (I'm not
> suggesting it do that.)  Do you see a problem with the lack of lock coupling
> at "first key on the next page"?

Practically speaking, no, I see no problems.

> I agree, but perhaps the "first key on the next page" code is more complex
> than general-case code would be.  If the lack of lock coupling is fine, then I
> think memory context lifecycle is the only obstacle making index page
> boundaries special.  Are there factors beyond that?

I believe that my concern back in 2021 was that the general complexity
of cross-page checking was unlikely to be worth it. Note that
nbtsplitloc.c is *maximally* aggressive about avoiding split points
that fall within some group of duplicates, so with a unique index it
should be very rare.

Admittedly, I was probably thinking about the complexity of adding a
bunch of code just to be able to check uniqueness across page
boundaries. I did mention lock coupling by name, but that was more of
a catch-all term for the problems in this area.

> We already have
> state->lowkey kept across pages via MemoryContextAlloc().  Similar lines of
> code could preserve the scan key for checkunique, making the "first key on the
> next page" code unnecessary.

I suspect that I was overly focussed on the index structure itself
back when I made these remarks. I might not have considered that just
using an MVCC snapshot for the TIDs makes the whole process safe,
though that now seems quite obvious.

Separately, I now see that the committed patch just reuses the code
that has long been used to check that things are in the correct order
across page boundaries: this is the bt_right_page_check_scankey check,
which existed in the very earliest versions of amcheck. So while I
agree that we could just keep the original scan key (from the last
item on every leaf page), and then make the check at the start of the
next page instead (as opposed to making it at the end of the previous
leaf page, which is how it works now), it's not obvious that that
would be a good trade-off, all things considered.

It might still be a little better that way around, overall, but you're
not just talking about changing the recently committed checkunique
patch (I think). You're also talking about restructuring the long
established bt_right_page_check_scankey check (otherwise, what's the
point?). I'm not categorically opposed to that, but it's not as if
it'll allow you to throw out a bunch of code -- AFAICT that proposal
doesn't have that clear advantage going for it. The race condition
that is described at great length in bt_right_page_check_scankey isn't
ever going to be a problem for the recently committed checkunique
patch (as you more or less pointed out yourself), but obviously it is
still a concern for the cross-page order check.

In summary, the old bt_right_page_check_scankey check is strictly
concerned with the consistency of a physical data structure (the index
itself), whereas the new checkunique check makes sure that the logical
content of the database is consistent (the index, the heap, and all
associated transaction status metadata have to be consistent). That
means that the concerns that are described at length in
bt_right_page_check_scankey (nor anything like those concerns) don't
apply to the new checkunique check. We agree on all that, I think. But
it's less clear that that presents us with an opportunity to simplify
this patch.

> Adding checkunique raised runtime from 58s to 276s, because it checks
> visibility for every heap tuple.  It could do the heap fetch and visibility
> check lazily, when the index yields two heap TIDs for one scan key.  That
> should give zero visibility checks for this particular test case, and it
> doesn't add visibility checks to bloated-table cases.

The added runtime that you report seems quite excessive to me. I'm
really surprised that the code doesn't manage to avoid visibility
checks in the absence of duplicates that might both have TIDs
considered visible. Lazy visibility checking seems almost essential,
and not just a nice-to-have optimization.

It seems like the implication of everything that you said about
refactoring/moving the check was that doing so would enable this
optimization (at least an implementation along the lines of your
pseudo code). If that was what you intended, then it's not obvious to
me why it is relevant. What, if anything, does it have to do with
making the new checkunique visibility checks happen lazily?

--
Peter Geoghegan

On Mon, Mar 25, 2024 at 12:03:10PM -0400, Peter Geoghegan wrote:
> On Sun, Mar 24, 2024 at 10:03 PM Noah Misch <noah@leadboat.com> wrote:

> Separately, I now see that the committed patch just reuses the code
> that has long been used to check that things are in the correct order
> across page boundaries: this is the bt_right_page_check_scankey check,
> which existed in the very earliest versions of amcheck. So while I
> agree that we could just keep the original scan key (from the last
> item on every leaf page), and then make the check at the start of the
> next page instead (as opposed to making it at the end of the previous
> leaf page, which is how it works now), it's not obvious that that
> would be a good trade-off, all things considered.
> 
> It might still be a little better that way around, overall, but you're
> not just talking about changing the recently committed checkunique
> patch (I think). You're also talking about restructuring the long
> established bt_right_page_check_scankey check (otherwise, what's the
> point?). I'm not categorically opposed to that, but it's not as if

I wasn't thinking about changing the pre-v17 bt_right_page_check_scankey()
code.  I got interested in this area when I saw the interaction of the new
"first key on the next page" logic with bt_right_page_check_scankey().  The
patch made bt_right_page_check_scankey() pass back rightfirstoffset.  The new
code then does palloc_btree_page() and PageGetItem() with that offset, which
bt_right_page_check_scankey() had already done.  That smelled like a misplaced
distribution of responsibility.  For a time, I suspected the new code should
move down into bt_right_page_check_scankey().  Then I transitioned to thinking
checkunique didn't need new code for the page boundary.

> it'll allow you to throw out a bunch of code -- AFAICT that proposal
> doesn't have that clear advantage going for it. The race condition
> that is described at great length in bt_right_page_check_scankey isn't
> ever going to be a problem for the recently committed checkunique
> patch (as you more or less pointed out yourself), but obviously it is
> still a concern for the cross-page order check.
> 
> In summary, the old bt_right_page_check_scankey check is strictly
> concerned with the consistency of a physical data structure (the index
> itself), whereas the new checkunique check makes sure that the logical
> content of the database is consistent (the index, the heap, and all
> associated transaction status metadata have to be consistent). That
> means that the concerns that are described at length in
> bt_right_page_check_scankey (nor anything like those concerns) don't
> apply to the new checkunique check. We agree on all that, I think. But
> it's less clear that that presents us with an opportunity to simplify
> this patch.

See above for why I anticipated a simplification opportunity with respect to
new-in-v17 code.  Still, it may not pan out.

> > Adding checkunique raised runtime from 58s to 276s, because it checks

Side note: my last email incorrectly described that as "raises runtime by
476%".  It should have said "by 376%" or "by a factor of 4.76".

> > visibility for every heap tuple.  It could do the heap fetch and visibility
> > check lazily, when the index yields two heap TIDs for one scan key.  That
> > should give zero visibility checks for this particular test case, and it
> > doesn't add visibility checks to bloated-table cases.

> It seems like the implication of everything that you said about
> refactoring/moving the check was that doing so would enable this
> optimization (at least an implementation along the lines of your
> pseudo code). If that was what you intended, then it's not obvious to
> me why it is relevant. What, if anything, does it have to do with
> making the new checkunique visibility checks happen lazily?

Their connection is just being the two big-picture topics I found in
post-commit review.  Decisions about the cross-page check are indeed separable
from decisions about lazy vs. eager visibility checks.

Thanks,
nm

On Mon, Mar 25, 2024 at 2:24 PM Noah Misch <noah@leadboat.com> wrote:
> I wasn't thinking about changing the pre-v17 bt_right_page_check_scankey()
> code.  I got interested in this area when I saw the interaction of the new
> "first key on the next page" logic with bt_right_page_check_scankey().  The
> patch made bt_right_page_check_scankey() pass back rightfirstoffset.  The new
> code then does palloc_btree_page() and PageGetItem() with that offset, which
> bt_right_page_check_scankey() had already done.  That smelled like a misplaced
> distribution of responsibility.  For a time, I suspected the new code should
> move down into bt_right_page_check_scankey().  Then I transitioned to thinking
> checkunique didn't need new code for the page boundary.

Ah, I see. Somehow I missed this point when I recently took a fresh
look at the committed patch.

 I did notice (I meant to point out) that I have concerns about this
part of the new uniqueness check code:

"
if (P_IGNORE(topaque) || !P_ISLEAF(topaque))
    break;
"

My concern here is with the !P_ISLEAF(topaque) test -- it shouldn't be
required. If the page in question isn't a leaf page, then the index
must be corrupt (or the page deletion recycle safety/drain technique
thing is buggy). The " !P_ISLEAF(topaque)" part of the check is either
superfluous or something that ought to be reported as corruption --
it's not a legal/expected state.

Separately, I dislike the way the target block changes within
bt_target_page_check(). The general idea behind verify_nbtree.c's
target block is that every block becomes the target exactly once, in a
clearly defined place. All corruption (in the index structure itself)
is formally considered to be a problem with that particular target
block. I want to be able to clearly distinguish between the target and
target's right sibling here, to explain my concerns, but they're kinda
both the target, so that's a lot harder than it should be. (Admittedly
directly blaming the target block has always been a little bit
arbitrary, at least in certain cases, but even there it provides
structure that makes things much easier to describe unambiguously.)

--
Peter Geoghegan

On Fri, Mar 29, 2024 at 02:17:08PM -0400, Peter Geoghegan wrote:
> On Mon, Mar 25, 2024 at 2:24 PM Noah Misch <noah@leadboat.com> wrote:
> > I wasn't thinking about changing the pre-v17 bt_right_page_check_scankey()
> > code.  I got interested in this area when I saw the interaction of the new
> > "first key on the next page" logic with bt_right_page_check_scankey().  The
> > patch made bt_right_page_check_scankey() pass back rightfirstoffset.  The new
> > code then does palloc_btree_page() and PageGetItem() with that offset, which
> > bt_right_page_check_scankey() had already done.  That smelled like a misplaced
> > distribution of responsibility.  For a time, I suspected the new code should
> > move down into bt_right_page_check_scankey().  Then I transitioned to thinking
> > checkunique didn't need new code for the page boundary.

>  I did notice (I meant to point out) that I have concerns about this
> part of the new uniqueness check code:
> 
> "
> if (P_IGNORE(topaque) || !P_ISLEAF(topaque))
>     break;
> "
> 
> My concern here is with the !P_ISLEAF(topaque) test -- it shouldn't be
> required. If the page in question isn't a leaf page, then the index
> must be corrupt (or the page deletion recycle safety/drain technique
> thing is buggy). The " !P_ISLEAF(topaque)" part of the check is either
> superfluous or something that ought to be reported as corruption --
> it's not a legal/expected state.

Good point.

> Separately, I dislike the way the target block changes within
> bt_target_page_check(). The general idea behind verify_nbtree.c's
> target block is that every block becomes the target exactly once, in a
> clearly defined place.

Agreed.

On 24.10.23 22:13, Alexander Korotkov wrote:
> On Wed, Sep 28, 2022 at 11:44 AM Aleksander Alekseev
> <aleksander@timescale.com> wrote:
>>> I think, this patch was marked as "Waiting on Author", probably, by mistake. Since recent changes were done without
anysignificant code changes and CF bot how happy again.
 
>>>
>>> I'm going to move it to RfC, could I? If not, please tell why.
>>
>> I restored the "Ready for Committer" state. I don't think it's a good
>> practice to change the state every time the patch has a slight
>> conflict or something. This is not helpful at all. Such things happen
>> quite regularly and typically are fixed in a couple of days.
> 
> This patch seems useful to me.  I went through the thread, it seems
> that all the critics are addressed.
> 
> I've rebased this patch.   Also, I've run perltidy for tests, split
> long errmsg() into errmsg(), errdetail() and errhint(), and do other
> minor enchantments.
> 
> I think this patch is ready to go.  I'm going to push it if there are
> no objections.

I just found the new pg_amcheck option --checkunique in PG17-to-be. 
Could we rename this to --check-unique?  Seems friendlier.  Maybe also 
rename the bt_index_check function argument to check_unique.

On Wed, Apr 17, 2024 at 6:41 PM Pavel Borisov <pashkin.elfe@gmail.com> wrote:
>>  I did notice (I meant to point out) that I have concerns about this
>> part of the new uniqueness check code:
>> "
>> if (P_IGNORE(topaque) || !P_ISLEAF(topaque))
>>     break;
>> "
>>
>> My concern here is with the !P_ISLEAF(topaque) test -- it shouldn't be
>> required
>
> I agree. But I didn't see the need to check uniqueness constraints violations in internal pages. Furthermore, it
doesn'tmean only a violation of constraint, but a major index corruption. I agree that checking and reporting this type
ofcorruption separately is a possible thing. 

I think we could just throw an error in case of an unexpected internal
page.  It doesn't seem reasonable to continue the check with this type
of corruption detected.  If the tree linkage is corrupted we may enter
an endless loop or something.

>> Separately, I dislike the way the target block changes within
>> bt_target_page_check(). The general idea behind verify_nbtree.c's
>> target block is that every block becomes the target exactly once, in a
>> clearly defined place. All corruption (in the index structure itself)
>> is formally considered to be a problem with that particular target
>> block. I want to be able to clearly distinguish between the target and
>> target's right sibling here, to explain my concerns, but they're kinda
>> both the target, so that's a lot harder than it should be. (Admittedly
>> directly blaming the target block has always been a little bit
>> arbitrary, at least in certain cases, but even there it provides
>> structure that makes things much easier to describe unambiguously.)
>
> The possible way to load the target block only once is to get rid of the cross-page uniqueness violation check. I
introducedit to catch more possible cases of uniqueness violations. Though they are expected to be extremely rare, and
anywaythe algorithm doesn't get any warranty, just does its best to catch what is possible. I don't object to this
change.

I think we could probably just avoid setting state->target during
cross-page check.  Just save that into a local variable and pass as an
argument where needed.

Skipping the visibility checks for "only one tuple for scan key" case
looks like very valuable optimization [1].

I also think we should wrap lVis_* variables into struct.  That would
make the way we pass them to functions more elegant.

Links.
1. https://www.postgresql.org/message-id/20240325020323.fd.nmisch%40google.com

------
Regards,
Alexander Korotkov

On Wed, Apr 17, 2024 at 9:38 AM Peter Eisentraut <peter@eisentraut.org> wrote:
> On 24.10.23 22:13, Alexander Korotkov wrote:
> > On Wed, Sep 28, 2022 at 11:44 AM Aleksander Alekseev
> > <aleksander@timescale.com> wrote:
> >>> I think, this patch was marked as "Waiting on Author", probably, by mistake. Since recent changes were done
withoutany significant code changes and CF bot how happy again. 
> >>>
> >>> I'm going to move it to RfC, could I? If not, please tell why.
> >>
> >> I restored the "Ready for Committer" state. I don't think it's a good
> >> practice to change the state every time the patch has a slight
> >> conflict or something. This is not helpful at all. Such things happen
> >> quite regularly and typically are fixed in a couple of days.
> >
> > This patch seems useful to me.  I went through the thread, it seems
> > that all the critics are addressed.
> >
> > I've rebased this patch.   Also, I've run perltidy for tests, split
> > long errmsg() into errmsg(), errdetail() and errhint(), and do other
> > minor enchantments.
> >
> > I think this patch is ready to go.  I'm going to push it if there are
> > no objections.
>
> I just found the new pg_amcheck option --checkunique in PG17-to-be.
> Could we rename this to --check-unique?  Seems friendlier.  Maybe also
> rename the bt_index_check function argument to check_unique.

+1 from me
Let's do so if nobody objects.

------
Regards,
Alexander Korotkov

On Thu, Apr 25, 2024 at 04:59:54PM +0400, Pavel Borisov wrote:
> 0001: Optimize speed by avoiding heap visibility checking for different
> non-deduplicated index tuples as proposed by Noah Misch
> 
> Speed measurements on my laptop using the exact method recommended by Noah
> upthread:
> Current master branch: checkunique off: 144s, checkunique on: 419s
> With patch 0001: checkunique off: 141s, checkunique on: 171s

Where is the CPU time going to make it still be 21% slower w/ checkunique on?
It's a great improvement vs. current master, but I don't have an obvious
explanation for the remaining +21%.

Hi Noah,

On Wed, May 1, 2024 at 5:24 AM Noah Misch <noah@leadboat.com> wrote:
> On Thu, Apr 25, 2024 at 04:59:54PM +0400, Pavel Borisov wrote:
> > 0001: Optimize speed by avoiding heap visibility checking for different
> > non-deduplicated index tuples as proposed by Noah Misch
> >
> > Speed measurements on my laptop using the exact method recommended by Noah
> > upthread:
> > Current master branch: checkunique off: 144s, checkunique on: 419s
> > With patch 0001: checkunique off: 141s, checkunique on: 171s
>
> Where is the CPU time going to make it still be 21% slower w/ checkunique on?
> It's a great improvement vs. current master, but I don't have an obvious
> explanation for the remaining +21%.

I think there is at least extra index tuples comparison.

------
Regards,
Alexander Korotkov