The following bug has been logged on the website:
Bug reference: 18917
Logged by: sam felder
Email address: samfelder17@gmail.com
PostgreSQL version: 16.4
Operating system: PostgreSQL 16.4 on aarch64-unknown-linux-gnu, com
Description:
when trying to drop a foreign key constraint with an existing index that
hasn't been used, you need to make a query against constraint table to get
it working. see below for psql sequence of events
```
mydb=> begin;
BEGIN
mydb=*> CREATE INDEX IF NOT EXISTS "child_table_parent_id_idx" ON
"child_table"("parent_id");
CREATE INDEX
mydb=*> ALTER TABLE "child_table" DROP CONSTRAINT
"child_table_parent_id_fkey";
ERROR: column is not in index
mydb=!> rollback;
ROLLBACK
mydb=> begin;
BEGIN
mydb=*> CREATE INDEX IF NOT EXISTS "child_table_parent_id_idx" ON
"child_table"("parent_id");
CREATE INDEX
mydb=*> SELECT conname, contype, conrelid::regclass, confrelid::regclass,
pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'child_table'::regclass;
conname | contype | conrelid | confrelid |
pg_get_constraintdef
-------------------------------+---------+--------------+------------+-----------------------------------------------------------
child_table_pkey | p | child_table | - |
PRIMARY KEY (parent_id, version_id)
child_table_parent_id_fkey | f | child_table | parent_table |
FOREIGN KEY (parent_id) REFERENCES parent_table(id) ON UPDATE CASCADE
(2 rows)
mydb=*> ALTER TABLE "child_table" DROP CONSTRAINT
"child_table_parent_id_fkey";
ALTER TABLE
mydb=*>
```
I first recognized this because \d+ table_name in psql would fix the error,
so drilled into what queries it was making. The query fixing the problem
seems to be
```
mydb=*> SELECT conname, contype, conrelid::regclass, confrelid::regclass,
pg_get_constraintdef(oid)
FROM pg_constraint
```
This issue does not occur on
PostgreSQL 15.12 (Debian 15.12-1.pgdg120+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
I recognize i'm not on the latest 16.x version, but I couldn't afford to
upgrade just yet and it doesn't seem to be referenced in the release note
for higher versions
