Horton

Re: [PATCH] Improve amcheck to also check UNIQUE constraint in btree index.

Mark Dilger <mark.dilger@enterprisedb.com>

View thread
From: Mark Dilger <mark.dilger@enterprisedb.com>
To: Pavel Borisov <pashkin.elfe@gmail.com>, Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Date: 2021-02-08T21:46:50Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. amcheck: Optimize speed of checking for unique constraint violation

  2. amcheck: Report an error when the next page to a leaf is not a leaf

  3. amcheck: Don't load the right sibling page into BtreeCheckState

  4. amcheck: Refactoring the storage of the last visible entry

  5. Teach contrib/amcheck to check the unique constraint violation

  6. Add macros in hash and btree AMs to get the special area of their pages


> 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 a protocol 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