From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: exclusion@gmail.com
Subject: BUG #17872: Dropping an attribute of a composite type breaks indexes over the type silently
Date: Mon, 27 Mar 2023 10:00:01 +0000
Message-ID: <17872-d0fbb799dc3fd85d@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17872
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 22.04
Description:
The following script:
CREATE TYPE ctype AS (i int, j int);
CREATE TABLE ctbl(a int, cf ctype);
CREATE UNIQUE INDEX ctbl_idx ON ctbl(cf);
INSERT INTO ctbl VALUES (1, '(1, 2)'::ctype), (2, '(1, 1)'::ctype);
ALTER TYPE ctype DROP ATTRIBUTE j;
Results in the UNIQUE constraint broken:
SELECT ctid, * FROM ctbl;
ctid | a | cf
-------+---+-----
(0,1) | 1 | (1)
(0,2) | 2 | (1)
\d+ ctbl
Table "public.ctbl"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
a | integer | | | | plain |
| |
cf | ctype | | | | extended |
| |
Indexes:
"ctbl_idx" UNIQUE, btree (cf)
Access method: heap
And the index corruption detected by amcheck:
CREATE EXTENSION amcheck;
SELECT bt_index_parent_check(oid, true, true) FROM pg_class
WHERE relname = 'ctbl_idx';
ERROR: could not find tuple using search from root page in index
"ctbl_idx"
DETAIL: Index tid=(1,1) points to heap tid=(0,2) page lsn=0/187BF80.
(Interestingly enough, amcheck doesn't detect a corruption if
VALUES (1, '(1, 1)'::ctype), (2, '(1, 2)'::ctype) are inserted.)
The same is observed with a composite type representing a table row,
for example:
CREATE TABLE ijtbl(i int, j int);
CREATE TABLE ttbl(a int, cf ijtbl);
CREATE UNIQUE INDEX ttbl_idx ON ttbl(cf);
INSERT INTO ttbl VALUES (1, '(1, 2)'::ijtbl), (2, '(1, 1)'::ijtbl);
ALTER TABLE ijtbl DROP COLUMN j;
But if an index created over a set of ordinary columns, it is removed
automatically when a column in the set is dropped.
CREATE TABLE tbl(a int, i int, j int);
CREATE UNIQUE INDEX tbl_idx ON tbl(i, j);
INSERT INTO tbl VALUES (1, 1, 2), (2, 1, 1);
\d+ tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | |
|
i | integer | | | | plain | |
|
j | integer | | | | plain | |
|
Indexes:
"tbl_idx" UNIQUE, btree (i, j)
Access method: heap
ALTER TABLE tbl DROP COLUMN j;
\d+ tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default | Storage | Compression |
Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | |
|
i | integer | | | | plain | |
|
Access method: heap
As I can see, in this case pg_depend contains dependencies of the index
on specific table columns, but for composite types there exists only a
dependency on a composite type as a whole.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, exclusion@gmail.com
Subject: Re: BUG #17872: Dropping an attribute of a composite type breaks indexes over the type silently
In-Reply-To: <17872-d0fbb799dc3fd85d@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox