How to fix 2F000: sql_routine_exception in PostgreSQL

-- Find the routine definition
SELECT proname, prosrc, prolang
FROM pg_proc 
WHERE proname = 'your_function_name';

-- Check for routines with recent errors
SELECT * FROM pg_stat_user_functions WHERE calls > 0 ORDER BY total_time DESC;

-- Get the function source code
SELECT prosrc FROM pg_proc WHERE proname = 'problem_function';

CREATE OR REPLACE FUNCTION safe_function()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- Your logic here
EXCEPTION
  WHEN sql_routine_exception THEN
    RAISE NOTICE 'Routine failed: %', SQLERRM;
    -- Handle or re-raise
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;
$$;

-- Test the routine with simple, controlled inputs
BEGIN;
SELECT your_problem_function('test_input');
ROLLBACK;

SET client_min_messages = DEBUG1;
-- Run your routine
SET client_min_messages = NOTICE;

-- Check for constraint violations
SELECT conname, contype, consrc 
FROM pg_constraint 
WHERE conrelid = 'your_table'::regclass;

-- Examine data that might violate constraints
SELECT * FROM your_table WHERE some_column IS NULL;

-- Example defensive check in PL/pgSQL
IF input_value IS NULL THEN
  RAISE EXCEPTION 'Input cannot be NULL';
END IF;

CREATE OR REPLACE FUNCTION robust_function(input_data jsonb)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  v_record record;
BEGIN
  -- Validate input
  IF input_data IS NULL OR input_data = '{}'::jsonb THEN
    RAISE EXCEPTION 'Invalid input data';
  END IF;

  BEGIN
    -- Main logic with savepoint for partial rollback
    SAVEPOINT sp_main;
    
    -- Your business logic here
    
    RELEASE SAVEPOINT sp_main;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO SAVEPOINT sp_main;
      RAISE NOTICE 'Function failed: %', SQLERRM;
      RAISE;
  END;
END;
$$;

-- Create a table to track routine failures
CREATE TABLE IF NOT EXISTS routine_error_log (
  id serial PRIMARY KEY,
  routine_name text NOT NULL,
  error_message text,
  parameters jsonb,
  occurred_at timestamptz DEFAULT NOW()
);

-- Create a function to log errors
CREATE OR REPLACE FUNCTION log_routine_error(
  p_routine_name text,
  p_error_message text,
  p_parameters jsonb DEFAULT NULL
) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO routine_error_log (routine_name, error_message, parameters)
  VALUES (p_routine_name, p_error_message, p_parameters);
END;
$$;