pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried
2+ messages / 2 participants
[nested] [flat]
* BUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried
@ 2025-05-08 01:23 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2025-05-08 01:23 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: samfelder17@gmail.com
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18917: "Foreign key constraint drop fails with 'column is not in index' unless pg_constraint is queried
@ 2025-05-08 01:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-05-08 01:50 UTC (permalink / raw)
To: samfelder17@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-05-08 01:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-08 01:23 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-08 01:50 ` Tom Lane <tgl@sss.pgh.pa.us>
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