BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: holly(dot)roberts(at)starlingbank(dot)com
Subject: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key
Date: 2022-02-16 14:38:55
Message-ID: 17409-52871dda8b5741cb@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17409
Logged by: Holly Roberts
Email address: holly(dot)roberts(at)starlingbank(dot)com
PostgreSQL version: 14.2
Operating system: Debian 10.2.1-6
Description:

When attempting to change the data type of a column that has previously been
clustered on, which is also referenced by a foreign key, then an exception
is thrown.

Reproduction steps using a fresh database:
CREATE TABLE parent (
parent_field INTEGER CONSTRAINT pk_parent PRIMARY KEY
);
CREATE TABLE child (
child_field INTEGER,
CONSTRAINT fk_child FOREIGN KEY (child_field) REFERENCES parent
(parent_field)
);
CLUSTER parent USING pk_parent;
ALTER TABLE parent ALTER COLUMN parent_field SET DATA TYPE BIGINT;

This throws the following error:
ERROR: relation 16458 has multiple clustered indexes
'SELECT 16458::regclass' returns 'parent';
This has previously worked on various versions of postgres 12 and 13 for me
(latest tried 13.6)

I have reproduced the following on both 14.2 and 14.0, my postgres version
is as follows:
SELECT version();
PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Some minor observations:
- Removing the FK allows the data type to be changed
- Occurs for both primary key/unique index
- Occurs on other data types (eg. going from TEXT to BIGINT with 'USING')

Looking through pg_catalog I can also see that only one index is clustered
as would be expected:
SELECT
table_cls.relnamespace::regnamespace::text AS schema,
table_cls.relname AS table,
index_cls.relname AS index,
indisclustered
FROM pg_index pi
INNER JOIN pg_class index_cls ON (pi.indexrelid = index_cls.oid)
INNER JOIN pg_class table_cls ON (pi.indrelid = table_cls.oid)
WHERE (table_cls.relnamespace::regnamespace::text, table_cls.relname) =
('public', 'parent');

schema | table | index | indisclustered
--------+--------+-----------+----------------
public | parent | pk_parent | t
(1 row)

Many Thanks,
Holly Roberts

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2022-02-16 15:12:38 Re: BUG #17407: trim trims more than expected
Previous Message David G. Johnston 2022-02-16 13:23:34 Re: BUG #17408: Duplicate aliases silently fail