Oracle Pragma SERIALLY_REUSABLE、RESTRICT_REFERENCES及INLINE的PostgreSQL实现咨询

Hey there! I’ve walked through several Oracle-to-PostgreSQL migration projects, so let’s break down how to replicate each of these pragmas in PostgreSQL—since the two databases handle these concepts a bit differently.

1. Replacing PRAGMA SERIALLY_REUSABLE #

In Oracle, this pragma marks a package so that its state (package-level variables) is reset after each call, cutting down on memory usage for frequently executed packages. PostgreSQL doesn’t have a direct equivalent pragma, but we can mimic the behavior with two straightforward approaches:

• Use local variables in functions/procedures: PostgreSQL functions don’t retain state between calls by default. If your Oracle package used SERIALLY_REUSABLE to avoid persisting variables across calls, simply move those variables inside individual PostgreSQL functions. Example:

  -- Oracle (package with SERIALLY_REUSABLE)
  CREATE PACKAGE my_pkg AS
    PRAGMA SERIALLY_REUSABLE;
    PROCEDURE process_data(p_id NUMBER);
  END my_pkg;
  
  CREATE PACKAGE BODY my_pkg AS
    PROCEDURE process_data(p_id NUMBER) IS
      v_temp NUMBER; -- Reset after each call
    BEGIN
      v_temp := p_id * 2;
      DBMS_OUTPUT.PUT_LINE(v_temp);
    END;
  END my_pkg;
  
  -- PostgreSQL equivalent
  CREATE OR REPLACE PROCEDURE process_data(p_id INT) AS
  $$
  DECLARE
    v_temp INT; -- Fresh local variable on each call
  BEGIN
    v_temp := p_id * 2;
    RAISE NOTICE '%', v_temp;
  END;
  $$ LANGUAGE plpgsql;
  

• Session-level temporary tables: If you need to share state across multiple function calls within a single session (but not across sessions), use a temporary table—it gets automatically dropped when the session ends:

  CREATE TEMP TABLE IF NOT EXISTS session_state (
    key TEXT PRIMARY KEY,
    value INT
  );
  
  -- Use this table in your functions to store/reuse temporary state
  

2. Replacing PRAGMA RESTRICT_REFERENCES #

Oracle uses this pragma to enforce that a function has no unintended side effects (like writing to the database or modifying package variables). In PostgreSQL, we use function volatility attributes to declare these constraints, which the query optimizer uses to optimize performance:

Example conversion:

-- Oracle
CREATE FUNCTION calculate_tax(p_amount NUMBER) RETURN NUMBER AS
  PRAGMA RESTRICT_REFERENCES(calculate_tax, WNDS, RNDS, WNPS, RNPS);
BEGIN
  RETURN p_amount * 0.08;
END;

-- PostgreSQL (IMMUTABLE since it only depends on input)
CREATE OR REPLACE FUNCTION calculate_tax(p_amount NUMERIC) 
RETURNS NUMERIC 
IMMUTABLE -- Tells PostgreSQL results are fixed for the same input
LANGUAGE plpgsql AS
$$
BEGIN
  RETURN p_amount * 0.08;
END;
$$;

For functions that read from the database but don’t modify it, use STABLE instead of IMMUTABLE.

3. Replacing PRAGMA INLINE #

Oracle’s PRAGMA INLINE hints to the compiler whether to inline a function call (replace the call with the function’s code) for performance. PostgreSQL has limited direct control, but here’s how to handle it:

• PostgreSQL 11+: Use the INLINE clause in the function definition to suggest inlining. A value of 1 encourages inlining, 0 discourages it:

  -- Oracle
  CREATE FUNCTION get_discount(p_amount NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN p_amount * 0.1;
  END;
  
  -- Inline hint in Oracle
  PRAGMA INLINE(get_discount, 'YES');
  v_total := p_amount - get_discount(p_amount);
  
  -- PostgreSQL equivalent with INLINE hint
  CREATE OR REPLACE FUNCTION get_discount(p_amount NUMERIC) 
  RETURNS NUMERIC 
  INLINE 1 -- Suggests the optimizer should inline this function
  LANGUAGE plpgsql AS
  $$
  BEGIN
    RETURN p_amount * 0.1;
  END;
  $$;
  
  -- Usage (optimizer considers inlining based on the hint)
  SELECT p_amount - get_discount(p_amount) AS total FROM orders;
  

• JIT Optimization: If you’re using PostgreSQL’s Just-In-Time compilation (enabled by default in newer versions), adjust the jit_inline_functions configuration parameter to control inlining behavior for JIT-compiled functions.

内容的提问来源于stack exchange，提问作者Suresh