DEV Community: umzzil nng

PostgreSQL 40000 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 14:03:04 +0000

PostgreSQL Error 40000: Transaction Rollback — What It Is and How to Fix It

PostgreSQL error code 40000 (transaction_rollback) is raised when a transaction cannot be completed and must be forcibly rolled back to preserve data integrity. This is a parent-level error class that encompasses several critical sub-errors including serialization failures (40001) and deadlocks (40P01). Understanding this error and its causes is essential for building resilient, production-grade database applications.

Top 3 Causes

1. Serialization Failure (40001)

When using SERIALIZABLE or REPEATABLE READ isolation levels, two concurrent transactions reading and writing the same data can conflict. PostgreSQL rolls back one of them to maintain consistency.

-- Transaction A and B both run concurrently
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;
-- If another transaction modified these rows concurrently,
-- PostgreSQL throws: ERROR: could not serialize access due to concurrent update

Fix: Implement a retry loop in your application whenever SQLSTATE 40001 is returned.

-- Check current isolation level
SHOW transaction_isolation;

-- Use lower isolation level if full serializability isn't needed
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Deadlock Detected (40P01)

A deadlock occurs when two transactions each hold a lock the other needs, creating a circular wait. PostgreSQL automatically detects this after deadlock_timeout (default: 1 second) and kills one transaction.

-- ❌ Deadlock-prone pattern
-- Session 1
BEGIN;
UPDATE orders SET status = 'done' WHERE order_id = 1;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10; -- waits for Session 2

-- Session 2 (runs simultaneously)
BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10;
UPDATE orders SET status = 'done' WHERE order_id = 1; -- waits for Session 1
-- DEADLOCK! PostgreSQL rolls back one session.

-- ✅ Fix: Always access tables/rows in the same order
BEGIN;
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
SELECT * FROM inventory WHERE item_id = 10 FOR UPDATE;
UPDATE orders SET status = 'done' WHERE order_id = 1;
UPDATE inventory SET qty = qty - 1 WHERE item_id = 10;
COMMIT;

3. Aborted Transaction State

Any error inside a transaction block (constraint violation, syntax error, etc.) puts the transaction into an aborted state. All subsequent commands are ignored until a ROLLBACK is issued.

-- ❌ Wrong: continuing after an error
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'alice@example.com');
INSERT INTO users (id, email) VALUES (1, 'bob@example.com'); -- UNIQUE violation!
SELECT * FROM users; -- ERROR: current transaction is aborted

-- ✅ Right: use SAVEPOINT for partial rollback
BEGIN;
INSERT INTO users (id, email) VALUES (1, 'alice@example.com');

SAVEPOINT sp1;
INSERT INTO users (id, email) VALUES (1, 'bob@example.com'); -- fails
ROLLBACK TO SAVEPOINT sp1; -- recover without killing the whole transaction

INSERT INTO users (id, email) VALUES (2, 'bob@example.com'); -- succeeds
COMMIT;

Quick Fix Solutions

-- Monitor rollback rates — high ratio signals a problem
SELECT
    datname,
    xact_commit,
    xact_rollback,
    ROUND(xact_rollback::numeric /
        NULLIF(xact_commit + xact_rollback, 0) * 100, 2) AS rollback_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Find transactions currently waiting on locks
SELECT pid, usename, state, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Set timeouts to prevent long-running transactions
SET lock_timeout = '5s';
SET idle_in_transaction_session_timeout = '30s';

Prevention Tips

1. Always implement retry logic for 40001 and 40P01
Catch these specific SQLSTATE codes in your application layer and retry with exponential backoff. Most ORMs and database drivers support this natively.

2. Keep transactions short and access objects in a consistent order
Long-running transactions dramatically increase the chance of conflicts and deadlocks. Access tables and rows in the same order across all transactions, and avoid placing external I/O operations (HTTP calls, file writes) inside transaction blocks.

-- Enable lock wait logging for proactive monitoring
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';
SELECT pg_reload_conf();

Related Error Codes

Code	Name	Summary
40001	serialization_failure	Concurrent serializable transaction conflict
40P01	deadlock_detected	Circular lock wait between transactions
40002	transaction_integrity_constraint_violation	Integrity check failed mid-transaction
25006	read_only_sql_transaction	Write attempted in a read-only transaction

All of these belong to SQL error class 40, and the universal solution pattern is: detect → rollback → retry.

Oracle ORA-12021 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 14:01:27 +0000

ORA-12021: Materialized View Definition is Out of Date

ORA-12021 occurs when a Materialized View's definition becomes stale due to structural changes in the underlying base objects, such as tables or views. Oracle marks the Materialized View as invalid and throws this error when any query attempts to access it. This is a common error encountered after schema migrations or DDL changes in production environments.

Top 3 Causes

1. DDL Changes on the Base Table

When you run ALTER TABLE to add, drop, or modify a column on a table that a Materialized View depends on, Oracle immediately invalidates the Materialized View definition.

-- This ALTER TABLE will invalidate dependent Materialized Views
ALTER TABLE schema_name.sales_fact ADD (discount_rate NUMBER(5,2));

-- Check which Materialized Views are now affected
SELECT owner,
       mview_name,
       compile_state,
       staleness
FROM   dba_mviews
WHERE  compile_state != 'VALID';

2. Recompilation of Dependent Views or Objects

If a Materialized View is built on top of a regular view or another Materialized View, and that intermediate object is recompiled or invalidated, the ORA-12021 error will cascade upward through the dependency chain.

-- Check object dependencies to trace the root cause
SELECT referenced_owner,
       referenced_name,
       referenced_type,
       owner,
       name,
       type
FROM   dba_dependencies
WHERE  name  = 'MV_SALES_SUMMARY'
  AND  owner = 'SCHEMA_NAME';

-- Check overall object status
SELECT object_name,
       object_type,
       status
FROM   dba_objects
WHERE  owner       = 'SCHEMA_NAME'
  AND  object_type IN ('MATERIALIZED VIEW', 'VIEW')
  AND  status      = 'INVALID';

3. Failed or Interrupted Refresh Jobs

A Materialized View Refresh job that fails mid-execution or is forcibly terminated can leave the Materialized View in an inconsistent state, causing ORA-12021 on the next access.

-- Check last refresh details and current state
SELECT mview_name,
       last_refresh_date,
       last_refresh_type,
       compile_state,
       staleness,
       refresh_mode
FROM   dba_mviews
WHERE  owner = 'SCHEMA_NAME'
ORDER BY last_refresh_date DESC;

Quick Fix Solutions

Step 1: Perform a Complete Refresh

-- Refresh a single Materialized View
BEGIN
    DBMS_MVIEW.REFRESH(
        list          => 'SCHEMA_NAME.MV_SALES_SUMMARY',
        method        => 'C',
        atomic_refresh => FALSE
    );
END;
/

Step 2: Recompile the Materialized View

-- If refresh alone doesn't work, try recompiling
ALTER MATERIALIZED VIEW schema_name.mv_sales_summary COMPILE;

Step 3: Drop and Recreate if Necessary

-- Backup the DDL first
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_SALES_SUMMARY','SCHEMA_NAME')
FROM dual;

-- Drop and recreate
DROP MATERIALIZED VIEW schema_name.mv_sales_summary;

CREATE MATERIALIZED VIEW schema_name.mv_sales_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id,
       SUM(sales_amount) AS total_sales
FROM   schema_name.sales_fact
GROUP BY dept_id;

