Extension relocation vs. schema qualification

On Wed, Apr 04, 2018 at 11:59:57PM -0700, Noah Misch wrote:
> On Mon, Apr 02, 2018 at 04:24:02PM -0400, Bruce Momjian wrote:
> > > I am not sure we can fix this without requiring people to drop and
> > > recreate such indexes. However, I am even at a loss in how to fix the
> > > CREATE FUNCTION to reference a cast in the same schema as the function,
> > > in this case 'public'. We can rewrite the cast to not use :: and use a
> > > function call with schema qualification. e.g. public.earth(), but how do
> > > we know what schema that is in, i.e. what if the extension is loaded
> > > into a schema other than public?
>
> The task is to convert it to being a non-relocatable extension that uses
> @extschema@, like here:
> https://www.postgresql.org/docs/devel/static/extend-extensions.html#EXTEND-EXTENSIONS-EXAMPLE

Extension "earthdistance" creates sql-language functions that call functions
of extension "cube", which may appear outside @extschema@ and may relocate at
any moment. Hence, this case is not as simple as using @extschema(at)(dot) While
the cube/earthdistance system happens to be revealing this problem, it would
arise in many cases of a function referring to an object of a relocatable
extension. I see these options:

1. Stop using relocatable=true in core extensions (by adding a new version
number and versioned control file). To relocate an extension, drop and
recreate it. Deprecate relocatable=true. Sub-options:

1a. Require that "earthdistance" and "cube" appear in the same schema by
forcing an error[1] when they don't.

1b. Expand @DEPNAME_schema@ in extension SQL files. Use @cube_schema@ to
refer to the right objects.

1c. Use plpgsql to query pg_extension.extnamespace, then EXECUTE a CREATE
FUNCTION statement after substituting the right schema names.

2. Like (1), including all sub-options, but warn about the problem without
deprecating relocatable=true. Drop relocatable=true from extensions that
have cause to do so: cube, earthdistance, pageinspect, pg_freespacemap,
xml2. Do likewise for others as needed in the future.

3. Make "earthdistance" dynamically discover the location of "cube" during
each function call. This entails rewriting earthdistance sql-language
functions in C. (One could use plpgsql, but that would add a substantial
performance loss and a runtime dependency.)

4. Re-implement the earthdistance sql functions in C, not calling "cube"
functions at all.

5. Create copies in "earthdistance" of the "cube" functions it uses[2]. This
violates modularity. It makes \dx+ uglier.

6. Allow an extension to ship SQL commands for re-binding to schemas when it
or a dependency relocates. This would allow relocatable=true in extensions
that refer to @extschema(at)(dot) Include (1b) in this project.

7. Augment function system and LANGUAGE sql to offer the ability to parse at
CREATE time, storing a Query tree like we do for views/rules. (This would
be a complex feature.) (One can simulate this today with a rule[3], but it
defeats inline_function().)

Overall, I lean toward (2b). It's a self-contained project that doesn't
uglify contrib and that sets a reasonable example for non-core extensions.
While (7) would solve this and other problems nicely, it's a poor back-patch
candidate. I liked (1b) for awhile, but it would be overkill if we ever get
(7). Other ideas or preferences?

Thanks,
nm

[1] SELECT 'cube and earthdistance must appear in the same schema',
1 / (count(DISTINCT extnamespace) = 1)::int
FROM pg_extension WHERE extname IN ('cube', 'earthdistance');

[2] CREATE FUNCTION _earthdistance_cube(float8) RETURNS cube
AS '$libdir/cube', 'cube_f8'
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;

[3] CREATE TABLE sum_scratch (addend0 int, addend1 int, sum int);
CREATE VIEW sum_impl AS SELECT * FROM sum_scratch;
CREATE RULE sum_calc AS
ON INSERT TO sum_impl DO INSTEAD INSERT INTO sum_scratch
VALUES (NEW.addend0, NEW.addend1, NEW.addend0 + NEW.addend1) RETURNING *;
CREATE FUNCTION sum(int, int) RETURNS int LANGUAGE sql AS
$$INSERT INTO sum_impl VALUES ($1, $2) RETURNING sum$$;
SELECT sum(4, 3);