From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: nik@postgres.ai
Subject: BUG #18244: Corruption in indexes involving whole-row expressions
Date: Tue, 12 Dec 2023 19:28:29 +0000
Message-ID: <18244-8a7897de2acd5a08@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18244
Logged by: Nikolay Samokhvalov
Email address: nik@postgres.ai
PostgreSQL version: 16.1
Operating system: any
Description:
nik=# create index on t1 (hash_record(t1));
CREATE INDEX
Such an index can easily be corrupted, e.g.:
nik=# alter table t1 add column yo int default -1;
ALTER TABLE
Or if we just drop a column:
nik=# alter table t_n drop column c2;
ALTER TABLE
nik=# \d t_n
Table "public.t_n"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | integer | | |
Indexes:
"t_n_hash_record_idx" btree (hash_record(t_n.*))
nik=# select * from t_n where hash_record(t_n) = hash_record(row(1, -1));
c1
----
1
(1 row)
nik=# select * from t_n where hash_record(t_n) = hash_record(row(1));
c1
----
(0 rows)
Or if index is unique:
nik=# select * from t_n;
c4
----
-1
-1
-1
(3 rows)
nik=# \d t_n
Table "public.t_n"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------
c4 | integer | | | '-1'::integer
Indexes:
"t_n_hash_record_idx" UNIQUE, btree (hash_record(t_n.*))
nik=# reindex index t_n_hash_record_idx;
ERROR: could not create unique index "t_n_hash_record_idx"
DETAIL: Key (hash_record(t_n.*))=(385747274) is duplicated.
Proposal: prohibit the use of whole-row expression – as it is already done
for generated columns and produce a similar error ("cannot use whole-row
variable in column generation expression")
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, nik@postgres.ai
Subject: Re: BUG #18244: Corruption in indexes involving whole-row expressions
In-Reply-To: <18244-8a7897de2acd5a08@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