1

The extension was created by user deploy with:

CREATE EXTENSION ltree;

now I need to move extension to another schema (still as a deploy user, schema utils is owned by deploy user too). So I do:

ALTER EXTENSION ltree SET SCHEMA utils;

which fails with:

ERROR: must be owner of extension ltree

So 2 questions:

  1. Why does PG allow creating the extension for deploy user but not altering it by the same user?
  2. How can I change the schema of the extension still being the deploy user?

Thanks.

Dmytrii Nagirniak
  • 23,696
  • 13
  • 75
  • 130

1 Answers1

0

The extension has to be relocatable to move to another schema.

The easiest solution is probably to just drop the extension from your schema, then create it in a new schema.

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Relocatable? Is that a permission or what? Why the extension created by the same user can't be reallocated to the schema created by the same user? Dropping extension isn't a good option because there are too many dependent objects (which I'll have to drop and recreate too). – Dmytrii Nagirniak Mar 20 '14 at 02:33
  • I just checked ltree.control and it has relocatable = true, on 9.3. What is your version? Can you check ltree.control and see what the setting for relocatable is? – Patrick Mar 20 '14 at 02:43
  • What kind of dependent objects do you have? These may also interfere with the schema change. – Patrick Mar 20 '14 at 02:45
  • I'm on PG 9.3. Dependent objects are functions that refer to `ltree`, `lquery` (without fully qualified path) and few columns. So I just add `utils` schema to the DB's `search_path`. – Dmytrii Nagirniak Mar 20 '14 at 02:53