Thread: BUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried

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

PG Bug reporting form <noreply@postgresql.org> writes:
> 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
> ... etc ...

This test case is incomplete on its face. (How was the table created?
Where did "child_table_parent_id_fkey" come from?)  So it's hard to
tell what's going on, much less fix it.  Could we have a complete
reproducer starting from an empty database?

            regards, tom lane