Step 4: Bulk fix all invalid Materialized Views

-- Generate recompile statements for all invalid MVs
SELECT 'ALTER MATERIALIZED VIEW ' || owner || '.' || mview_name || ' COMPILE;'
FROM   dba_mviews
WHERE  compile_state != 'VALID';

-- Or use UTL_RECOMP for full schema recompilation
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

Prevention Tips

1. Always check dependencies before DDL changes and automate post-change refresh:

-- Run this BEFORE any ALTER TABLE
SELECT name, type, owner
FROM   dba_dependencies
WHERE  referenced_name  = 'SALES_FACT'
  AND  referenced_owner = 'SCHEMA_NAME'
  AND  type             = 'MATERIALIZED VIEW';

2. Schedule regular automated Refresh jobs using DBMS_SCHEDULER:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_REFRESH_MV_DAILY',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''SCHEMA_NAME.MV_SALES_SUMMARY'',''C''); END;',
        repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0',
        enabled         => TRUE
    );
END;
/

By combining proactive dependency checks before schema changes with automated refresh scheduling and monitoring of DBA_MVIEWS.COMPILE_STATE, you can virtually eliminate ORA-12021 from your production environment.

Related Errors: ORA-12008 (error in Materialized View refresh path), ORA-32321 (MV log too old for fast refresh), ORA-12004 (fast refresh not supported), ORA-01031 (insufficient privileges during refresh).

PostgreSQL 3F000 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 10:02:53 +0000

PostgreSQL Error 3F000: invalid_schema_name — Causes, Fixes, and Prevention

PostgreSQL error 3F000: invalid_schema_name occurs when a SQL statement references a schema that does not exist in the current database or is specified in an invalid format. This commonly happens during application deployments, database migrations, or multi-tenant architecture setups where schema names are misconfigured. Understanding the root causes can save you significant debugging time in production environments.

Top 3 Causes

1. Nonexistent Schema in `search_path`

The most frequent cause is setting search_path to a schema that hasn't been created yet or was deleted without updating the configuration.

-- This will trigger 3F000 if 'myschema' doesn't exist
SET search_path TO myschema, public;

-- Check which schemas actually exist
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

-- Verify a specific schema exists before setting path
SELECT EXISTS (
    SELECT 1 FROM pg_namespace WHERE nspname = 'myschema'
) AS schema_exists;

2. Typo or Case Mismatch in Explicit Schema Reference

PostgreSQL schema names are case-sensitive when quoted. A simple typo or incorrect casing in a schema reference will throw this error immediately.

-- Wrong: schema name doesn't match what's in the catalog
SET search_path TO MySchema;   -- may not match 'myschema'

-- Correct: check exact name from catalog
SELECT nspname FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
  AND nspname != 'information_schema';

-- Safe dynamic schema usage with proper quoting
DO $$
DECLARE
    v_schema TEXT := 'tenant_42';
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = v_schema) THEN
        EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', v_schema);
    END IF;
    EXECUTE format('SET search_path TO %I, public', v_schema);
END;
$$;

3. Role-Level `search_path` Pointing to a Deleted Schema

When a role has a persistent search_path set via ALTER ROLE, and that schema is later dropped, every connection using that role will encounter this error.

-- Check role-level search_path configuration
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolname = 'app_user';

-- Fix: reset to a valid schema
ALTER ROLE app_user SET search_path TO app_schema, public;

-- Or reset to default
ALTER ROLE app_user RESET search_path;

-- Scope it to a specific database for finer control
ALTER ROLE app_user IN DATABASE mydb SET search_path TO app_schema, public;

Quick Fix Solutions

-- Step 1: Check current search_path
SHOW search_path;
SELECT current_schema();
SELECT current_schemas(true);  -- includes system schemas

-- Step 2: Create missing schema if needed
CREATE SCHEMA IF NOT EXISTS myschema;

-- Step 3: Reassign ownership if necessary
ALTER SCHEMA myschema OWNER TO app_user;

-- Step 4: Reset search_path at session level immediately
SET search_path TO myschema, public;

-- Step 5: Persist at database level
ALTER DATABASE mydb SET search_path TO myschema, public;

Prevention Tips

Validate schemas at application startup. Add a schema existence check as part of your application boot sequence or CI/CD pipeline to catch misconfigurations before they reach production.

-- Reusable schema validation function
CREATE OR REPLACE FUNCTION check_schema(p_schema TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = p_schema) THEN
        RAISE EXCEPTION 'Schema "%" not found', p_schema USING ERRCODE = '3F000';
    END IF;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Manage search_path centrally, not in application code. Set search_path at the database or role level using ALTER DATABASE or ALTER ROLE rather than hardcoding it in connection strings or application configs. This reduces drift between environments and makes schema changes easier to track.

Related Errors

42P01: undefined_table — Schema exists, but the referenced table does not.
42501: insufficient_privilege — Schema exists, but the user lacks access permissions.
2BP01: dependent_objects_still_exist — Thrown when dropping a schema that still contains objects; can lead to 3F000 if not handled cleanly.

Oracle ORA-12016 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 10:01:28 +0000

ORA-12016: materialized view does not include all primary key columns

ORA-12016 occurs when you attempt to create a Materialized View using the REFRESH FAST option, but the SELECT clause does not include all primary key columns of the base table. Oracle relies on primary key columns to track row-level changes in Fast Refresh operations, so any missing primary key column makes incremental synchronization impossible. This error most commonly appears when building MV-based reporting layers or replication setups.

Top 3 Causes

1. Missing Primary Key Columns in the SELECT Clause

The most frequent cause — especially with composite primary keys — is accidentally omitting one or more PK columns from the MV definition.

-- Check base table primary key columns first
SELECT cols.column_name, cols.position
FROM all_constraints cons
JOIN all_cons_columns cols
  ON cons.constraint_name = cols.constraint_name
WHERE cons.constraint_type = 'P'
  AND cons.table_name = 'ORDERS';

-- WRONG: ORDER_ID (PK) is missing
CREATE MATERIALIZED VIEW mv_orders
REFRESH FAST ON COMMIT
AS
SELECT order_date, customer_id, total_amount  -- ORA-12016 triggered
FROM orders;

-- CORRECT: All PK columns included
CREATE MATERIALIZED VIEW mv_orders
REFRESH FAST ON COMMIT
AS
SELECT order_id, order_date, customer_id, total_amount
FROM orders;

2. Primary Key Changed on Base Table Without Updating the MV

When a DBA alters the primary key of a base table (adds a new column to a composite PK, for example), existing Materialized Views that no longer reflect the full PK will fail on the next refresh cycle.

-- Find all MVs referencing a specific table
SELECT mview_name, owner, refresh_method
FROM all_mviews
WHERE mview_name IN (
    SELECT mview_name
    FROM all_mview_detail_relations
    WHERE detailobj_name = 'ORDERS'
);

-- Drop and recreate MV to include the updated PK
DROP MATERIALIZED VIEW mv_orders;

CREATE MATERIALIZED VIEW mv_orders
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT order_id,
       order_seq,   -- newly added PK column
       order_date,
       customer_id,
       total_amount
FROM orders;

3. Missing or Incorrectly Configured Materialized View Log

Fast Refresh requires a properly configured MV Log on the base table. If the log was created without the WITH PRIMARY KEY clause, Oracle cannot track row changes correctly, leading to ORA-12016.

