LTREE extension does not exist after CREATE EXTENSION

Question

I am trying to create a trigger that uses ltree column.

CREATE EXTENSION IF NOT EXISTS ltree;

Running

SELECT * FROM pg_catalog.pg_extension 

gives the following output:

extname     |extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
------------|--------|------------|--------------|----------|---------|------------|
plpgsql     |      10|          11|false         |1.0       |NULL     |NULL        |
ltree       |      10|      361964|true          |1.1       |NULL     |NULL        |
postgres_fdw|      10|      361970|true          |1.0       |NULL     |NULL        |

I cannot drop the extension because I get an error that there are dependencies. I was also able to create a table that has a column with data type ltree without an error.

When trying to create a trigger, I get the following error:

SQL Error [42704]: ERROR: type "ltree" does not exist
  Position: 252

This is a snippet of the trigger and the problem seems to be happeing when I declare a variable of type ltree:

CREATE OR REPLACE FUNCTION asset.update_asset_path()

RETURNS TRIGGER

AS $$
    DECLARE
        db_path_ltree ltree;
        single_asset INT;
        s3path TEXT = '';
        ltree_array TEXT[];
        v_asset_name TEXT;
        i_last_element INT;

What is confusing, is this is only happening in one environment for dev. Every other postgres server does not have this problem and is using the same code.

Any ideas why this is happening?

Answer 1

The extension ltree is installed in the schema with the object ID 361964. If you want to know the name of that schema, run

SELECT 361964::regnamespace;

It could be that that schema is not on the search_path when you execute the function. That would explain the error.

If you install an extension in a schema other than public, it is usually best to reference its objects with schema qualification, for example

DECLARE
    db_path_ltree extschema.ltree;