In Supabase, which is built on PostgreSQL, when you create functions using PL/pgSQL (Procedural Language/PostgreSQL), you have the option to specify the volatility category of each function: VOLATILE, STABLE, or IMMUTABLE. These categories describe how the function interacts with the database, how predictable its output is, and whether it can have side effects. Understanding these distinctions is crucial for writing efficient, safe, and optimized database functions.

Volatility Categories Explained

1. IMMUTABLE Functions

• Definition: Functions that always return the same result for the same input parameters and have no side effects. They do not read or modify the database.
• Characteristics:
  • Deterministic: Their output depends solely on their input parameters.
  • No Side Effects: They cannot access tables, modify database data, or depend on any external state.
• Examples:
  • Mathematical computations: abs(), sqrt()
  • String manipulations: upper(), concat()
• Usage Notes:
  • Can be used in index expressions and materialized views.
  • The query planner can optimize queries by precomputing or caching results.

2. STABLE Functions

• Definition: Functions that do not modify the database but can read data. They return consistent results within a single query execution but might return different results in different executions if the underlying data changes.
• Characteristics:
  • Read-Only Access: They can read tables or views but not modify them.
  • Consistent Within a Query: The function returns the same result throughout a single query execution.
• Examples:
  • Lookup functions: fetching a value from a table based on an input.
  • Functions that depend on the current time (CURRENT_TIMESTAMP is actually STABLE).
• Usage Notes:
  • Suitable for functions that depend on database data that doesn't change during the query.
  • Cannot be used in certain index expressions because the output may change between queries.

3. VOLATILE Functions

• Definition: Functions that can perform any operations, including modifying the database. The results can vary even within a single query execution.
• Characteristics:
  • Non-Deterministic: Results may change for the same inputs at any time.
  • Possible Side Effects: Can insert, update, delete data, or perform external actions.
• Examples:
  • Functions that generate random values: random()
  • Functions that read system status: currval()
  • Functions that modify data: inserting logs, updating records.
• Usage Notes:
  • Cannot be optimized by the query planner.
  • Always executed as they appear in the query.

Why Volatility Matters

The volatility category affects how PostgreSQL (and thus Supabase) handles function execution and query optimization:

• Performance Optimization:
  • The query planner can optimize queries more aggressively with IMMUTABLE functions, such as by using indexes or caching results.
  • STABLE functions have moderate optimization, while VOLATILE functions have minimal optimization.
• Correctness and Safety:
  • Misclassifying a function can lead to incorrect results or unintended side effects.
  • For example, declaring a function as IMMUTABLE when it reads database tables could cause the planner to use stale data.
• Security and RLS (Row Level Security):
  • Incorrect volatility classification can affect how RLS policies are enforced.
  • Functions that access tables with RLS policies need careful consideration.

Applying Volatility Categories in Supabase Functions

When creating functions in Supabase, you choose the appropriate volatility based on what your function does:

When to Use IMMUTABLE

• Use Cases:

  • Functions that perform calculations or transformations purely based on input parameters.
  • Example: A function that converts temperatures from Celsius to Fahrenheit.

• Considerations:

  • Do not access or modify database tables.
  • Ensure that the function's output depends only on its inputs.

When to Use STABLE

• Use Cases:

  • Functions that read data from the database but do not modify it.
  • Example: A function that returns a user's role based on their ID.

• Considerations:

  • Safe to read from tables, but writes are not allowed.
  • The function's result might change between queries if the underlying data changes.

When to Use VOLATILE

• Use Cases:

  • Functions that modify the database or perform actions that can produce different results even within the same query.
  • Example: A function that logs actions to a table or generates random numbers.

• Considerations:

  • Necessary for functions with side effects.
  • Accept that minimal query optimization will occur.

Examples

IMMUTABLE Function Example

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER) RETURNS INTEGER
LANGUAGE SQL IMMUTABLE AS $$
  SELECT a + b;
$$;

• Explanation: This function simply adds two numbers. It doesn't access the database, so it's IMMUTABLE.