-- Check existing MV log configuration
SELECT log_table, primary_key, rowid, sequence
FROM user_mview_logs
WHERE master = 'ORDERS';

-- Drop incorrect log and recreate properly
DROP MATERIALIZED VIEW LOG ON orders;

CREATE MATERIALIZED VIEW LOG ON orders
WITH PRIMARY KEY, ROWID, SEQUENCE
INCLUDING NEW VALUES;

-- Now create the MV
CREATE MATERIALIZED VIEW mv_orders
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT order_id, order_date, customer_id, total_amount
FROM orders;

-- Test the fast refresh
EXEC DBMS_MVIEW.REFRESH('MV_ORDERS', 'F');

Quick Fix Solutions

If Fast Refresh is not strictly required, switch to Complete Refresh as a quick workaround — it has no primary key requirement.

-- Complete Refresh workaround (no PK columns required)
CREATE MATERIALIZED VIEW mv_orders_complete
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT order_date, customer_id, total_amount
FROM orders;

-- Run a manual complete refresh
EXEC DBMS_MVIEW.REFRESH('MV_ORDERS_COMPLETE', 'C');

Prevention Tips

1. Always verify PK columns before creating an MV.
Run a pre-creation check to confirm every PK column of the base table is present in your SELECT clause. Integrate this into your deployment checklist or CI/CD pipeline.

-- Pre-creation validation query
SELECT a.column_name AS pk_column,
       CASE WHEN b.column_name IS NOT NULL
            THEN 'INCLUDED' ELSE 'MISSING' END AS mv_status
FROM (
    SELECT cols.column_name
    FROM all_constraints cons
    JOIN all_cons_columns cols
      ON cons.constraint_name = cols.constraint_name
    WHERE cons.constraint_type = 'P'
      AND cons.table_name = 'ORDERS'
) a
LEFT JOIN (
    SELECT column_name FROM all_mview_keys
    WHERE mview_name = 'MV_ORDERS'
) b ON a.column_name = b.column_name;

2. Enforce an MV impact analysis for any DDL change on base tables.
Before altering or dropping a primary key on any table, always query ALL_MVIEW_DETAIL_RELATIONS to identify dependent Materialized Views and plan updates accordingly. Making this a mandatory step in your change management process eliminates surprise failures in production.

Related Oracle Errors

ORA-12015 – Cannot create a fast-refreshable MV from a complex query.
ORA-23413 – Table does not have a Materialized View log.
ORA-12054 – ON COMMIT refresh option not supported for this MV.
ORA-32401 – MV log does not exist on the master table.

PostgreSQL 3D000 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 06:03:04 +0000

PostgreSQL Error 3D000: Invalid Catalog Name

PostgreSQL error code 3D000 — invalid_catalog_name — occurs when a client attempts to connect to a database (catalog) that does not exist in the PostgreSQL cluster. In PostgreSQL terminology, a "catalog" refers to a database, so this error essentially means "the database you're trying to connect to cannot be found." It is one of the most common connection-related errors and typically surfaces during application startup, migrations, or environment configuration changes.

Top 3 Causes and Fixes

Cause 1: The Database Simply Does Not Exist

The most straightforward cause — the target database was never created, was accidentally dropped, or the name has a typo (e.g., mydb vs my_db).

Diagnosis:

-- List all databases in the cluster
SELECT datname FROM pg_catalog.pg_database ORDER BY datname;

-- Check for close matches (case-insensitive)
SELECT datname FROM pg_catalog.pg_database WHERE datname ILIKE '%mydb%';

Fix:

-- Create the missing database
CREATE DATABASE mydb
    WITH
    OWNER = myuser
    ENCODING = 'UTF8'
    TEMPLATE = template0;

-- Verify creation
SELECT datname, datcollate FROM pg_database WHERE datname = 'mydb';

Cause 2: Misconfigured Connection String or Environment Variable

Applications using DATABASE_URL or framework-specific config files (settings.py, application.properties, database.yml) often pass a wrong or empty database name — especially in containerized environments where environment variables fail to inject correctly.

Diagnosis:

-- After connecting, verify which database you're actually on
SELECT current_database(), current_user;

-- Check active connections and their target databases (requires superuser)
SELECT pid, datname, usename, application_name, client_addr
FROM pg_stat_activity
WHERE datname IS NOT NULL;

Fix:
Validate your connection string format and test it directly:

-- Test via psql (run in terminal)
-- psql "postgresql://myuser:mypassword@localhost:5432/mydb"

-- Pre-flight existence check before app startup
SELECT CASE
    WHEN EXISTS (
        SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'mydb'
    ) THEN 'Database EXISTS'
    ELSE 'Database MISSING'
END AS status;

Cause 3: Wrong Database Specified in CLI Tools or Scripts

Using psql \c, pg_dump -d, or pg_restore -d with an incorrect database name triggers this error. Automated scripts with broken variable substitution often pass an empty string as the database name.

Diagnosis and Fix:

-- In psql, always list databases before switching
\l

-- Switch to the correct database
\c mydb

-- Defensive check in SQL scripts
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_catalog.pg_database WHERE datname = 'mydb'
    ) THEN
        RAISE EXCEPTION 'Database "mydb" not found. Aborting.';
    END IF;
END
$$;

For shell scripts, guard against empty variables:

-- Terminal guard example (bash):
-- DB_NAME="${DB_NAME:?ERROR: DB_NAME environment variable is not set}"
-- psql -U myuser -d "$DB_NAME" -c "SELECT current_database();"

Quick Prevention Tips

1. Add a pre-connection existence check to your startup logic.
Always query pg_catalog.pg_database to confirm the target database exists before your application boots or before running migration scripts. This turns a cryptic runtime crash into a clear, actionable error message.

-- Reusable existence check
SELECT COUNT(*) > 0 AS database_exists
FROM pg_catalog.pg_database
WHERE datname = current_setting('app.target_db', true);

2. Standardize and validate connection strings across environments.
Define a strict naming convention (e.g., appname_env → myapp_prod, myapp_staging) and enforce non-empty validation at the infrastructure level using secret managers (AWS Secrets Manager, HashiCorp Vault). Never allow a connection string with a blank or default placeholder database name to reach production.

Related Errors

Code	Name	When it occurs
`28000`	invalid_authorization_specification	Database exists but user access is denied
`08006`	connection_failure	Cannot reach the PostgreSQL server at all
`42P04`	duplicate_database	Trying to `CREATE DATABASE` with a name that already exists
`08001`	sqlclient_unable_to_establish_sqlconnection	Client-side wrapper error that often masks `3D000`

Oracle ORA-12015 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 06:01:38 +0000

ORA-12015: Cannot Create a Fast Refresh Materialized View from a Complex Query

ORA-12015 is thrown when you attempt to create a Materialized View with the REFRESH FAST option, but the underlying query contains constructs that Oracle cannot support for incremental (fast) refresh. Fast Refresh relies on Materialized View Logs to track row-level changes, and this mechanism only works with queries that follow strict structural rules. If your query is too complex — using UNION, analytic functions, or unsupported aggregate patterns — Oracle raises ORA-12015 and refuses to create the MV.

Top 3 Causes

1. UNION / UNION ALL or Set Operators in the Query

Oracle's Fast Refresh engine cannot track incremental changes when the query merges multiple result sets using UNION, UNION ALL, INTERSECT, or MINUS. Since changes must be mapped to a single source log, set operators make this impossible.

-- This will raise ORA-12015
CREATE MATERIALIZED VIEW mv_bad_union
REFRESH FAST ON DEMAND
AS
SELECT emp_id, emp_name FROM employees
UNION ALL
SELECT emp_id, emp_name FROM contractors;

-- Fix: Switch to COMPLETE REFRESH
CREATE MATERIALIZED VIEW mv_good_union
REFRESH COMPLETE ON DEMAND
START WITH SYSDATE NEXT SYSDATE + 1/24
AS
SELECT emp_id, emp_name FROM employees
UNION ALL
SELECT emp_id, emp_name FROM contractors;

2. Analytic Functions or Subqueries

Analytic functions such as RANK(), ROW_NUMBER(), LAG(), and LEAD() are evaluated over the entire result set, making row-level incremental tracking impossible. Similarly, subqueries in the SELECT or WHERE clause break the Fast Refresh requirement.

-- This will raise ORA-12015
CREATE MATERIALIZED VIEW mv_bad_rank
REFRESH FAST ON DEMAND
AS
SELECT
    emp_id,
    salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees;

-- Fix: Use COMPLETE REFRESH, or separate the analytic logic into a view
CREATE MATERIALIZED VIEW mv_emp_base
REFRESH FAST ON DEMAND
AS
SELECT emp_id, dept_id, salary FROM employees;

-- Wrap analytic function in a regular view on top of the MV
CREATE OR REPLACE VIEW v_emp_ranked AS
SELECT emp_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM mv_emp_base;

3. Missing MV Log or Incorrect Aggregate Structure

For aggregate-based Fast Refresh MVs, Oracle requires a Materialized View Log on the base table and COUNT(*) must be included in the SELECT list. Using AVG() without COUNT(*) or referencing columns not covered by the MV Log are common mistakes that trigger ORA-12015.

-- Step 1: Create the MV Log on the base table
CREATE MATERIALIZED VIEW LOG ON sales
WITH ROWID, SEQUENCE (product_id, amount)
INCLUDING NEW VALUES;

-- Step 2: Create the MV with COUNT(*) included (required for Fast Refresh)
CREATE MATERIALIZED VIEW mv_sales_agg
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
    product_id,
    COUNT(*)       AS total_rows,   -- mandatory for fast refresh
    SUM(amount)    AS total_amount,
    COUNT(amount)  AS cnt_amount    -- needed to derive AVG later
FROM sales
GROUP BY product_id;

Quick Fix Solutions

Situation	Fix
UNION / UNION ALL in query	Switch to `REFRESH COMPLETE`
Analytic functions present	Use `REFRESH COMPLETE` or separate into a plain view
No MV Log on base table	Run `CREATE MATERIALIZED VIEW LOG ON <table>`
Missing `COUNT(*)` in aggregate MV	Add `COUNT(*)` to SELECT list

Pre-validate before creating the MV:

-- Run explain to check fast refresh capability before creating
BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW(
        mv      => 'SELECT product_id, COUNT(*), SUM(amount) FROM sales GROUP BY product_id',
        stmt_id => 'MY_MV_TEST'
    );
END;
/

-- Check the results
SELECT capability_name, possible, msgtxt
FROM mv_capabilities_table
WHERE statement_id = 'MY_MV_TEST';

Prevention Tips

Always run DBMS_MVIEW.EXPLAIN_MVIEW before creating a Fast Refresh MV. This built-in procedure tells you exactly which part of your query prevents Fast Refresh, saving you from trial-and-error at deployment time.
Establish team coding standards for Materialized Views. Document which query patterns support Fast Refresh (simple joins, GROUP BY with COUNT(*), single-table selects) and which do not (UNION, subqueries, analytic functions, DISTINCT). Include an MV design checklist in your code review process to catch issues early.

Related Errors

ORA-12014 – MV Log missing PRIMARY KEY information needed for Fast Refresh.
ORA-23413 – No Materialized View Log exists on the base table.
ORA-32401 – MV Log does not include the required columns for the MV definition.

PostgreSQL 3B001 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 00:03:19 +0000

PostgreSQL Error 3B001: invalid savepoint specification

The 3B001 invalid savepoint specification error occurs in PostgreSQL when you attempt to reference a savepoint that does not exist within the current transaction using ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT. This typically means the savepoint was never created, has already been released, or was implicitly invalidated by a prior rollback. Understanding savepoint lifecycle management is essential for building robust transactional logic.

Top 3 Causes

1. Referencing a Savepoint That Was Never Created

The most common cause is a simple typo or logic error where the savepoint name used in ROLLBACK TO SAVEPOINT doesn't match any previously declared savepoint.

-- ❌ Incorrect: referencing a savepoint that was never created
BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (101, 5000);
ROLLBACK TO SAVEPOINT sp_order;  -- ERROR 3B001: savepoint "sp_order" does not exist
COMMIT;

-- ✅ Correct: always declare the savepoint first
BEGIN;
SAVEPOINT sp_order;
INSERT INTO orders (customer_id, amount) VALUES (101, 5000);

SAVEPOINT sp_items;
INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 201, 3);

-- Rollback only the item insert, keeping the order header
ROLLBACK TO SAVEPOINT sp_items;
COMMIT;

2. Referencing an Already Released Savepoint

Once you call RELEASE SAVEPOINT, that savepoint and all savepoints created after it are destroyed. Any subsequent reference to a released savepoint triggers 3B001.

-- ❌ Incorrect: referencing a savepoint after releasing it
BEGIN;
SAVEPOINT sp_first;
INSERT INTO products (name, price) VALUES ('Widget', 9900);

SAVEPOINT sp_second;
INSERT INTO inventory (product_id, stock) VALUES (1, 100);

RELEASE SAVEPOINT sp_second;  -- sp_second is now gone

ROLLBACK TO SAVEPOINT sp_second;  -- ERROR 3B001: already released
COMMIT;

-- ✅ Correct: only reference savepoints that are still active
BEGIN;
SAVEPOINT sp_first;
INSERT INTO products (name, price) VALUES ('Widget', 9900);

SAVEPOINT sp_second;
INSERT INTO inventory (product_id, stock) VALUES (1, 100);

-- Roll back to sp_first (sp_second is automatically invalidated)
ROLLBACK TO SAVEPOINT sp_first;

-- Do NOT reference sp_second anymore — it no longer exists
INSERT INTO products (name, price) VALUES ('Gadget', 14900);
COMMIT;

3. Savepoints Invalidated by a Prior Rollback

When you execute ROLLBACK TO SAVEPOINT sp_x, all savepoints created after sp_x are automatically removed. Referencing those invalidated savepoints afterward causes 3B001.

-- ❌ Incorrect: referencing a savepoint invalidated by a prior rollback
BEGIN;
SAVEPOINT sp_a;
INSERT INTO logs (msg) VALUES ('step A');

SAVEPOINT sp_b;
INSERT INTO logs (msg) VALUES ('step B');

SAVEPOINT sp_c;
INSERT INTO logs (msg) VALUES ('step C');

ROLLBACK TO SAVEPOINT sp_a;  -- sp_b and sp_c are now invalid

ROLLBACK TO SAVEPOINT sp_b;  -- ERROR 3B001: sp_b no longer exists
COMMIT;

-- ✅ Correct: use PL/pgSQL exception handling for nested savepoint logic
DO $$
BEGIN
    INSERT INTO logs (msg) VALUES ('step A');
    SAVEPOINT sp_a;

    BEGIN
        INSERT INTO logs (msg) VALUES ('step B');
        SAVEPOINT sp_b;

        INSERT INTO logs (msg) VALUES ('step C - may fail');

    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp_a;
        INSERT INTO logs (msg) VALUES ('recovered at sp_a');
    END;
