Missing pg_depend entries for constraints created by extensions (deptype 'e')

From: "Joel Jacobson" <joel(at)compiler(dot)org>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Missing pg_depend entries for constraints created by extensions (deptype 'e')
Date: 2025-05-31 21:10:53
Message-ID: c59f4393-bcdf-4336-8309-d9de7cf14209@app.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

Foreign key constraints created during CREATE EXTENSION
lack a pg_depend entry with deptype='e'.

Looking at the code, I found that CreateConstraintEntry() in
src/backend/catalog/pg_constraint.c does not call
recordDependencyOnCurrentExtension(), unlike most other CREATE functions.

This seems to affect all constraint types created through CreateConstraintEntry.

I stumbled upon this issue when trying to query all database objects
belonging to an extension using:
SELECT * FROM pg_depend WHERE deptype = 'e' AND refobjid = <ext_oid> AND refclassid = 'pg_extension'::regclass

Is this omission intentional? I couldn't find any documentation or code
comments explaining why constraints should not be extension members.

Currently, it seems impossible to distinguish between:
- Constraints created by CREATE EXTENSION
- Constraints added manually to extension tables after extension creation

Would it make sense to add recordDependencyOnCurrentExtension() to
CreateConstraintEntry()? Or is there a specific reason why constraints
should be handled differently from other extension objects?

/Joel

Attachment Content-Type Size
fix-pg_constraint.txt text/plain 825 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2025-05-31 21:56:12 Re: Missing pg_depend entries for constraints created by extensions (deptype 'e')
Previous Message Tom Lane 2025-05-31 20:33:56 Confused coding in PLy_traceback()