Hologres:Extensions

Limitations

• An extension can be loaded into only one schema per database. For example, loading an extension into the default schema prevents you from loading it into any other schema in that database.

• Load an extension into the pg_catalog system schema to make it available across all schemas in the database. If no schema is specified, the extension loads into the public schema by default.

• Only accounts with Superuser permissions can load or uninstall extensions.

• Currently, only built-in system extensions are supported. Custom or external extensions cannot be loaded.

Supported extensions

Load an extension

Loading an extension into pg_catalog makes it available across all schemas in the database. If you load an extension into any other schema, its functions are only accessible within that schema's search_path.

-- Requires Superuser permissions
CREATE EXTENSION IF NOT EXISTS <extension_name> SCHEMA <schema_name>;

Example: Load the postgis extension into pg_catalog:

CREATE EXTENSION IF NOT EXISTS postgis SCHEMA pg_catalog;

View loaded extensions in the current database

Run the following query to list all extensions loaded in the current database, including those loaded by default:

SELECT
    e.extname AS "Name",
    e.extversion AS "Version",
    n.nspname AS "Schema",
    c.description AS "Description"
FROM
    pg_catalog.pg_extension e
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
    LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
        AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
    ORDER BY 1;

Uninstall an extension

-- Requires Superuser permissions
DROP EXTENSION <extension_name>;

Example: Query an extension across schemas

Some extensions, such as roaringbitmap, can only be loaded into the public schema. If your table is in a different schema, calling an RB function without a schema prefix fails with function xxx does not exist. Prefix the function with public. to resolve this.

The following example demonstrates the issue and the fix.

1. Create a table in a non-public schema and populate it with data.

   CREATE EXTENSION roaringbitmap;
   
   CREATE SCHEMA test;
   
   -- Create a table in the test schema
   CREATE TABLE test.t1 (
       id integer,
       bitmap roaringbitmap
   );
   
   -- Insert a bitmap from an array
   INSERT INTO test.t1
   SELECT 1, RB_BUILD(ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 200]);
   
   -- Insert a bitmap aggregated from a series
   INSERT INTO test.t1
   SELECT 2, RB_BUILD_AGG(e)
   FROM GENERATE_SERIES(1, 100) e;

2. Query using an RB function while search_path is set to public. The query succeeds.

   SELECT RB_OR(a.bitmap, b.bitmap)
   FROM (SELECT bitmap FROM test.t1 WHERE id = 1) AS a,
        (SELECT bitmap FROM test.t1 WHERE id = 2) AS b;

3. Switch search_path to the test schema and run the same query. The query fails.

   SET search_path TO test;
   
   SELECT RB_OR(a.bitmap, b.bitmap)
   FROM (SELECT bitmap FROM test.t1 WHERE id = 1) AS a,
        (SELECT bitmap FROM test.t1 WHERE id = 2) AS b;
   -- ERROR: function rb_or(public.roaringbitmap, public.roaringbitmap) does not exist

4. Prefix the function with public. to call it from the correct schema.

   SELECT public.RB_OR(a.bitmap, b.bitmap)
   FROM (SELECT bitmap FROM test.t1 WHERE id = 1) AS a,
        (SELECT bitmap FROM test.t1 WHERE id = 2) AS b;

FAQ

An extension was loaded into the wrong schema. After switching schemas, its functions are inaccessible and return `function xxx does not exist`.

This happens when an extension is loaded into the public schema but the active search_path no longer includes public. Uninstall the extension without CASCADE, then reload it into pg_catalog to make it accessible from all schemas:

DROP EXTENSION hologres_fdw;

CREATE EXTENSION IF NOT EXISTS hologres_fdw SCHEMA pg_catalog;