END;
$$;

Quick Fix Solutions

Verify savepoint names match exactly: Savepoint names in PostgreSQL are case-folded to lowercase unless quoted. SAVEPOINT MyPoint and ROLLBACK TO SAVEPOINT mypoint are equivalent, but ROLLBACK TO SAVEPOINT "MyPoint" is not.
Track savepoint lifecycle: Maintain a stack-like structure in your application to track which savepoints are currently active before referencing them.
Use PL/pgSQL EXCEPTION blocks: Instead of manually managing ROLLBACK TO SAVEPOINT, let PL/pgSQL's built-in exception handling manage implicit savepoints for you.

-- Safe pattern using PL/pgSQL exception handling
CREATE OR REPLACE FUNCTION process_with_savepoint() RETURNS VOID AS $$
BEGIN
    INSERT INTO audit (event) VALUES ('started');
    SAVEPOINT sp_main;

    BEGIN
        -- Risky operation
        INSERT INTO transactions (amount) VALUES (-99999);
        -- Additional validation
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Insert failed';
        END IF;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK TO SAVEPOINT sp_main;
        INSERT INTO audit (event) VALUES ('rolled back: ' || SQLERRM);
    END;

    RELEASE SAVEPOINT sp_main;
END;
$$ LANGUAGE plpgsql;

Prevention Tips

1. Centralize savepoint name management: Define savepoint names as constants in your application layer or PL/pgSQL functions. Never scatter raw string literals for savepoint names across multiple code paths — this is the fastest route to a typo-induced 3B001.

2. Prefer PL/pgSQL exception blocks over manual savepoints: PostgreSQL's BEGIN...EXCEPTION...END block implicitly creates and manages savepoints for you. This eliminates the risk of referencing a stale or non-existent savepoint entirely, and is the recommended approach for most transactional stored procedures.

Related Errors

25P01 no_active_sql_transaction: Fired when SAVEPOINT is used outside a BEGIN block.
25P02 in_failed_sql_transaction: Occurs when any command other than ROLLBACK (or ROLLBACK TO SAVEPOINT) is executed in an aborted transaction.
3B000 invalid_savepoint_specification: The parent error class of 3B001, covering all savepoint specification violations.

Oracle ORA-12014 Error: Causes and Solutions Complete Guide

umzzil nng — Sat, 05 Sep 2026 00:01:29 +0000

ORA-12014: table does not contain a primary key constraint

ORA-12014 is an Oracle error that occurs when you attempt to create a Materialized View Log or set up a Fast Refresh Materialized View on a table that lacks a Primary Key constraint. Oracle's Fast Refresh mechanism relies on the primary key to uniquely identify and track changed rows, so without it, the operation is rejected entirely. This error is most commonly encountered in data warehousing and replication environments where Materialized Views are heavily used.

Top 3 Causes and Fixes

Cause 1: No Primary Key Defined on the Table

The most frequent cause — you're creating a Materialized View Log on a table that was never given a primary key during design.

-- Check existing constraints
SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'SALES_DATA';

-- Add a primary key
ALTER TABLE sales_data
ADD CONSTRAINT pk_sales_data PRIMARY KEY (sale_id);

-- Now create the Materialized View Log
CREATE MATERIALIZED VIEW LOG ON sales_data
WITH PRIMARY KEY
INCLUDING NEW VALUES;

-- Create the Fast Refresh Materialized View
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FAST ON COMMIT
AS
SELECT sale_id, SUM(amount) AS total_amount
FROM sales_data
GROUP BY sale_id;

Cause 2: Primary Key Exists but is DISABLED

A primary key was defined but was temporarily disabled for bulk data operations and never re-enabled.

-- Find disabled primary keys
SELECT constraint_name, status
FROM user_constraints
WHERE table_name = 'ORDERS'
  AND constraint_type = 'P';

-- Re-enable the primary key with validation
ALTER TABLE orders
ENABLE VALIDATE CONSTRAINT pk_orders;

-- Proceed to create the Materialized View Log
CREATE MATERIALIZED VIEW LOG ON orders
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Cause 3: One of the Join Tables is Missing a Primary Key

When building a Materialized View based on a multi-table join, all participating tables must have enabled primary keys for Fast Refresh to work.

-- Find all tables without an active primary key
SELECT table_name
FROM user_tables
WHERE table_name NOT IN (
    SELECT table_name
    FROM user_constraints
    WHERE constraint_type = 'P'
      AND status = 'ENABLED'
);

-- Add primary keys to all join tables
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

ALTER TABLE departments
ADD CONSTRAINT pk_departments PRIMARY KEY (department_id);

-- Create the Materialized View
CREATE MATERIALIZED VIEW mv_emp_dept
REFRESH FAST ON DEMAND
AS
SELECT e.employee_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Quick Fix: ROWID-Based Alternative

If adding a primary key is not immediately feasible, you can use a ROWID-based Materialized View Log as a workaround — though with some limitations.

-- Create a ROWID-based Materialized View Log
CREATE MATERIALIZED VIEW LOG ON legacy_table
WITH ROWID
INCLUDING NEW VALUES;

-- Create a ROWID-based Materialized View
CREATE MATERIALIZED VIEW mv_legacy
REFRESH FAST ON DEMAND
WITH ROWID
AS
SELECT col1, col2, col3
FROM legacy_table;

Prevention Tips

1. Enforce Primary Keys at Design Time

Make it a standard practice to always define a primary key for every table. Run the following query periodically to catch violators early:

SELECT owner, table_name
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND table_name NOT IN (
      SELECT table_name
      FROM dba_constraints
      WHERE constraint_type = 'P'
        AND status = 'ENABLED'
  );

2. Always Re-enable Constraints After Bulk Operations

If you must disable a primary key for performance reasons during a bulk load, always include the re-enable step in your script so it cannot be accidentally skipped:

-- Disable for bulk load
ALTER TABLE sales_data DISABLE CONSTRAINT pk_sales_data;

-- Bulk insert
INSERT INTO sales_data SELECT * FROM staging_table;

-- Always re-enable with validation
ALTER TABLE sales_data ENABLE VALIDATE CONSTRAINT pk_sales_data;

Related Errors

ORA-12015 – Cannot create a fast refresh MV from a complex query
ORA-12016 – MV does not include all primary key columns
ORA-02437 – Primary key violation when enabling a constraint with duplicate data
ORA-23413 – Table does not have a Materialized View Log

PostgreSQL 3B000 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 04 Sep 2026 14:03:30 +0000

PostgreSQL Error 3B000: Savepoint Exception — What It Means and How to Fix It

PostgreSQL error code 3B000 savepoint_exception occurs when your code attempts to reference, release, or roll back to a savepoint that doesn't exist or is being used outside a valid transaction block. Savepoints are intermediate markers within a transaction that allow partial rollbacks without aborting the entire transaction. This error most commonly appears in ORM-heavy applications, connection-pooled environments, or complex batch processing logic where transaction lifecycle management is automated or poorly controlled.

Top 3 Causes

1. Referencing a Savepoint That No Longer Exists

Once a savepoint is released with RELEASE SAVEPOINT or implicitly discarded after a full rollback, any further reference to it triggers 3B000. This is the single most common cause in production environments.

