Thread: Bug with specific-schema extensions

This happens on 9.4.1 and HEAD.

The variant extension is locked to a specific schema by it's control file:
decibel@decina:[23:53]~/git/variant (master %=)$cat variant.control
# variant extension
comment = 'Variant data type for PostgreSQL'
default_version = '1.0.0-beta3'
relocatable = false
schema = 'variant'

CREATE EXTENSION creates the variant schema for me, but it leaves it 
behind when I drop the extension. I assume this is a bug and not by design?

decibel@decina.attlocal=# \dn List of schemas  Name  |  Owner
--------+--------- public | decibel
(1 row)

decibel@decina.attlocal=# BEGIN;
BEGIN
decibel@decina.attlocal=#* CREATE EXTENSION variant;
CREATE EXTENSION
decibel@decina.attlocal=#* \dn  List of schemas   Name   |  Owner
----------+--------- _variant | decibel public   | decibel variant  | decibel
(3 rows)

decibel@decina.attlocal=#* DROP EXTENSION variant;
DROP EXTENSION
decibel@decina.attlocal=#* \dn  List of schemas  Name   |  Owner
---------+--------- public  | decibel variant | decibel
(2 rows)

decibel@decina.attlocal=#* DROP SCHEMA variant ;
DROP SCHEMA
decibel@decina.attlocal=#* \q
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> CREATE EXTENSION creates the variant schema for me, but it leaves it 
> behind when I drop the extension. I assume this is a bug and not by design?

No, it's intentional.  If you want the schema to be considered part of the
extension, then create it within the extension.

We could have made this case fail, rather than auto-create the containing
schema.  That would have been more consistent but probably less useful,
so we didn't.
        regards, tom lane

On 4/19/15 5:08 PM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> CREATE EXTENSION creates the variant schema for me, but it leaves it
>> behind when I drop the extension. I assume this is a bug and not by design?
>
> No, it's intentional.  If you want the schema to be considered part of the
> extension, then create it within the extension.

Throws an exception since the schema now exists (see below).

> We could have made this case fail, rather than auto-create the containing
> schema.  That would have been more consistent but probably less useful,
> so we didn't.

It seems like a POLA violation. You start with no schema, CREATE 
EXTENSION, DROP EXTENSION, and magically there's this schema left over.

If we're going to magically create the schema (which at least for a 
fully non-relocatable extension is fine), then I think we should also 
mark the schema as being part of the extension. I'd even argue this is a 
bug and should be backpatched, but if people object to that we should at 
least change the behavior going forward.

As a refresher, the docs say this about fully non-relocatable extensions:

"If the extension does not support relocation at all, set relocatable = 
false in its control file, and also set schema to the name of the 
intended target schema. This will prevent use of the SCHEMA option of 
CREATE EXTENSION, unless it specifies the same schema named in the 
control file. This choice is typically necessary if the extension 
contains internal assumptions about schema names that can't be replaced 
by uses of @extschema@. The @extschema@ substitution mechanism is 
available in this case too, although it is of limited use since the 
schema name is determined by the control file."
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@bluetreble.com> writes:
> If we're going to magically create the schema (which at least for a 
> fully non-relocatable extension is fine), then I think we should also 
> mark the schema as being part of the extension.

If we do that, what happens to other objects that were added to the
containing schema, sometime after creation of the extension?  Surely those
should not become dependent on the extension.

> I'd even argue this is a 
> bug and should be backpatched, but if people object to that we should at 
> least change the behavior going forward.

I disagree that this is a bug at all, much less that we should back-patch
a behavioral change here.

> As a refresher, the docs say this about fully non-relocatable extensions:

As a refresher, the docs also say this about the target schema for an
extension, about two paras down from where you quoted:

"The target schema is determined by the schema parameter in the control
file if that is given, otherwise by the SCHEMA option of CREATE EXTENSION
if that is given, otherwise the current default object creation schema
(the first one in the caller's search_path). When the control file schema
parameter is used, the target schema will be created if it doesn't already
exist, but in the other two cases it must already exist."

I do not recall the full reasoning for this inconsistency in behavior,
and since I'm on vacation I don't plan to go looking in the archives to
find it.  But this behavior was most certainly explicitly discussed and
agreed to when we put in CREATE EXTENSION.  If you want to lobby to change
it, you'd better look up that discussion and refute the arguments therein.
Simply asserting your opinion that a four-year-old behavior is wrong is
not going to get you anywhere.
        regards, tom lane