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

> On Feb 8, 2021, at 2:46 AM, Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)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