This error occurs when PostgreSQL cannot determine whether a transaction was committed or rolled back due to a connection loss. It typically indicates a network disruption between your application and the database server.
The "Transaction resolution unknown" error (SQLSTATE 08007) belongs to PostgreSQL's Connection Exception class and signifies that the outcome of a transaction is indeterminate. This happens when the connection between your client application and the PostgreSQL server is severed before the database can report the final state of the transaction. The transaction may have been committed, rolled back, or left in an incomplete state, and your application cannot determine which. This is a critical error because it leaves your data in an uncertain state and requires explicit recovery actions.
Look at the PostgreSQL server logs to see if the transaction was actually committed or rolled back:
grep "transaction resolution unknown" /var/log/postgresql/postgresql.logOr in the PostgreSQL data directory:
tail -f /usr/local/var/postgres/server.logCheck the timestamp when the error occurred and look for COMMIT/ROLLBACK entries in that timeframe.
Query the database directly to check if your transaction was actually applied:
SELECT * FROM your_table WHERE id = your_transaction_id;If the data is present, the transaction committed despite the client error. If absent, it was rolled back. This tells you the actual state of your database and what recovery steps you need.
Orphaned transactions may still hold locks on tables. Find them with:
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';If you find processes in "idle in transaction" state, terminate them:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction' AND pid <> pg_backend_pid();This prevents locks from blocking other operations.
Update your application to retry failed transactions with exponential backoff:
async function executeTransactionWithRetry(
query: string,
maxRetries: number = 3
): Promise<any> {
for (let attempt = 1; attempt <= maxRetries; attempt++) {
try {
const result = await client.query(query);
return result;
} catch (error) {
if (error.code === '08007' && attempt < maxRetries) {
// Exponential backoff: 100ms, 200ms, 400ms
const delayMs = 100 * Math.pow(2, attempt - 1);
await new Promise(resolve => setTimeout(resolve, delayMs));
continue;
}
throw error;
}
}
}This gives transient network issues a chance to recover.
Configure your connection pool to handle disconnections gracefully:
// Using node-postgres with connection pool
const pool = new Pool({
connectionTimeoutMillis: 2000,
idleTimeoutMillis: 30000,
max: 20,
});
// For Prisma
prisma = new PrismaClient({
datasources: {
db: {
url: process.env.DATABASE_URL + "?connect_timeout=10",
},
},
});Proper timeout settings prevent connections from staying open indefinitely after a network issue.
Monitor for connection failures and set up alerts:
-- Run this periodically to check for problematic patterns
SELECT
datname,
count(*) as connection_count,
state
FROM pg_stat_activity
GROUP BY datname, state
ORDER BY connection_count DESC;Set up application-level logging to capture when error code 08007 occurs, including:
- Exact timestamp
- Transaction details
- Network conditions at the time
- Whether the transaction actually succeeded
This helps you identify patterns and root causes.
Error 08007 is particularly tricky because your application must handle two simultaneous uncertainties: the network failure itself AND the unknown transaction state. Modern connection pools and ORMs like Prisma handle some of this automatically, but you should still implement explicit recovery. For critical operations, consider using application-level idempotency keys so you can safely replay failed transactions without duplicating data. PostgreSQL does not support two-phase commit at the protocol level for client applications, so infrastructure-level solutions (connection pooling, circuit breakers, or service meshes) provide the best protection. When using transactions with savepoints, error 08007 will abort the entire transaction; you cannot recover just the savepoint without knowing the full transaction state.
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