| From: | Nikhil Sontakke <nikhil(at)planetscale(dot)com> |
|---|---|
| To: | Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> |
| Cc: | jian he <jian(dot)universality(at)gmail(dot)com>, lakshmi <lakshmigcdac(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, 金 <jinbinge(at)126(dot)com>, Kirill Reshke <reshkekirill(at)gmail(dot)com>, Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
| Subject: | Re: let ALTER TABLE DROP COLUMN drop whole-row referenced object |
| Date: | 2026-11-19 17:00:11 |
| Message-ID: | CA+UBoq238DdCfFDpvo1oNctwM9JrV=4jUAPz=yUAOrhDE3MwzQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
> Additionally, I don't think that dropping whole-row indexes is proper
> when the column is dropped or changes data type; the index definition
> is still correct, it just needs to be rebuilt. See my fix for this
> class of issues at [0] (cf [1], it implements the approach I
> described.
>
>
While working on [1], I found a whole-row case that belongs
here. On master, a unique index over a whole-row expression silently
stops enforcing uniqueness after DROP COLUMN:
CREATE TABLE t (a int, b int);
INSERT INTO t VALUES (1, 2), (1, 3);
CREATE UNIQUE INDEX t_uq ON t ((hash_record(t)));
ALTER TABLE t DROP COLUMN b;
-- both rows now hash alike, but the index has the old hashes
SET enable_seqscan = off;
SELECT count(*) FROM t WHERE hash_record(t) = hash_record(ROW(1)::t);
-- 0, while a sequential scan returns 2
RESET enable_seqscan;
INSERT INTO t VALUES (1); -- succeeds
REINDEX INDEX t_uq;
ERROR: could not create unique index "t_uq"
DETAIL: Key (hash_record(t.*))=(-1905060026) is duplicated.
A whole-row CHECK is left violated the same way:
CREATE TABLE c (a int, b int, CHECK (c::text <> '(1)'));
INSERT INTO c VALUES (1, 2);
ALTER TABLE c DROP COLUMN b; -- succeeds; the row is now (1)
I tried both proposed fixes on these:
- v14 (on master as of July 14; it no longer applies to HEAD): DROP
COLUMN silently drops t_uq and c_check, without CASCADE, and the
duplicate INSERT then succeeds.
- Matthias's v5 [2]: DROP COLUMN fails with the duplicate key error
above, and on c with "check constraint "c_check" of relation "c" is
violated by some row". A non-unique hash_record(t) index is rebuilt
and returns correct results.
For unique indexes and CHECK constraints, rebuilding or rechecking
seems preferable. Dropping matches what DROP COLUMN does for an index
on the dropped column itself, but a whole-row index or constraint
still covers the remaining columns, so dropping it quietly removes a
guarantee on data that is still there.
[1]
https://www.postgresql.org/message-id/flat/CA%2BUBoq0F2ua2fQEAU3-6w0oxOgFc9BgEN4gRcchiUUZ%2BWouVrQ%40mail.gmail.com
[2] https://commitfest.postgresql.org/patch/7071/
Regards,
Nikhil
---
Nikhil Sontakke
PlanetScale
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Geier | 2026-11-19 17:33:48 | Re: Use local buffer for packed numerics |
| Previous Message | Sami Imseih | 2026-11-19 16:54:35 | Re: [PATCH v1] Add pg_stat_progress overview view |