How to fix 39P03: event_trigger_protocol_violated in PostgreSQL

-- Correct signature for ddl_command_end event trigger:
CREATE OR REPLACE FUNCTION my_event_trigger()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- Event trigger logic here
  RAISE NOTICE 'DDL command executed';
END;
$$;

-- For event triggers that don't need to return a value:
CREATE OR REPLACE FUNCTION my_event_trigger()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- Event trigger logic here
  INSERT INTO ddl_log (command) VALUES (TG_TAG);
END;
$$;

-- INCORRECT - missing return type or wrong type:
CREATE OR REPLACE FUNCTION bad_trigger()  -- Missing RETURNS clause
LANGUAGE plpgsql
AS $$
BEGIN
  -- This will cause 39P03 error
END;
$$;

-- Correct usage of event trigger variables:
CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO ddl_audit (
    event_type,
    command_tag,
    executed_at,
    username
  ) VALUES (
    TG_EVENT,
    TG_TAG,
    NOW(),
    CURRENT_USER
  );
END;
$$;

-- Create the event trigger:
CREATE EVENT TRIGGER log_ddl_trigger
  ON ddl_command_end
  EXECUTE FUNCTION log_ddl_event();

-- Common mistakes:
-- 1. Trying to pass custom parameters to event trigger functions
-- 2. Using variables not available in event trigger context
-- 3. Missing required variable declarations

-- PROBLEMATIC CODE in event triggers:
CREATE OR REPLACE FUNCTION problematic_trigger()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- These can cause 39P03 errors:
  
  -- 1. Transaction control (NOT ALLOWED):
  COMMIT;  -- ERROR
  ROLLBACK; -- ERROR
  
  -- 2. Certain DDL in some contexts:
  CREATE TABLE temp_table (id serial); -- May be restricted
  
  -- 3. Operations that conflict with ongoing DDL:
  ALTER TABLE users ADD COLUMN new_column int; -- If trigger fires on ALTER
  
  -- 4. Accessing temporary tables from other sessions
END;
$$;

-- SAFE operations in event triggers:
CREATE OR REPLACE FUNCTION safe_trigger()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- These are generally safe:
  INSERT INTO audit_log (event, timestamp) VALUES (TG_TAG, NOW());
  UPDATE statistics SET ddl_count = ddl_count + 1;
  PERFORM pg_notify('ddl_event', TG_TAG);
END;
$$;

-- Supported languages for event triggers:
-- plpgsql (most common)
-- C (with proper event trigger handler)
-- Other procedural languages with event trigger support

-- Check if your language supports event triggers:
SELECT lanname, lanpltrusted
FROM pg_language
WHERE lanname = 'plpgsql';

-- For custom C functions, ensure proper handler:
CREATE OR REPLACE FUNCTION my_c_trigger()
RETURNS event_trigger
AS '/path/to/library', 'my_event_trigger'
LANGUAGE C;

-- Common issues:
-- 1. Using SQL language (CREATE FUNCTION ... LANGUAGE sql) - NOT SUPPORTED
-- 2. Missing C handler function for event trigger interface
-- 3. Language not installed or not trusted

-- 1. First, drop the event trigger if it exists:
DROP EVENT TRIGGER IF EXISTS my_trigger;

-- 2. Test the function directly:
SELECT my_event_trigger();
-- If this fails, the function has fundamental issues

-- 3. Create a simple test event trigger:
CREATE EVENT TRIGGER test_trigger
  ON ddl_command_end
  WHEN TAG IN ('CREATE TABLE', 'DROP TABLE')
  EXECUTE FUNCTION my_event_trigger();

-- 4. Test with a simple DDL command:
BEGIN;
CREATE TABLE test_table (id serial);  -- Should trigger
DROP TABLE test_table;                -- Should trigger
ROLLBACK;

-- 5. Check PostgreSQL logs for detailed error:
-- tail -f /var/log/postgresql/postgresql-*.log
-- Look for "event_trigger_protocol_violated" with stack trace

-- Check PostgreSQL version:
SELECT version();

-- Event triggers were introduced in PostgreSQL 9.3
-- Significant changes in 9.4, 9.5, 10, 11, etc.

-- Check if your function uses features from newer versions:
-- 1. Filter conditions (WHEN clause) added in 9.5
-- 2. New event types in later versions
-- 3. Changes to variable availability

-- Review migration notes for your PostgreSQL version:
-- https://www.postgresql.org/docs/current/release.html

-- If migrating from older version, recreate event triggers:
DROP EVENT TRIGGER old_trigger;
CREATE EVENT TRIGGER new_trigger
  ON ddl_command_end
  EXECUTE FUNCTION updated_event_trigger();