Troubleshooting PostgreSQL search_path

If there is one word that captures the essence of search_path, it is probably implicit. It saves us from explicitly writing the schema before every PostgreSQL object, making SQL easier to read.

However, this convenience comes at a cost: when an object is resolved implicitly, the actual cause of a problem does not always appear clearly in error messages or logs. Sometimes the symptoms seem completely unrelated to search_path.

I already touched on this point in my previous article about search_path, notably by showing that PostgreSQL extension SQL scripts rely on it as well. In other words, we are not the only ones depending on search_path within our own cluster.

Additionally, that demonstration itself would have made a good debugging case study worthy of inclusion in this article. I encourage you to give it a read.

Trying to avoid altogether by emptying it and/or systematically qualifying every PostgreSQL object with its schema is a false solution. Since it is a cross-cutting mechanism shared both by your application logic and by other components of the cluster, establishing a clear and consistent policy around its usage becomes essential.

This article therefore aims to sharpen your diagnostic instincts regarding bugs induced by search_path, through a case study involving a seemingly missing function. Since these bugs often share similar patterns, a detailed study of a single case should help you recognize the symptoms that ought to trigger a thought such as: "What if this is caused by search_path?"

Initial situation

A developer needs to add the first function to the cluster. Wanting to keep the databases properly organized, he decides to group functions into a schema named “routines”. As the name suggests, this schema will store PostgreSQL routines, namely functions and procedures.

He creates the schema, implement the function while explicitly specifying routines as its storage schema, and even define a session-level search_path in a setup.sql file included from the script entry point using \ir <path/to/setup.sql>.

setup.sql    

    \set ON_ERROR_STOP true

    \set QUIET

    \pset format unaligned
    \pset tuples_only true
    \pset pager off

    SET search_path = routines, extensions;

The function is tested, and everything works correctly.

    -- --------------------
    -- db_owner_owns_schema
    -- --------------------
    --  Parameters :
    --      schema_name : text
    --      db_owner : text
    --  Returns : text
    --      Pgtap description.
    --  Notes :
    --      pg_database_owner is the predefined role standing for
    --      the database owner.
    --      See the exhibit pg_database_owner_is_database_owner.

    CREATE OR REPLACE FUNCTION db_owner_owns_schema(
        db_owner text,
        schema_name text
    )
    RETURNS text
    LANGUAGE sql
    AS $$
        SELECT schema_owner_is(
            schema_name,
            'pg_database_owner',
            'Schema ' || schema_name || ' should be owned by ' || db_owner
        );
    $$;

While reviewing the code, the developer notices that the function would be more readable if the parameter order were different.

    ERROR:  cannot change name of input parameter "schema_name"
    HINT:  Use DROP FUNCTION db_owner_owns_schema(text,text) first.

Since the order and types of parameters are part of what PostgreSQL uses to identify a function object - something the error message makes particularly clear - the function must therefore be dropped using DROP FUNCTION <function_name>; and then recreated. But :

    drop function db_owner_owns_schema;
    ERROR:  could not find a function named "db_owner_owns_schema"

Yet the function clearly exists, since it had already been called multiple times during development. What’s wrong? Where is it?

Locating the function

PostgreSQL provides users with meta-commands to avoid querying the cluster’s system catalogs directly. Among them, \df <function_name> can be used to retrieve information about a function.

    \df db_owner_owns_schema

                        List of functions
    Schema | Name | Result data type | Argument data types | Type 
    -------+------+------------------+---------------------+------
    (0 rows)

Let’s use \df differently and list the functions present in the routines schema.

    \df routines.*

    Schema   |          Name           
    ---------+-------------------------
    routines | db_owner_owns_schema    
    (1 rows)

The function clearly exists, so why does DROP FUNCTION fail? Of course, we could explicitly qualify the function with its schema such as DROP FUNCTION <schema_name>.<function_name>;, but this merely works around the issue, while also providing a clue about the actual cause of the bug: a search_path different from the one we expected.

Indeed, an object that cannot be found despite already existing is often symptomatic of a search_path-related issue. Let's verify that:

    show search_path;
    # or current_setting('search_path');

    search_path 
    -------------
    extensions
    (1 row)

The displayed search_path corresponds to the one applied to this session through its configuration. In the developer's workflow, however, scripts are executed remotely: they connect to the database, open a session, include setup.sql (thereby modifying search_path), execute their logic, and finally disconnect.

The developer, not wanting to create a dedicated script simply to change a function’s parameter order, connected directly to the database and manually executed the DROP FUNCTION command. However, the search_path of this session was defined by its configuration rather than by setup.sql, which had been systematically included in the test scripts.

Retrying with a search_path containing routines should therefore resolve the issue.

    set search_path = routines, extensions;
    SET
    drop function db_owner_owns_schema;
    DROP FUNCTION

This reveals one of the most subtle implications of search_path: when configured incorrectly, it can unintentionally hide your own PostgreSQL objects.

We can verify that the function was successfully removed using \df db_owner_owns_schema, now that search_path is configured correctly - at least for the duration of the current session. We can also bypass the meta-command entirely and directly query the catalog using the following SQL statement:

    SELECT
        proname,
        pronamespace::regnamespace
    FROM pg_proc
    WHERE proname = 'db_owner_owns_schema';

    proname | pronamespace 
    --------+-------------
    (0 rows)

Conclusion

Search_path can still hold many surprises; this article is only a first step toward developing diagnostic instincts for this type of bug.

If there is one thing to remember, it is that a search_path different from the one you expect can hide objects that are actually present in the database.

At best, this results in a PostgreSQL error stating that an object does not exist or cannot be found - neither by the engine nor through the meta-commands you use.

At worst, tables that are actually present become invisible to the engine, which relies on search_path to resolve objects during execution. Queries execute successfully, scripts run without issue, yet information is missing and results diverge from what was expected, without any obvious explanation.

Search_path is not a simple configuration detail; it directly influences the visibility of the objects your application uses.