Peter Eisentraut <peter_e@gmx.net> writes:
> create extension hstore with schema pg_catalog;
> alter extension hstore set schema public;
> ERROR: 0A000: cannot remove dependency on schema pg_catalog because it
> is a system object
> drop extension hstore; -- works
> I've seen this happen cleaning up after mistakenly misplaced extensions.
> I suspect this is a bug.
It's not a bug, it's an intentional implementation restriction that
would be quite expensive to remove.
The reason it fails is that we don't record dependencies on system
objects, and therefore there's no way for ALTER EXTENSION to modify
those dependencies when trying to do SET SCHEMA. That is, since
pg_catalog is pinned, we don't have any explicit record of which
objects in the extension would've needed dependencies on it, thus
no way to manufacture the dependencies on schema public that would
need to exist after the SET SCHEMA.
AFAICS the only maintainable fix would be to start storing dependencies
on pinned objects explicitly, which would make for enormous and 99.99%
useless bloat in pg_depend.
I wonder whether it'd not be a better idea to forbid specifying
pg_catalog as the target schema for relocatable extensions.
regards, tom lane
