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

The Post Office <noreply@postgresql.org>

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: holly.roberts@starlingbank.com
Date: 2022-02-16T14:38:55Z
Lists: 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

Commits

  1. Restore the previous semantics of get_constraint_index().

  2. Change get_constraint_index() to use pg_constraint.conindid