-- BROKEN: Attempting to rollback to an already-released savepoint
BEGIN;

SAVEPOINT my_sp;
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
RELEASE SAVEPOINT my_sp;   -- savepoint is now gone

-- This will throw ERROR 3B000
ROLLBACK TO SAVEPOINT my_sp;

COMMIT;

-- FIXED: Decide rollback vs release BEFORE releasing
BEGIN;

SAVEPOINT my_sp;

-- Check condition first, then act
-- On success:
RELEASE SAVEPOINT my_sp;

-- On failure (use one OR the other, not both):
-- ROLLBACK TO SAVEPOINT my_sp;
-- RELEASE SAVEPOINT my_sp;   -- always release after rollback

COMMIT;

2. Using SAVEPOINT Outside a Transaction Block

SAVEPOINT is only valid inside an explicit transaction block started with BEGIN. In autocommit mode or after a transaction has already been committed or rolled back, attempting to use a savepoint raises 3B000.

-- BROKEN: No BEGIN, autocommit mode is on
SAVEPOINT sp1;  -- ERROR 3B000: no active transaction

-- FIXED: Always wrap savepoints inside an explicit transaction
BEGIN;

SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('alice');
RELEASE SAVEPOINT sp1;

COMMIT;

-- Check current transaction state
SELECT
    txid_current() AS txid,
    current_setting('transaction_isolation') AS isolation;

3. ORM / Connection Pool Savepoint Conflicts

Frameworks like Django, SQLAlchemy, and Hibernate silently create and manage savepoints internally. When application code manually controls transactions on top of ORM-managed sessions, or when a connection pool recycles a session with a dirty transaction state, the expected savepoint structure no longer matches the actual database state — triggering 3B000.

-- Simulating what Django's atomic() does internally
BEGIN;

SAVEPOINT "django_savepoint_1";
INSERT INTO app_order (product_id, qty) VALUES (10, 2);
RELEASE SAVEPOINT "django_savepoint_1";

-- If manual code interferes here and issues ROLLBACK,
-- the ORM's next RELEASE will raise 3B000
-- because the savepoint was wiped by the full rollback

COMMIT;

-- Safe batch processing pattern with unique savepoint names
BEGIN;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id FROM pending_jobs LOOP
        EXECUTE format('SAVEPOINT sp_%s', rec.id);
        BEGIN
            UPDATE pending_jobs SET status = 'done' WHERE id = rec.id;
            EXECUTE format('RELEASE SAVEPOINT sp_%s', rec.id);
        EXCEPTION WHEN OTHERS THEN
            EXECUTE format('ROLLBACK TO SAVEPOINT sp_%s', rec.id);
            EXECUTE format('RELEASE SAVEPOINT sp_%s', rec.id);
            RAISE NOTICE 'Job % failed: %', rec.id, SQLERRM;
        END;
    END LOOP;
END;
$$;

COMMIT;

Quick Fix Solutions

Always release after rollback: After ROLLBACK TO SAVEPOINT, the savepoint still exists — release it explicitly before creating a new one with the same name.
Wrap in exception handlers: Use PL/pgSQL BEGIN ... EXCEPTION WHEN OTHERS blocks to ensure savepoints are always properly cleaned up.
Check autocommit settings: Verify your driver's autocommit mode (connection.autocommit = False in psycopg2) before using savepoints.
Avoid PgBouncer transaction mode with savepoints: Use pool_mode = session when your application relies on savepoints.

Prevention Tips

1. Use a helper procedure to encapsulate savepoint logic:

