Hologres:Extensions

Limits

• You can create extensions in only one schema of a database. For example, if you have created extensions in the default schema of a database, you cannot create extensions in other schemas of the database.

• If you create an extension in the pg_catalog system schema, the feature provided by the extension can be used in all schemas of the database by default. If you do not specify a schema when you create an extension, the extension is created in the public schema by default.

• You can create or drop an extension only as a superuser.

• You can create only built-in extensions. Custom extensions and external extensions cannot be created.

Extensions

Create an extension

We recommend that you create an extension in the pg_catalog system schema. In this case, the feature provided by the extension can be used in all schemas of the database by default.

• Syntax

  To create an extension, execute the following SQL statement:

  -- You must create an extension as a superuser.
  CREATE extension IF NOT EXISTS <extension_name> SCHEMA <schema_name>;

  Parameter	Description
  extension_name	The name of the extension that you want to create. For more information about the extensions that are supported by Hologres, see the "Extensions" section in this topic.
  schema_name	The schema in which you want to create the extension. If you do not specify a schema, the extension is created in the public schema by default. We recommend that you specify the pg_catalog schema. This way, the extension takes effect on all schemas in the database.

• Examples

  Create the postgis extension in the pg_catalog schema.

  CREATE extension if not exists postgis schema pg_catalog;

View the created extensions in the current database

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;

Drop an extension

-- You must drop an extension as a superuser.
DROP extension <extension_name>;

Example of querying extensions across schemas

Specific extensions can be created only in specific schemas and cannot be created in the pg_catalog schema. For example, you can create the roaringbitmap extension only in the public schema. If a table exists in a schema rather than the public schema and you use an RB function on the table, the following error message is reported: function xxx does not exist. To resolve this issue, you must add the name of the default schema when you use the function. This section describes how to use the roaringbitmap extension.

1. Prepare data.

   Create a table in a schema rather than the public schema and import data into the table.

   CREATE EXTENSION roaringbitmap;
   
   CREATE SCHEMA test;
   
   -- Create a table in a specified schema.
   CREATE TABLE test.t1 (
       id integer,
       bitmap roaringbitmap
   );
   
   -- Set the bit value of an array to 1.
   INSERT INTO test.t1
   SELECT
       1,
       RB_BUILD (ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 200]);
   
   -- Set the bit values of multiple elements to 1 and aggregate the bit values into a roaring bitmap.
   INSERT INTO test.t1
   SELECT
       2,
       RB_BUILD_AGG (e)
   FROM
       GENERATE_SERIES(1, 100) e;
   

2. In the public schema, use an RB function to query data in the table. 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. In the schema in which the table is created, use the RB function to query data in the table. An error message is reported.

   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;
   RROR:  function rb_or(public.roaringbitmap, public.roaringbitmap) does not exist

4. Solution: Add public before the RB function and use the RB function in the public 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

When I create an extension in an invalid schema, and I use the function related to the extension in another schema, the error message function xxx does not exist is reported. What do I do?

• Cause: The extension is created in the public schema and cannot be accessed from other schemas.

• Solution: Drop the extension, and create the extension in the pg_catalog schema. Sample statements:

  Important

  We recommend that you do not use the DROP EXTENSION <extension_name> CASCADE; syntax to drop an extension. Otherwise, all objects that depend on the extension are dropped, and your business is negatively affected.

  drop extension hologres_fdw;
  
  create extension if not exists hologres_fdw schema pg_catalog;