Articles in this section
Category / Section

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

Published: Jun 23, 2023

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.

image.png

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

Was this article useful?
Like
Dislike
Help us improve this page
Please provide feedback or comments
SM
Written by Siranjeevi Murugan
Updated May 15, 2024
Comments (1)
Please  to leave a comment
DS
Devendran S
Access denied
Access denied