DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 39P01 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 39P01: trigger protocol violated

The 39P01 trigger protocol violated error occurs when a trigger function fails to comply with PostgreSQL's internal trigger calling convention. This typically means the function is not returning the correct type or value expected by the trigger dispatcher. Unlike regular functions, trigger functions must be declared with RETURNS trigger and must return NEW, OLD, or NULL depending on the trigger type.

Top 3 Causes

1. Missing or Incorrect RETURN Statement

The most common cause is a trigger function that does not return NEW (or NULL) at the end of its execution. In BEFORE row-level triggers, a missing RETURN causes the trigger dispatcher to receive an unexpected result.

-- WRONG: No RETURN statement
CREATE OR REPLACE FUNCTION bad_trigger()
RETURNS trigger AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price must be positive';
    END IF;
    -- Missing RETURN NEW causes 39P01
END;
$$ LANGUAGE plpgsql;

-- CORRECT: Always return NEW or NULL
CREATE OR REPLACE FUNCTION good_trigger()
RETURNS trigger AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION 'Price must be positive';
    END IF;
    RETURN NEW;  -- Required
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

2. Function Not Declared as RETURNS trigger

Attaching a non-trigger function (e.g., RETURNS void) to a trigger event will cause PostgreSQL to raise 39P01 at runtime because the executor expects a trigger-compatible return value.

-- WRONG: Function returns void, not trigger
CREATE OR REPLACE FUNCTION not_a_trigger()
RETURNS void AS $$
BEGIN
    RAISE NOTICE 'This will break as a trigger';
END;
$$ LANGUAGE plpgsql;

-- This trigger will fire 39P01 at runtime
CREATE TRIGGER bad_trg
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION not_a_trigger();

-- CORRECT: Declare RETURNS trigger
CREATE OR REPLACE FUNCTION proper_trigger()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER good_trg
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION proper_trigger();
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Return Value in External Language Triggers (PL/Python)

When writing triggers in PL/Python or PL/Perl, the return convention differs from PL/pgSQL. Returning an unsupported type or omitting the return value in a BEFORE trigger violates the protocol.

-- WRONG: Returning an unsupported type in PL/Python trigger
CREATE OR REPLACE FUNCTION bad_python_trg()
RETURNS trigger AS $$
    return 42  -- Invalid: must return "OK", "SKIP", "MODIFY", or None
$$ LANGUAGE plpython3u;

-- CORRECT: Return proper string constant or None
CREATE OR REPLACE FUNCTION good_python_trg()
RETURNS trigger AS $$
    if TD["new"]["amount"] < 0:
        return "SKIP"       # Cancel the operation
    TD["new"]["flag"] = "Y"
    return "MODIFY"         # Apply modifications to NEW
$$ LANGUAGE plpython3u;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1: Identify trigger functions with wrong return types.

-- Find trigger functions with incorrect return type
SELECT p.proname AS function_name,
       pg_get_function_result(p.oid) AS return_type
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
WHERE pg_get_function_result(p.oid) <> 'trigger';
Enter fullscreen mode Exit fullscreen mode

Step 2: Drop and recreate the offending trigger function with the correct RETURNS trigger declaration and a proper RETURN NEW / RETURN NULL / RETURN OLD statement.

Step 3: Validate triggers on a specific table after the fix.

-- Validate triggers on a table
SELECT t.tgname, p.proname, pg_get_function_result(p.oid) AS ret_type
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
JOIN pg_class c ON t.tgrelid = c.oid
WHERE c.relname = 'your_table_name'
  AND NOT t.tgisinternal;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Use a standard trigger function template. Always start with a boilerplate that includes RETURNS trigger and RETURN NEW so neither is accidentally omitted during development or code review.

-- Standard team template
CREATE OR REPLACE FUNCTION schema.trg_tablename_action()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    -- your logic here
    RETURN NEW;  -- or OLD for DELETE triggers
END;
$$;
Enter fullscreen mode Exit fullscreen mode

2. Add a CI/CD validation step. Before deploying to production, run an automated query against pg_trigger and pg_proc to confirm all trigger-bound functions return the trigger type. Catching this at deploy time is far cheaper than debugging it in production.

Related Errors

  • 42P13 invalid_function_definition — Raised at CREATE FUNCTION time when the function definition is structurally invalid; complements 39P01 which is a runtime error.
  • 39004 null_value_not_allowed — Occurs when a trigger function returns NULL for a column with a NOT NULL constraint.
  • P0001 raise_exception — Explicit exceptions raised inside trigger functions; often confused with 39P01 during debugging.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)