BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key
От | PG Bug reporting form |
---|---|
Тема | BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key |
Дата | |
Msg-id | 17409-52871dda8b5741cb@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17409 Logged by: Holly Roberts Email address: holly.roberts@starlingbank.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
В списке pgsql-bugs по дате отправления: