From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: samfelder17@gmail.com
Subject: BUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried
Date: Thu, 08 May 2025 01:23:53 +0000
Message-ID: <18917-fea6ac4ccec4e24a@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, samfelder17@gmail.com
Subject: Re: BUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried
In-Reply-To: <18917-fea6ac4ccec4e24a@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox