How to resolve "cannot execute insert in a read-only transaction" issue in PostgreSQL

This article guides you through the steps to resolve the issue “cannot execute insert in a read-only transaction” when creating a data source in Bold BI. This issue may occur if you have configured PostgreSQL as your data store database.

Issue Details

When creating a data source, you may encounter the following error: “Cannot execute Insert in a read-only transaction.” This issue is likely due to the read-only transaction being enabled for the database you are connecting to.

Cause

This issue was caused due to the read-only transaction being enabled for the database you are connecting.

Solution

To resolve this issue, please follow these steps:

1. First, it is recommended to verify the recovery mode by executing the command provided below. If the below query returns zero, Admin users need to resolve the recovery mode and then check the below query to see if default_transaction_read_only is enabled. If it is enabled, then execute the below command.

   select pg_is_in_recovery()
   

   If the above query returns zero, Admin users need to resolve the recovery mode and then check the below query to see if default_transaction_read_only is enabled. If it is enabled, then execute the below command.

2. Execute the following query on your PostgreSQL server:

   alter database <yourDB> set default_transaction_read_only = off;
   

   Replace <yourDB> with the name of your database.

3. Restart your Bold BI deployment.

4. Try creating the data source again.

By following these steps, you should be able to successfully create a data source without encountering the “Cannot execute Insert in a read-only transaction” error.

Related Links

• Data Store database settings
• PostgreSQL ALTER DATABASE Documentation
• Bold BI Documentation