arbitrary UNSAFE_TRANSACTION errors when using sql.begin
Author: JuHwonCreated Mar 9, 2024Updated Sep 1, 2026
The following query arbitrary causes an UNSAVE_TRANSACTION error.
await this.sql.begin(async (tsql) => {
await tsql`DELETE
FROM mapping_table
WHERE a_id = ${aId}`;
if (items.length === 0) {
return;
}
await tsql`INSERT INTO mapping_table ${
// eslint-disable-next-line @ts-safeql/check-sql
tsql(items, 'a_id', 'b_id')
}`;
});the error most likely happens when this code is executed very frequently and items is usually an array betwenn 0 and a few thousand items.
error:
Error: UNSAFE_TRANSACTION: Only use sql.begin, sql.reserved or max: 1
at CommandComplete (/Users/juhwon/Projects/xx/xx/node_modules/postgres/cjs/src/connection.js:580:29)
at handle (/Users/juhwon/Projects/xx/xx/node_modules/postgres/cjs/src/connection.js:476:6)
at Socket.data (/Users/juhwon/Projects/xx/xx/node_modules/postgres/cjs/src/connection.js:315:9)
at Socket.emit (node:events:514:28)
at addChunk (node:internal/streams/readable:376:12)
at readableAddChunk (node:internal/streams/readable:349:9)
at Socket.Readable.push (node:internal/streams/readable:286:10)
at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
at TCP.callbackTrampoline (node:internal/async_hooks:130:17)to my understanding using sql.begin for something like this should be fine. have we found a bug here or is there something wrong with our code?
Source: porsager/postgres