Re: cannot move relocatable extension out of pg_catalog schema

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.

Peter Eisentraut <peter_e(at)gmx(dot)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

On 2/1/13 3:21 PM, Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)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.

Fair enough. It's not that important.

> I wonder whether it'd not be a better idea to forbid specifying
> pg_catalog as the target schema for relocatable extensions.

But that would be important, I think.

On Fri, Feb 1, 2013 at 5:13 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> I wonder whether it'd not be a better idea to forbid specifying
>> pg_catalog as the target schema for relocatable extensions.
>
> But that would be important, I think.

I understand the temptation to forbid pg_catalog as the target schema
for relocatable extensions, or indeed for object creation in general.
The fact that you can't, for example, go back and drop the objects
later is a real downer. On the other hand, from a user perspective,
it's really tempting to want to create certain extensions (adminpack,
for example) in such a way that they appear to be "part of the system"
rather than something that lives in a user schema. Had we some other
solution to that problem (a second schema that behaves like pg_catalog
but is empty by default and allows drops?) we might alleviate the need
to put stuff in pg_catalog per se.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/04/2013 02:16 AM, Robert Haas wrote:
> On Fri, Feb 1, 2013 at 5:13 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> I wonder whether it'd not be a better idea to forbid specifying
>>> pg_catalog as the target schema for relocatable extensions.
>> But that would be important, I think.
> I understand the temptation to forbid pg_catalog as the target schema
> for relocatable extensions, or indeed for object creation in general.
> The fact that you can't, for example, go back and drop the objects
> later is a real downer. On the other hand, from a user perspective,
> it's really tempting to want to create certain extensions (adminpack,
> for example) in such a way that they appear to be "part of the system"
> rather than something that lives in a user schema. Had we some other
> solution to that problem (a second schema that behaves like pg_catalog
> but is empty by default and allows drops?) we might alleviate the need
> to put stuff in pg_catalog per se.
+1

Having a standard schema for extensions (say pg_extensions) is
something I have wanted multiple times.

Hannu
>

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 1, 2013 at 5:13 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>>> I wonder whether it'd not be a better idea to forbid specifying
>>> pg_catalog as the target schema for relocatable extensions.

We should do that, yes. Rationale: it's only documenting an existing
restriction that you just explained we can't get rid of. Want me to send
a patch (tomorrow)?

> I understand the temptation to forbid pg_catalog as the target schema
> for relocatable extensions, or indeed for object creation in general.

Those two cases are not to be mixed.

> The fact that you can't, for example, go back and drop the objects
> later is a real downer. On the other hand, from a user perspective,
> it's really tempting to want to create certain extensions (adminpack,
> for example) in such a way that they appear to be "part of the system"
> rather than something that lives in a user schema. Had we some other

It's easy to do that in the extension's control properties:

relocatable = false
schema = pg_catalog

And the adminpack extension is already set that way. It's then part of
the system and you can still remove it. The only think you can not do is
move its objects in another schema, and I don't much see the point.

> solution to that problem (a second schema that behaves like pg_catalog
> but is empty by default and allows drops?) we might alleviate the need
> to put stuff in pg_catalog per se.

We had extensive talks about that when cooking the extension patch, and
that almost killed it. I think it took about a full year to get back on
our feet again. The only thing I know about that search_path can of
worms is that I will stay away from it as much as possible, and
wholeheartedly advice anyone to do the same.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support