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
Attachments:
fix-pg_constraint.txttext/plain; name=fix-pg_constraint.txtDownload
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 2d5ac1ea813..59ae9befff4 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -28,6 +28,7 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
+#include "commands/extension.h"
#include "common/int.h"
#include "utils/array.h"
#include "utils/builtins.h"
@@ -393,6 +394,13 @@ CreateConstraintEntry(const char *constraintName,
InvokeObjectPostCreateHookArg(ConstraintRelationId, conOid, 0,
is_internal);
+ /*
+ * If the constraint is being created as part of an extension script,
+ * mark it as a member of the extension.
+ */
+ if (!is_internal)
+ recordDependencyOnCurrentExtension(&conobject, false);
+
return conOid;
}
"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?
(If it did create them, the indirect dependency seems sufficient.)
regards, tom lane
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
On Sun, Jun 1, 2025, at 18:04, Tom Lane wrote:
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.
Thanks for the detailed explanation.
I underestimated the complexity involved in modifying pg_dump to handle this
case, and given other cowboy cases would still remain, I see why it's not worth
a core fix just for this single case. This reinforces my thinking that there's
high value in improving tooling in this area outside of core PostgreSQL.
I suggest a minimal change to the docs, to clarify that manually-added
subsidiary objects on extension tables will not be preserved by pg_dump since
they're not explicitly tracked as extension members.
Attached is a proposed improvement for the documentation.
/Joel
Attachments:
doc-extension-subsidiary-note.patchapplication/octet-stream; name="=?UTF-8?Q?doc-extension-subsidiary-note.patch?="Download
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 63c5ec6d1eb..997b98db7bc 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -610,7 +610,12 @@ RETURNS anycompatible AS ...
such objects, if it does so they will not be tracked as part of the
extension.) Also notice that while a table can be a member of an
extension, its subsidiary objects such as indexes are not directly
- considered members of the extension.
+ considered members of the extension. Consequently, such objects added
+ manually to extension tables will not be preserved by
+ <application>pg_dump</application>.
+ </para>
+
+ <para>
Another important point is that schemas can belong to extensions, but not
vice versa: an extension as such has an unqualified name and does not
exist <quote>within</quote> any schema. The extension's member objects,
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index d7595a7e546..d63c5d4ec1c 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1671,6 +1671,11 @@ CREATE DATABASE foo WITH TEMPLATE template0;
</programlisting>
</para>
+ <para>
+ Objects manually added to extension tables (such as indexes or constraints)
+ are not preserved by <application>pg_dump</application>.
+ </para>
+
<para>
When a dump without schema is chosen and the option <option>--disable-triggers</option>
is used, <application>pg_dump</application> emits commands