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

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