STABLE Function Example

CREATE FUNCTION get_user_email(user_id UUID) RETURNS TEXT
LANGUAGE SQL STABLE AS $$
  SELECT email FROM users WHERE id = user_id;
$$;

• Explanation: The function reads from the users table but doesn't modify any data. It's STABLE because it might return different emails if the users table changes between queries.

VOLATILE Function Example

CREATE FUNCTION record_page_view(page_id UUID, user_id UUID) RETURNS VOID
LANGUAGE plpgsql VOLATILE AS $$
BEGIN
  INSERT INTO page_views (page_id, user_id, viewed_at)
  VALUES (page_id, user_id, NOW());
END;
$$;

• Explanation: This function inserts a new record into the page_views table, modifying the database. It's VOLATILE.

Impact on Query Planning and Optimization

• IMMUTABLE Functions:

  • Can be evaluated at plan time if inputs are constants.
  • Can be used in index expressions and indexed searches.
  • Example: An index on LOWER(email) allows for case-insensitive searches using an IMMUTABLE function.

• STABLE Functions:

  • Evaluated once per query execution when used in a query.
  • Cannot be used in index expressions.
  • The planner assumes they return the same result during a single query.

• VOLATILE Functions:

  • Evaluated every time they are called in the query.
  • No assumptions are made about the result consistency.
  • Can't be optimized or pre-evaluated.

Security Considerations in Supabase

• Row Level Security (RLS):
  • RLS policies can call functions to determine access rights.
  • Functions used in RLS should typically be STABLE or IMMUTABLE to ensure consistent behavior during policy enforcement.
• Function Privileges:
  • Be cautious with SECURITY DEFINER functions, which run with the privileges of the function's owner.
  • Ensure that functions don't unintentionally bypass security checks.

Best Practices

1. Accurately Declare Volatility:

   • Always declare the correct volatility to match the function's behavior.
   • Resist the temptation to declare a function as less volatile for potential performance gains if it doesn't match its true behavior.

2. Avoid Side Effects in STABLE and IMMUTABLE Functions:

   • Do not perform write operations or rely on mutable external state.
   • Ensure that the function's classification aligns with what it actually does.

3. Use IMMUTABLE Functions When Possible:

   • For functions that perform calculations or manipulations without database interaction.
   • Allows for better optimization and potential use in indexes.

4. Consider Function Dependencies:

   • If an IMMUTABLE function calls another function that is STABLE or VOLATILE, the volatility must match the most volatile function in the chain.
   • PostgreSQL enforces that a function cannot be less volatile than any functions it calls.

5. Test Function Behavior:

   • Verify that functions behave as expected, especially when used in complex queries or with RLS policies.
   • Check for consistency and correctness in different scenarios.

Summary

• VOLATILE:

  • Use for functions that can change data or whose results can vary unpredictably.
  • Example: Functions that insert logs, generate random numbers, or read system variables that change rapidly.

• STABLE:

  • Use for read-only functions that query the database but don't modify it.
  • Example: Functions that fetch and return data from tables.

• IMMUTABLE:

  • Use for functions that always produce the same output from the same input and have no side effects.
  • Example: Pure mathematical functions or deterministic conversions.

By correctly categorizing your functions in Supabase, you help the database engine optimize queries effectively and maintain data integrity and security. Accurate volatility declarations ensure that the query planner makes the right assumptions, leading to better performance and reliable application behavior.

Additional Notes

• Function Costs and Rows Estimates:
  • You can also specify COST and ROWS estimates when creating functions, which can further help the query planner.
• Updating Function Definitions:
  • If you change a function's behavior, remember to update its volatility classification accordingly.
• Documentation and Maintenance:
  • Document your functions with comments explaining their purpose and why a particular volatility category was chosen.

Understanding the differences between VOLATILE, STABLE, and IMMUTABLE functions is essential when working with Supabase's PL/pgSQL functions. By carefully considering how your functions interact with the database and their potential side effects, you can write efficient, secure, and reliable database code.