How to turn off implicit transactions on Postgre?

Databases
1

Is it possible to turn off implicit transactions using the PostgreSQLDatabase plugin? I can’t create a database in code without this.

2

is it what you’re looking for ?

When autocommit is on (the sane default in psql), you can “turn it off” by starting a transaction manually with BEGIN .

BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEGIN ), PostgreSQL executes transactions in “autocommit” mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

1 Like
3

You can make it explicit using pure Xojo as

BeginTransaction() , CommitTransaction(), RollbackTransaction()

Try
  DB.BeginTransaction
  DB.ExecuteSQL("CREATE TABLE AddressBook name VARCHAR, email VARCHAR")
  DB.CommitTransaction
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try

https://documentation.xojo.com/api/databases/postgresqldatabase.html#postgresqldatabase-begintransaction

4

Thanks for the replies, however Postgres will not allow creating a database within a transaction. The error message I get is “ERROR: CREATE DATABASE cannot run inside a transaction block”.

5

I have a workaround. I was creating the database & setting permissions in a single executeSQL statement, so 4 SQL commands. By executing the create database command by itself I don’t get the error & I can then execute the other 3 commands as a group.

2 Likes