On Sun, Jun 1, 2025, at 08:24, Joel Jacobson wrote: > On Sat, May 31, 2025, at 23:56, Tom Lane wrote: >> "Joel Jacobson" <j...@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
