12

I'm working on a project that needs to use hstore on multiple schemas. The 'public' schema, where the hstore extension was being installed isn't available everywhere, because my scope doesn't lookup at 'public'. On some tryouts, I've created the extension on a schema called 'hstore' and used the schema on every available scope (search path) used.

Based on this, I've some questions:

  • Is it ok to create a schema just for the extension? Or is it better to create the extension on every single schema (like, customer_1, customer_2, and so on...)?

  • Does the creation of the extension in a separate schema affect where the data is stored? I'm using multiple schemas to make it easier to backup/restore, and really don't want pg to store all my hstore data in a hidden table (like pg_large_objects for blobs) on a single schema.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Gregório Kusowski
  • 188
  • 1
  • 8

2 Answers2

13

It is not allowed to install extensions multiple times per database. Quoting the manual on CREATE EXTENSION:

Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.

If you don't want to include public in your search_path, install "public" extensions into a dedicated schema (example: extensions). I would use a single schema for all of them, not a separate schema for each extension. There are quite a few of them.
CREATE EXTENSION offers an option to install to an existing schema of your choice:

CREATE EXTENSION hstore SCHEMA extensions;

And make sure the schema is included in the search_path of users who might want to make use of it.

Data storage is not affected at all by the schema the extension resides in.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • The `extensions` schema would need to be added to search_path, correct? – Qcom Apr 22 '15 at 16:31
  • 1
    @Qcom: Correct. And the sequence of schemas in the search_path is relevant, too. *Or* you have to schema-qualify all objects you want to reference - including operators! Details: http://stackoverflow.com/questions/22975599/how-to-use-operator-from-the-extension-pg-trgm/22975642#22975642 – Erwin Brandstetter Apr 22 '15 at 16:44
  • Are there any obvious benefits other than clarity in installing extensions in a dedicated schema rather than using public? – Qcom Apr 22 '15 at 17:02
  • 1
    @Qcom: `public` is just another schema. The only thing special about it, is that it's installed by default and the default search_path uses it. A dedicated schema for extensions is mainly for clarity, easier management, easier handling of backups ... – Erwin Brandstetter Apr 22 '15 at 17:07
0

Try this to install extension hstore on all schemas

create extension hstore schema pg_catalog;
Rahul Patel
  • 1,386
  • 1
  • 14
  • 16
  • I don't think it's a good idea to store non-core things in `pg_catalog` –  Apr 18 '17 at 11:03