how can I use prepared transactions?

[massivefermion]

I'm working on a project where I need to implement distributed transactions. After some research, we came to the conclusion that we should use postgresql's prepared transactions.
But the problem is, begin does not support prepared transactions. So I tried, e.g:

sql.unsafe(`begin; insert into users (username) values (${username}); prepare transaction 'insert_user';`)

But this gives the following error:

Uncaught Error: UNSAFE_TRANSACTION: Only use sql.begin or max: 1

Is it possible to use prepared transactions at all? If yes, how?
Thanks

[porsager]

Right, that's prevented by the current safety check..

Would you mind trying it out using the unreleased reserve feature #603 (comment)

You can use the latest by installing from github with npm install porsager/postgres

Your code would look like this then:

const reserved = await sql.reserve()
await reserved`begin`
await reserved`insert into users (username) values (${ username })`
await reserved`prepare transaction 'insert_user'`
reserved.release()

[massivefermion]

I installed the reserve branch with npm install porsager/postgres#reserve and it worked.
When will it be released?
Thanks

P.S.
Is it possible to modify begin in a way that if you pass e.g. { prepare: true } to it, it prepares the transaction instead of committing it right away?

[massivefermion]

There seems to be a problem. This is fine:

await reserved`prepare transaction 'insert_user'`

But this:

await reserved`prepare transaction ${txName}`

throws the following error:

PostgresError: syntax error at or near "$1"

I also tried:

await reserved`prepare transaction ${reserved(txName)}`

But I get"

PostgresError: syntax error at or near ""{txName}""

[porsager]

Yes, that's because the name is not a value, so you can't use it as a parameter. You need to do like with dynamic table or column names, so put sql() around the value. (check the docs for more)

[porsager]

@massivefermion we need the name, so I'm thinking it would be cool to simply have sql.prepare(name) available like sql.savepoint(), which results in no implicit commit if called

[massivefermion]

We're encountering another problem. We need a way to gracefully handle errors caused by queries in a transaction, so right now we have something like:

const txName = 'txName'
const reserved = await sql.reserve()

try {
  await reserved`begin`
  await reserverd`insert into users (username) values (${ username })`
} finally {
  await reserved.unsafe(`prepare transaction '${ txName }'`)
  reserved.release()
}

(We're using unsafe for the prepare part because reserved(tXName) or sql(txName) didn't work, I still get the error PostgresError: syntax error at or near ""{txName}"")

Anyway, we intentionally gave the wrong table name in the insert part to induce an error. But this happens:

/node_modules/postgres/cjs/src/index.js:228
? q.push(q)
^
TypeError: q.push is not a function
at Query.handler /node_modules/postgres/cjs/src/index.js:228:13)
at Query.handle (/node_modules/postgres/cjs/src/query.js:141:65)
at processTicksAndRejections (node:internal/process/task_queues:95:5)

We put the prepare statement in the finally part because otherwise the transaction would stay open after the error.

[porsager]

Thanks a lot @massivefermion - That looks like a bug in the reserve feature, I'll dig into it.

[shayan-shojaei]

I've created the #628 PR and added the functionality to create prepared transactions using the beginPrepared function.
I'm working on the same project as @massivefermion and this function resolved our issue.