Best way to install hstore on multiple schemas in a Postgres database?

Question

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.

Goodspeed

Answer 1

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.

• How does the search_path influence identifier resolution and the "current schema"

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

Disintegrate

The extensions schema would need to be added to search_path, correct? – Inviolable

@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: #22976099 – Disintegrate

Are there any obvious benefits other than clarity in installing extensions in a dedicated schema rather than using public? – Inviolable

@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 ... – Disintegrate

Answer 2

Try this to install extension hstore on all schemas

create extension hstore schema pg_catalog;

Valma

I don't think it's a good idea to store non-core things in pg_catalog – Hettiehetty