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

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

On Sat, May 31, 2025, at 23:56, Tom Lane wrote:
> "Joel Jacobson" <joel@compiler.org> writes:
>> Foreign key constraints created during CREATE EXTENSION
>> lack a pg_depend entry with deptype='e'.
>
> Why would it be a sensible thing for an extension to create a
> foreign-key constraint on table(s) that it didn't itself create?

That wouldn't make any sense, no.

> (If it did create them, the indirect dependency seems sufficient.)

The indirect dependency might seem sufficient initially but becomes problematic
when needing to distinguish between objects originally created by an extension
and those manually added later by users.

Consider this scenario:

1. A company modularizes its system using extensions. Each extension defines
   tables, indexes, and constraints necessary for its operation.

2. During an emergency (e.g., slow queries or high load), a DBA quickly adds
   an index directly to an extension-defined table as a temporary fix but
   neglects to document this change in the extension upgrade scripts.

3. Later, during a PostgreSQL upgrade using pg_dump / pg_restore:

   - Extension objects are correctly skipped by pg_dump.
   - However, the manually-added index, indistinguishable from extension-created
     indexes due to the lack of explicit deptype='e' marking,
     is also unintentionally skipped.
   - Consequently, the manually-added index is silently lost.

Currently, neither indexes nor constraints have explicit deptype='e' entries.
To reliably detect manually-added objects, one would have to:

- Install the extension in a clean database.
- Query all indirect dependencies in both the clean and production databases.
- Perform a diff between these datasets to identify discrepancies.

This approach seems cumbersome and impractical in production environments.

Explicitly adding deptype='e' entries for all objects created during
CREATE EXTENSION would significantly improve this situation.
With this enhancement, pg_dump could easily detect and warn users about
manually-added objects, or even dump these objects as comments for manual review.

Even if pg_dump itself isn't improved, users would at least be able to write a
straightforward query to identify such objects in production environments.

While manually adding objects to extension-defined tables is of course bad
practice, I think it would help DBAs if they at least had a simple way to
detect such a situation.

/Joel

On Sun, Jun 1, 2025, at 08:24, Joel Jacobson wrote:
> On Sat, May 31, 2025, at 23:56, Tom Lane wrote:
>> "Joel Jacobson" <joel@compiler.org> writes:
>>> Foreign key constraints created during CREATE EXTENSION
>>> lack a pg_depend entry with deptype='e'.
>>
>> Why would it be a sensible thing for an extension to create a
>> foreign-key constraint on table(s) that it didn't itself create?
>
> That wouldn't make any sense, no.
>
>> (If it did create them, the indirect dependency seems sufficient.)
>
> The indirect dependency might seem sufficient initially but becomes problematic
> when needing to distinguish between objects originally created by an extension
> and those manually added later by users.
...
> Explicitly adding deptype='e' entries for all objects created during
> CREATE EXTENSION would significantly improve this situation.
> With this enhancement, pg_dump could easily detect and warn users about
> manually-added objects, or even dump these objects as comments for 
> manual review.

Upon further reflection, perhaps we can address this issue more cleanly without
resorting to workarounds like warnings or comments.

Given that pg_dump already emits CREATE EXTENSION commands in dump files,
if we enhanced pg_depend to track all object classes associated with extensions
explicitly, we could just emit commands to recreate any manually-added objects
immediately after the corresponding CREATE EXTENSION commands in the dump file.

This would ensure that manually-added extension objects, regardless of the reason for
their creation, would be reliably preserved and not risk being lost.

/Joel

"Joel Jacobson" <joel@compiler.org> writes:
> Explicitly adding deptype='e' entries for all objects created during
> CREATE EXTENSION would significantly improve this situation.
> With this enhancement, pg_dump could easily detect and warn users about
> manually-added objects, or even dump these objects as comments for manual review.

I'm pretty skeptical that this situation justifies the amount of
pg_depend bloat that you're suggesting.  I also don't think it'd be
easy or cheap for pg_dump to detect objects that should be dumped
because they lack an 'e' dependency but depend on objects that do
have one.  Normally, because extension member objects aren't dumped,
pg_dump doesn't even collect info on their indexes etc.

In short, it seems like quite a lot of work and quite a lot of
overhead (paid by everybody) to accommodate somebody abusing
extensions in one very specific way.  There are a lot of scenarios
in which a cowboy DBA can cause the database contents to differ
from what the extension scripts say, and most of them would not
be helped by what you're suggesting.

            regards, tom lane