Thread

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

   PG Bug reporting form <noreply@postgresql.org> — 2025-05-08T01:23:53Z

   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
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-08T01:50:34Z

   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