The "Cache lookup failed for type" error occurs when PostgreSQL cannot find a data type with a specific OID (Object Identifier) in its internal system catalog cache. This typically happens after types or schemas have been dropped and recreated while connections remain active. The cache mismatch causes queries and functions to fail until connections are reset or the application is restarted.
PostgreSQL maintains an internal cache of data types mapped by their OID (Object ID). When a cached reference points to a type that no longer exists or has been recreated with a different OID, PostgreSQL throws error XX000: "Cache lookup failed for type". This is an internal catalog integrity issue. The error commonly occurs in three scenarios: (1) A type or schema was dropped and recreated while the application was running, leaving stale cache entries; (2) A database migration changed type definitions without properly invalidating cached connections; (3) A function references a type that was deleted after the function was compiled into the cache. Unlike syntax or constraint errors, this is a connection-level caching issue that persists across queries until the connection's cache is cleared.
The fastest resolution is to restart your application to clear all cached connections and type metadata:
# For web applications
sudo systemctl restart myapp
# or
kill $(pgrep -f "node.*app.js")
npm start
# For .NET applications
sudo systemctl restart dotnetapp
# or
iisresetThis forces all connections to reconnect and rebuild their type cache from the current database schema.
If restarting is not possible, clear the database connection pool:
For Node.js with pg or Sequelize:
const pool = new pg.Pool();
await pool.end(); // Drain all connections
const newPool = new pg.Pool(); // Create fresh connectionsFor .NET with Npgsql:
using var conn = new NpgsqlConnection(connectionString);
conn.Open();
conn.ReloadTypes(); // Refresh type cache
conn.Close();
// Clear the connection pool
NpgsqlConnection.ClearAllPools();For Python with psycopg2:
conn.close()
psycopg2.extensions.connection.isolation_level = psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT
conn = psycopg2.connect(dsn)Examine your migration logs to identify what types or schemas were modified:
SELECT version, description, installed_on FROM schema_migrations ORDER BY version DESC LIMIT 10;Look for migrations that:
- Dropped and recreated custom types or enums
- Modified type definitions without safe migrations
- Dropped and recreated schemas containing user types
For example, this pattern is dangerous:
-- Migration 001: Create enum
CREATE TYPE status_enum AS ENUM ('active', 'inactive');
-- Migration 002: Unsafe - creates a new OID
DROP TYPE status_enum;
CREATE TYPE status_enum AS ENUM ('active', 'inactive', 'pending');Safer approach:
-- Instead of dropping, add the value
ALTER TYPE status_enum ADD VALUE 'pending';If the problem continues after restarting, the database catalog may be corrupted. Perform a clean backup and restore:
# On the source server
pg_dumpall > backup.sql
# Stop PostgreSQL
sudo systemctl stop postgresql
# Remove corrupted data directory
sudo rm -rf /var/lib/postgresql/15/main
# Reinitialize the database cluster
sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main
# Start PostgreSQL
sudo systemctl start postgresql
# Restore from backup
psql -f backup.sqlNote: This is a last resort. Make sure you have verified backups before attempting this.
If you recently upgraded PostgreSQL, check for version-specific type OID changes:
psql --version
psql -U postgres -c "SELECT version();" # Check server versionIf upgrading (e.g., from PostgreSQL 12 to 15), always follow the official upgrade procedure:
# Use pg_upgrade for a faster upgrade
pg_upgrade --old-datadir /var/lib/postgresql/12/main --new-datadir /var/lib/postgresql/15/mainDo not simply restore a dump from an older version and expect it to work perfectly.
Deep Dive: Type OID Mapping and Caching: PostgreSQL internally identifies every data type by a 32-bit integer OID. When a client connects, the driver (psycopg2, Npgsql, pg, etc.) caches OID-to-type mappings. If a type is dropped and recreated, PostgreSQL assigns a new OID, but cached connections still reference the old one. This mismatch causes the "cache lookup failed" error. In Npgsql specifically, you can call conn.ReloadTypes() to force a refresh, but the underlying issue is the stale OID. For critical systems, use proper migration tools (Flyway, Liquibase, Alembic) that handle type changes safely. When modifying enums, use ALTER TYPE...ADD VALUE instead of DROP/CREATE. For other types, create a new type with a different name, migrate data, then drop the old one. Connection pools must be drained between schema changes in production environments. Monitor your migration logs and test schema changes thoroughly in staging before deploying to production.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL