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

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