How to fix No active SQL transaction in PostgreSQL

BEGIN;  -- Start the transaction
SELECT * FROM users WHERE id = 1;
UPDATE users SET name = 'Alice' WHERE id = 1;
COMMIT; -- End the transaction

-- NO BEGIN STATEMENT
SELECT * FROM users;
COMMIT;  -- ERROR: 25P01 - No active SQL transaction

BEGIN;

-- Outer transaction is active
SAVEPOINT sp1;
UPDATE table1 SET col1 = 'value';
ROLLBACK TO sp1;

COMMIT;  -- This works because the outer transaction is still active

SAVEPOINT sp1;  -- ERROR: 25P01 - No outer transaction
UPDATE table1 SET col1 = 'value';

// Sequelize
await sequelize.transaction(async (transaction) => {
  await User.update({ name: 'Alice' }, { where: { id: 1 }, transaction });
  // COMMIT is automatic when the callback completes
});

BEGIN;
UPDATE users SET id = 1 WHERE id = 999;  -- Might fail
-- If an error occurred, the transaction is now ABORTED
-- You MUST ROLLBACK before starting a new transaction
ROLLBACK;  -- Always roll back on error

-- Now you can start a new transaction
BEGIN;
-- Try again or take a different action
COMMIT;

try {
  const client = await pool.connect();
  await client.query('BEGIN');

  await client.query('UPDATE users SET name = $1 WHERE id = $2', ['Alice', 1]);

  await client.query('COMMIT');
  client.release();
} catch (error) {
  console.error('Transaction failed:', error.message);
  // ROLLBACK is implicit when the connection is released on error
  // Or explicitly:
  // await client.query('ROLLBACK');
}

# Update to the latest version
composer update laravel/framework

DB::transaction(function () {
  // Outer transaction
  DB::transaction(function () {
    // Inner transaction (savepoint in PostgreSQL)
    User::create(['name' => 'Alice']);
  });
});

await sequelize.transaction(async (t) => {
  await User.create({ name: 'Alice' }, { transaction: t });
});

-- PostgreSQL has autocommit ON by default
-- (each statement auto-commits unless in a transaction block)
-- To see current settings:
SHOW autocommit;

// Node.js with pg
const client = await pool.connect();

try {
  // Explicitly start a transaction
  await client.query('BEGIN');

  // Your operations here
  await client.query('UPDATE users SET name = $1', ['Alice']);

  // Explicitly commit
  await client.query('COMMIT');
} catch (error) {
  // Explicitly rollback on error
  await client.query('ROLLBACK');
} finally {
  client.release();
}

// Node.js with pg - proper connection pooling
const pool = new Pool({
  connectionTimeoutMillis: 5000,   // 5 second connection timeout
  idleTimeoutMillis: 30000,        // 30 second idle timeout
  max: 20,                          // Max 20 connections
});

// Always release connections when done
const client = await pool.connect();
try {
  await client.query('BEGIN');
  // ... your queries
  await client.query('COMMIT');
} finally {
  client.release();  // CRITICAL: Always release
}

pool.on('error', (error, client) => {
  console.error('Unexpected error on idle client', error);
  // The client is no longer usable - it will be removed from the pool
});