CREATE OR REPLACE PROCEDURE run_with_savepoint(sp_name TEXT, sql_cmd TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('SAVEPOINT %I', sp_name);
    BEGIN
        EXECUTE sql_cmd;
        EXECUTE format('RELEASE SAVEPOINT %I', sp_name);
    EXCEPTION WHEN OTHERS THEN
        EXECUTE format('ROLLBACK TO SAVEPOINT %I', sp_name);
        EXECUTE format('RELEASE SAVEPOINT %I', sp_name);
        RAISE;
    END;
END;
$$;

2. Monitor idle-in-transaction sessions to catch savepoint leaks early:

SELECT pid, usename, state, query, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < NOW() - INTERVAL '5 minutes';

Related Errors

Code	Name	Notes
`3B001`	`invalid_savepoint_specification`	Subcode of 3B000; invalid or non-existent savepoint name
`25P02`	`in_failed_sql_transaction`	Occurs when SQL runs after an unhandled error without rollback
`40001`	`serialization_failure`	Forces transaction abort; must be handled alongside savepoint retry logic
`25000`	`invalid_transaction_state`	Triggered when transaction state is invalid for the requested operation

Oracle ORA-12012 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 04 Sep 2026 14:01:47 +0000

ORA-12012: Error on Auto Execute of Job — Causes, Fixes & Prevention

What Is ORA-12012?

ORA-12012 is a wrapper error raised by Oracle's job execution engine (DBMS_JOB or DBMS_SCHEDULER) when an automatically scheduled job fails during execution. It rarely appears alone — you'll almost always see it accompanied by secondary errors like ORA-06512, ORA-01031, or ORA-01555 in the alert.log or trace files, which reveal the true root cause. Understanding ORA-12012 requires analyzing the full error stack, not just the top-level message.

Top 3 Causes

1. INVALID Stored Procedures or Packages Referenced by the Job

When a DDL change (e.g., adding/dropping a column) invalidates a stored procedure or package that a job depends on, the job will fail at runtime with ORA-12012. Oracle automatically invalidates dependent objects after DDL operations, so any job pointing to an INVALID object will immediately error out.

-- Check for INVALID objects in the job owner's schema
SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'SCOTT'
ORDER BY object_type, object_name;

-- Recompile an invalid procedure
ALTER PROCEDURE scott.my_job_proc COMPILE;

-- Bulk recompile an entire schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCOTT', compile_all => FALSE);

2. Insufficient Privileges on the Job Execution Account

Privileges granted via a Role are not visible inside stored procedures running with Definer's Rights. If the job's owner account relies on role-based privileges to access tables or packages, the job will fail with ORA-01031 (insufficient privileges), which surfaces as ORA-12012.

-- Check direct privileges granted to the job owner
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'SCOTT';

-- Grant privileges directly (not via role) to fix the issue
GRANT SELECT, INSERT, UPDATE ON hr.employees TO scott;
GRANT EXECUTE ON sys.dbms_lock TO scott;

-- Check if DBMS_JOB job is BROKEN due to repeated failures
SELECT job, what, broken, failures, last_date, next_date
FROM dba_jobs
WHERE broken = 'Y';

-- Re-enable a broken job
BEGIN
  DBMS_JOB.BROKEN(job => 101, broken => FALSE, next_date => SYSDATE);
  COMMIT;
END;
/

3. Unhandled Exceptions Inside the Job Logic

If the procedure called by the job raises an unhandled exception — such as a division by zero, NO_DATA_FOUND, or a data integrity issue — Oracle's job engine catches it and logs ORA-12012. In DBMS_JOB, repeated failures increment the FAILURES counter, and after 16 failures the job is automatically marked BROKEN and stops running.

-- Check recent job run failures (DBMS_SCHEDULER)
SELECT job_name, log_date, status, error#, additional_info
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
  AND log_date >= SYSDATE - 7
ORDER BY log_date DESC;

-- Example: Procedure with proper exception handling
CREATE OR REPLACE PROCEDURE scott.my_job_proc AS
  v_error_msg VARCHAR2(4000);
BEGIN
  -- Main business logic here
  INSERT INTO scott.job_log (run_date, status)
  VALUES (SYSDATE, 'RUNNING');
  COMMIT;

  -- ... actual work ...

EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := SQLERRM || CHR(10)
                   || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
    INSERT INTO scott.job_error_log (log_date, proc_name, error_msg)
    VALUES (SYSDATE, 'MY_JOB_PROC', v_error_msg);
    COMMIT;
    RAISE; -- Re-raise so the scheduler marks the job as FAILED
END my_job_proc;
/

Quick Fix Solutions

-- Re-enable a disabled DBMS_SCHEDULER job
BEGIN
  DBMS_SCHEDULER.ENABLE(name => 'SCOTT.MY_SCHEDULER_JOB');
END;
/

-- Manually run a DBMS_SCHEDULER job to test it
BEGIN
  DBMS_SCHEDULER.RUN_JOB(job_name => 'SCOTT.MY_SCHEDULER_JOB');
END;
/

-- View full error details for DBMS_SCHEDULER jobs
SELECT job_name, log_date, status, error#, additional_info
FROM dba_scheduler_job_run_details
WHERE job_name = 'MY_SCHEDULER_JOB'
ORDER BY log_date DESC
FETCH FIRST 10 ROWS ONLY;

Prevention Tips

1. Automate job failure monitoring. Set up a daily monitoring query or an OEM alert that checks DBA_SCHEDULER_JOB_RUN_DETAILS and DBA_JOBS for failures. Notify the DBA team when the failure count exceeds a threshold (e.g., 3 consecutive failures) before the job goes BROKEN.

2. Include INVALID object checks in every deployment. After any DDL change in production, always run DBMS_UTILITY.COMPILE_SCHEMA or utlrp.sql, and verify that zero INVALID objects remain before signing off on the deployment. Make this a mandatory step in your change management checklist.

Related Oracle Errors

Error Code	Description
ORA-06512	PL/SQL stack trace — shows exact line number of failure
ORA-01031	Insufficient privileges — common companion to ORA-12012
ORA-04068	Package state discarded — triggers ORA-12012 on next job run
ORA-01555	Snapshot too old — seen in long-running batch jobs
ORA-12011	Multiple jobs failed — similar to ORA-12012 but for batch failures

PostgreSQL 39P03 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 04 Sep 2026 10:03:10 +0000

PostgreSQL Error 39P03: event trigger protocol violated

PostgreSQL error code 39P03 (event_trigger_protocol_violated) occurs when an event trigger function fails to comply with the internal protocol that PostgreSQL enforces for event triggers. Unlike regular triggers, event trigger functions must return the special type event_trigger and must be registered exclusively via CREATE EVENT TRIGGER. This error typically surfaces at execution time when the engine detects a mismatch between what it expects from an event trigger function and what the function actually provides.

Top 3 Causes

1. Wrong Return Type on the Event Trigger Function

Event trigger functions must declare RETURNS event_trigger. Using RETURNS void, RETURNS trigger, or any other type will violate the protocol the moment PostgreSQL tries to invoke the function during a DDL event.

-- WRONG: Using RETURNS void
CREATE OR REPLACE FUNCTION bad_event_trigger()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'DDL happened';
END;
$$;

-- CORRECT: Must use RETURNS event_trigger
CREATE OR REPLACE FUNCTION good_event_trigger()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'DDL event: %, tag: %', tg_event, tg_tag;
END;
$$;

CREATE EVENT TRIGGER my_ddl_trigger
ON ddl_command_start
EXECUTE FUNCTION good_event_trigger();

2. Mixing Up Event Triggers and Regular Triggers

A common mistake is attaching an event trigger function (returning event_trigger) to a regular table trigger via CREATE TRIGGER, or vice versa. The internal calling conventions are completely different, and PostgreSQL will throw 39P03 when it detects the mismatch at runtime.

-- WRONG: Attaching an event trigger function to a regular trigger
-- CREATE TRIGGER bad_trigger
-- BEFORE INSERT ON my_table
-- FOR EACH ROW EXECUTE FUNCTION good_event_trigger(); -- Will fail!

-- CORRECT: Regular trigger uses RETURNS trigger
CREATE OR REPLACE FUNCTION regular_audit()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log(op, ts) VALUES (TG_OP, now());
    RETURN NEW;
END;
$$;

CREATE TRIGGER regular_audit_trigger
BEFORE INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION regular_audit();

-- CORRECT: DDL auditing uses an event trigger separately
CREATE OR REPLACE FUNCTION ddl_audit()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'DDL tag: %', tg_tag;
END;
$$;

CREATE EVENT TRIGGER ddl_audit_trigger
ON ddl_command_end
EXECUTE FUNCTION ddl_audit();

3. Non-PL/pgSQL Language Extensions Not Implementing the Protocol Correctly

When event trigger functions are written in C or procedural languages like PL/Python, the language handler must correctly implement the EventTriggerData structure and return protocol. Outdated or custom-built language extensions may not fully support this, leading to 39P03 at runtime.

-- Check all event triggers and their function details
SELECT
    et.evtname        AS trigger_name,
    et.evtevent       AS event,
    p.proname         AS function_name,
    l.lanname         AS language,
    p.prorettype::regtype AS return_type
FROM pg_event_trigger et
JOIN pg_proc p ON p.oid = et.evtfoid
JOIN pg_language l ON l.oid = p.prolang
ORDER BY et.evtname;

-- Disable a problematic trigger while you fix the function
ALTER EVENT TRIGGER problematic_trigger DISABLE;

-- After fixing, re-enable
ALTER EVENT TRIGGER problematic_trigger ENABLE;

Quick Fix Solutions

Verify return type — Query pg_proc to confirm the function returns event_trigger:

   SELECT proname, prorettype::regtype
   FROM pg_proc
   WHERE proname = 'your_function_name';
   -- Must show: event_trigger

Drop and recreate — If the return type is wrong, drop the event trigger first, fix the function, then recreate the trigger.
Rewrite in PL/pgSQL — If the issue is in a C or external-language function, rewriting it in PL/pgSQL is the safest path.

Prevention Tips

Add a pre-deployment validation query to your CI/CD pipeline that checks pg_event_trigger joined with pg_proc to ensure every event trigger function returns event_trigger. Fail the deployment automatically if any mismatch is found.
Standardize a team template for event trigger functions and enforce code review by a senior DBA for any trigger-related changes. Clearly document the difference between regular triggers and event triggers to prevent copy-paste mistakes.

Related Errors

Code	Name	Description
39P01	`trigger_protocol_violated`	Regular trigger function returned an invalid result
39P02	`srf_protocol_violated`	Set-returning function violated its calling protocol
42P13	`invalid_function_definition`	Function definition is invalid, often seen alongside return type mistakes

PostgreSQL 39P02 Error: Causes and Solutions Complete Guide

umzzil nng — Fri, 04 Sep 2026 06:03:27 +0000

PostgreSQL Error 39P02: srf protocol violated

The 39P02: srf protocol violated error occurs when a Set-Returning Function (SRF) — a function designed to return multiple rows — fails to follow PostgreSQL's internal protocol for producing result sets. This typically happens in C-language extensions, procedural language functions using RETURNS SETOF, or after major version upgrades when extensions are not recompiled against the new PostgreSQL binaries.

Top 3 Causes and Fixes

1. Incorrect C-Language SRF Implementation

C-based SRFs must use the SRF_IS_FIRSTCALL(), SRF_FIRSTCALL_INIT(), SRF_PERCALL_SETUP(), SRF_RETURN_NEXT(), and SRF_RETURN_DONE() macros in the correct order. Skipping any of these — especially forgetting SRF_RETURN_DONE() — triggers the protocol violation.

Diagnosis and Quick Fix:

-- Check all C-language set-returning functions in your database
SELECT
    n.nspname  AS schema,
    p.proname  AS function_name,
    p.probin   AS shared_library
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l  ON p.prolang = l.oid
WHERE p.proretset = true
  AND l.lanname = 'c'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Drop and recreate the broken function after fixing the C code
DROP FUNCTION IF EXISTS broken_srf_function();

CREATE OR REPLACE FUNCTION broken_srf_function()
RETURNS SETOF TEXT
AS '/path/to/fixed_extension.so', 'broken_srf_function'
LANGUAGE C STRICT;

2. Extension Incompatibility After Major Version Upgrade

After upgrading PostgreSQL (e.g., v14 → v15), C extensions compiled against the old version can violate the SRF protocol due to ABI changes. Always recompile or reinstall extensions against the new PostgreSQL version.

-- Check installed extension versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;

-- Attempt to update the extension in-place
ALTER EXTENSION your_extension_name UPDATE;

-- If update is not possible, reinstall
DROP EXTENSION IF EXISTS your_extension_name CASCADE;
-- (Install the correct OS package version first, then:)
CREATE EXTENSION your_extension_name;

-- Confirm server version to match against extension build
SELECT version();

3. Incorrect RETURNS SETOF in PL/pgSQL Functions

Using RETURN value instead of RETURN NEXT value inside a RETURNS SETOF function, or failing to call a final RETURN to signal completion, can trigger this error.

-- BAD: incorrect pattern
CREATE OR REPLACE FUNCTION bad_example()
RETURNS SETOF INTEGER
LANGUAGE plpgsql AS $$
BEGIN
    RETURN 1; -- Wrong! This does not follow SRF protocol
END;
$$;

-- GOOD: correct use of RETURN NEXT
CREATE OR REPLACE FUNCTION good_example_v1()
RETURNS SETOF INTEGER
LANGUAGE plpgsql AS $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 1..5 LOOP
        RETURN NEXT i;
    END LOOP;
    RETURN; -- explicit termination
END;
$$;

-- GOOD: correct use of RETURN QUERY
CREATE OR REPLACE FUNCTION good_example_v2(p_schema TEXT)
RETURNS SETOF TEXT
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
        SELECT tablename::TEXT
        FROM pg_tables
        WHERE schemaname = p_schema;
END;
$$;

-- GOOD: RETURNS TABLE pattern
CREATE OR REPLACE FUNCTION good_example_v3()
RETURNS TABLE(id INT, label TEXT)
LANGUAGE plpgsql AS $$
BEGIN
    id := 1; label := 'first';  RETURN NEXT;
    id := 2; label := 'second'; RETURN NEXT;
END;
$$;

-- Test the functions
SELECT * FROM good_example_v1();
SELECT * FROM good_example_v2('public');
SELECT * FROM good_example_v3();

Quick Prevention Tips

Always validate extensions before upgrading PostgreSQL.
Run pg_upgrade --check before any major version upgrade, and ensure all third-party extensions (PostGIS, TimescaleDB, etc.) have a version available for the target PostgreSQL release. Include SRF function regression tests in your CI/CD pipeline.

Prefer PL/pgSQL over C for custom SRFs when possible.
C-based SRFs are powerful but error-prone. If performance allows, implement SRFs in PL/pgSQL using RETURN NEXT or RETURN QUERY, which are far less likely to produce protocol violations. When C is necessary, strictly follow the SRF macro pattern and add unit tests that call the function in isolation.

Related Errors

39P01 – invalid_transaction_termination: improper transaction control inside a function context.
42P13 – invalid_function_definition: malformed function definition, often a root cause of SRF issues.
XX000 – internal_error: may appear alongside 39P02 in server logs when a C extension crashes.

DEV Community: umzzil nng

PostgreSQL 40000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 40000: Transaction Rollback — What It Is and How to Fix It

Top 3 Causes

1. Serialization Failure (40001)

2. Deadlock Detected (40P01)

3. Aborted Transaction State

Quick Fix Solutions

Prevention Tips

Related Error Codes

Oracle ORA-12021 Error: Causes and Solutions Complete Guide

ORA-12021: Materialized View Definition is Out of Date

Top 3 Causes

1. DDL Changes on the Base Table

2. Recompilation of Dependent Views or Objects

3. Failed or Interrupted Refresh Jobs

Quick Fix Solutions

Prevention Tips

PostgreSQL 3F000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 3F000: invalid_schema_name — Causes, Fixes, and Prevention

Top 3 Causes

1. Nonexistent Schema in search_path

2. Typo or Case Mismatch in Explicit Schema Reference

3. Role-Level search_path Pointing to a Deleted Schema

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-12016 Error: Causes and Solutions Complete Guide

ORA-12016: materialized view does not include all primary key columns

Top 3 Causes

1. Missing Primary Key Columns in the SELECT Clause

2. Primary Key Changed on Base Table Without Updating the MV

3. Missing or Incorrectly Configured Materialized View Log

Quick Fix Solutions

Prevention Tips

Related Oracle Errors

PostgreSQL 3D000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 3D000: Invalid Catalog Name

Top 3 Causes and Fixes

Cause 1: The Database Simply Does Not Exist

Cause 2: Misconfigured Connection String or Environment Variable

Cause 3: Wrong Database Specified in CLI Tools or Scripts

Quick Prevention Tips

Related Errors

Oracle ORA-12015 Error: Causes and Solutions Complete Guide

ORA-12015: Cannot Create a Fast Refresh Materialized View from a Complex Query

Top 3 Causes

1. UNION / UNION ALL or Set Operators in the Query

2. Analytic Functions or Subqueries

3. Missing MV Log or Incorrect Aggregate Structure

Quick Fix Solutions

Prevention Tips

Related Errors

PostgreSQL 3B001 Error: Causes and Solutions Complete Guide

PostgreSQL Error 3B001: invalid savepoint specification

Top 3 Causes

1. Referencing a Savepoint That Was Never Created

2. Referencing an Already Released Savepoint

3. Savepoints Invalidated by a Prior Rollback

Quick Fix Solutions

Prevention Tips

Related Errors

Oracle ORA-12014 Error: Causes and Solutions Complete Guide

ORA-12014: table does not contain a primary key constraint

Top 3 Causes and Fixes

Cause 1: No Primary Key Defined on the Table

Cause 2: Primary Key Exists but is DISABLED

Cause 3: One of the Join Tables is Missing a Primary Key

Quick Fix: ROWID-Based Alternative

Prevention Tips

Related Errors

PostgreSQL 3B000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 3B000: Savepoint Exception — What It Means and How to Fix It

Top 3 Causes

1. Referencing a Savepoint That No Longer Exists

2. Using SAVEPOINT Outside a Transaction Block

3. ORM / Connection Pool Savepoint Conflicts

Quick Fix Solutions

Prevention Tips

Related Errors

1. Nonexistent Schema in `search_path`

3. Role-Level `search_path` Pointing to a Deleted Schema