How to fix Transaction resolution unknown in PostgreSQL

grep "transaction resolution unknown" /var/log/postgresql/postgresql.log

tail -f /usr/local/var/postgres/server.log

SELECT * FROM your_table WHERE id = your_transaction_id;

SELECT pid, usename, state, query 
FROM pg_stat_activity 
WHERE state = 'idle in transaction';

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction' AND pid <> pg_backend_pid();

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;
    }
  }
}

// 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",
    },
  },
});

-- 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;