如何在pg-promise中捕获连接关闭事件并中断长事务回滚？

Let's walk through how to modify your pg-promise transaction to roll back automatically when the client closes the connection (like when the user navigates away or closes the tab). First, let's fix a critical security issue in your original code, then add the connection close handling.

Step 1: Fix SQL Injection Vulnerabilities #

Your original code uses string interpolation for SQL values, which is a huge SQL injection risk. Always use pg-promise's parameterized queries instead—they're safer and more reliable:

// Replace this unsafe string interpolation
var file = await t.one(`insert into ui.user_datasets_files (user_dataset_id,filename) values (${itemId},'${fileName}') RETURNING id`);

// With this parameterized version
var file = await t.one(
  'INSERT INTO ui.user_datasets_files (user_dataset_id, filename) VALUES ($1, $2) RETURNING id',
  [itemId, fileName]
);

Step 2: Add Connection Close Listener to Cancel Transaction #

pg-promise's transaction object (t) has a built-in cancel() method that immediately terminates the transaction and triggers a rollback. We'll attach a listener to the req.close event to call this method, and clean up the listener once the transaction completes to avoid memory leaks.

Here's the full modified code:

db.tx(async t => {
  // Insert file record with safe parameterized query
  const file = await t.one(
    'INSERT INTO ui.user_datasets_files (user_dataset_id, filename) VALUES ($1, $2) RETURNING id',
    [itemId, fileName]
  );

  // Prepare batch insert data
  const data = rows.map(row => ({
    user_dataset_id: itemId,
    file_id: file.id,
    json_data: JSON.stringify(row)
  }));

  // Create batch insert query using pg-promise helpers
  const insert = pgPromise.helpers.insert(data, dataset_data_columns);

  // Define connection close handler to cancel the transaction
  const handleConnectionClose = () => {
    console.log('Client connection closed - rolling back transaction');
    t.cancel('Connection closed by client');
  };
  req.on('close', handleConnectionClose);

  try {
    // Execute the long-running batch insert
    await t.none(insert);
    return;
  } finally {
    // Clean up the listener to prevent memory leaks
    req.off('close', handleConnectionClose);
  }
})
.then(() => callback())
.catch(err => {
  // The cancellation error (and any other transaction errors) will land here
  callback(err);
});

Key Details: #

• t.cancel(): This tells pg-promise to abort the transaction immediately, which triggers a ROLLBACK on the PostgreSQL side—undoing all changes made in the transaction scope.
• Listener Cleanup: The finally block ensures we remove the close listener once the transaction finishes (success or failure). This prevents orphaned listeners from cluttering up memory in long-running servers.
• Error Handling: When the transaction is cancelled, the catch block receives the error message we passed to t.cancel(), which you can pass directly to your callback to notify the caller of the connection issue.

Why This Works #

When the client closes the connection, the req.close event fires, and we call t.cancel(). PostgreSQL terminates any ongoing queries for that transaction and rolls back all prior changes. The catch block catches this cancellation error, and your callback gets notified of the connection close problem.

内容的提问来源于stack exchange，提问作者Akram Kamal Qassas