Error cannot execute create function in a read only transaction

I'm trying to setup the pgexercises data in my local machine. When I run: psql -U -f clubdata.sql -d postgres -x I get the error: psql:clubdata.sql:6: ERROR: cannot execute CREATE

I’m trying to setup the pgexercises data in my local machine. When I run: psql -U <username> -f clubdata.sql -d postgres -x I get the error: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction.

Why did it create a read-only database on my local machine? Can I change this?

asked Jul 2, 2015 at 13:48

ltrainpr's user avatar

3

Normally the most plausible reasons for this kind of error are :

  • trying create statements on a read-only replica (the entire instance is read-only).

  • <username> has default_transaction_read_only set to ON

  • the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>.
And it wouldn’t work if any of the reasons above was directly applicable.

One possibility that would technically explain this would be that default_transaction_read_only would be ON in the postgresql.conf file, and set to OFF for the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

answered Jul 2, 2015 at 16:32

Daniel Vérité's user avatar

Daniel VéritéDaniel Vérité

56.1k15 gold badges125 silver badges150 bronze badges

1

Reached out to pgexercises.com and they were able to help me.

I ran these commands(separately):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Then I dropped the database from the terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

answered Jul 4, 2015 at 4:45

ltrainpr's user avatar

ltrainprltrainpr

2,9653 gold badges28 silver badges40 bronze badges

1

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage!

Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don’t know if the same effect would happen if I was on a dedicated server.

alecxe's user avatar

alecxe

455k116 gold badges1061 silver badges1180 bronze badges

answered Jan 15, 2019 at 4:52

Brad Mathews's user avatar

Brad MathewsBrad Mathews

1,4532 gold badges20 silver badges45 bronze badges

1

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

answered Jun 16, 2021 at 16:22

Bharathiraja's user avatar

BharathirajaBharathiraja

6441 gold badge11 silver badges20 bronze badges

In my case I had a master and replication nodes, and the master node became replication node, which I believe switched it into hot_standby mode. So I was trying to write data into a node that was meant only for reading, therefore the «read-only» problem.

You can query the node in question with SELECT pg_is_in_recovery(), and if it returns True then it is «read-only», and I suppose you should switch to using whatever master node you have now.

I got this information from: https://serverfault.com/questions/630753/how-to-change-postgresql-database-from-read-only-to-writable.
So full credit and my thanks goes to Craig Ringer!

answered May 6, 2021 at 9:12

vladimirror's user avatar

vladimirrorvladimirror

65014 silver badges7 bronze badges

Dbeaver: In my case
enter image description here

This was on.

answered Oct 4, 2022 at 4:00

Utkarsh's user avatar

This doesn’t quite answer the original question, but I received the same error and found this page, which ultimately led to a fix.

My issue was trying to run a function with temp tables being created and dropped. The function was created with SECURITY DEFINER privileges, and the user had access locally.

In a different environment, I received the cannot execute DROP TABLE in a read-only transaction error message. This environment was AWS Aurora, and by default, non-admin developers were given read-only privileges. Their server connections were thus set up to use the read-only node of Aurora (-ro- is in the connection url), which must put the connection in the read-only state. Running the same function with the same user against the write node worked.

Seems like a good use case for table variables like SQL Server has! Or, at least, AWS should modify their flow to allow temp tables to be created and dropped on read nodes.

answered May 7, 2020 at 15:35

ps2goat's user avatar

ps2goatps2goat

7,8971 gold badge33 silver badges68 bronze badges

This occurred when I was restoring a production database locally, the database is still doing online recovery from the WAL records.

A little bit unexpected as I assumed pgbackgrest was creating instantly recoverable restores, perhaps not.

 91902 postgres  20   0 1445256  14804  13180 D   4.3  0.3   0:28.06 postgres: startup   recovering 000000010000001E000000A5  

answered Dec 6, 2019 at 15:28

Kevin Parker's user avatar

Kevin ParkerKevin Parker

16.8k20 gold badges76 silver badges103 bronze badges

If like me you are trying to create DB on heroku and are stuck as this message shows up on the dataclip tab

I did this,

Choose Resources from(Overview Resources Deploy Metrics Activity Access Settings)
Choose Settings out of (Overview, Durability, Settings, Dataclip)
Then in Administration->Database Credentials choose View Credentials...

then open terminal and fill that info here and enter

psql --host=***************.amazonaws.com --port=5432 --username=*********pubxl --password --dbname=*******lol

then it’ll ask for password, copy-paste from there and you can run Postgres cmds.

answered May 27, 2021 at 18:42

shekhar g h's user avatar

shekhar g hshekhar g h

1,1751 gold badge9 silver badges12 bronze badges

I suddenly started facing this error on postgres installed on my windows machine, when I was running alter query from dbeaver, all I did was deleted the connection of postgres from dbeaver and created a new connection

answered Apr 8, 2022 at 9:55

Muhammad Awais's user avatar

Muhammad AwaisMuhammad Awais

1,3501 gold badge17 silver badges20 bronze badges

I just had this error. My cause was not granting permission to the SEQUENCE

GRANT ALL ON SEQUENCE word_mash_word_cube_template_description_reference_seq TO ronshome_user;

answered Oct 13, 2022 at 16:18

Ron Piggott's user avatar

Ron PiggottRon Piggott

7031 gold badge8 silver badges26 bronze badges

If you are facing this issue with an RDS instance cluster, please check your endpoint and use the Writer instance endpoint. Then it should work now.

answered Nov 23, 2022 at 11:46

Aditya Y's user avatar

Aditya YAditya Y

5015 silver badges9 bronze badges

Skip to content

In this article, we will see the solution for error like PostgreSQL Error: cannot execute in a read-only transaction.

ERROR: cannot execute CREATE DATABASE in a read-only transaction Error: cannot execute DROP DATABASE in a read-only transaction ERROR: cannot execute CREATE TABLE in a read-only transaction Error: cannot execute DROP TABLE in a read-only transaction cannot execute INSERT in a read-only transaction

PostgreSQL Error cannot execute in a read-only transaction


Reason:

Reason is simple, PostgreSQL server is recovery(read-only) or standby mode.

Resolution:

1. We have to remove recovery(read-only) or standby mode.

2. Go to your data or main directory then remove standby.signal and recovery.signal files.

Before performing following action, take backup of these files in some other location.

File location in(Ubuntu change your respective version nuymber)

/var/lib/postgresql/13/main

3. After removing signal files, restart PostgreSQL cluster.

sudo systemctl restart postgresql

4. Then, you will able to connect to the server and perform all actions as usual.

You could always double check your problem by issuing a command such as

/usr/bin/pgsql/bin/createdb test

You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.

But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. You can do this by either

  • rebooting the server or

  • reloading the .conf file or

  • change on the client side

From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.

I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).

For a system reboot,

pgctl restart

To reload, you could also try these commands (from here — or check the documentation page given above).

Option 1: From the command-line shell

su - postgres /path/to/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

You can verify that the setting has «taken» from the table pg_settings. Also of interest is this page.

For the default_transaction_read_only parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters — reboot, reload or client.

test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | on
(1 row)

test=# 
test=# `SET default_transaction_read_only = OFF;`
SET
test=# 
test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | off
(1 row)

test=# 
test=# SET default_transaction_read_only = ON;
SET

Normally the most plausible reasons for this kind of error are :

  • trying create statements on a read-only replica (the entire instance is read-only).

  • <username> has default_transaction_read_only set to ON

  • the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>.
And it wouldn’t work if any of the reasons above was directly applicable.

One possibility that would technically explain this would be that default_transaction_read_only would be ON in the postgresql.conf file, and set to OFF for the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

Reached out to pgexercises.com and they were able to help me.

I ran these commands(separately):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Then I dropped the database from the terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage! Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don’t know if the same effect would happen if I was on a dedicated server.

Здравствуйте! Настроил реплику БД postgres по инструкции https://www.8host.com/blog/replikaciya-baz-dannyx-…

Теперь возникла необходимость создать на Slave машине в той же БД таблицу, в которую slave мог бы делать инсёрты. Как это возможно? При попытке создать таблицу, получаю ошибку:

ERROR: cannot execute CREATE TABLE in a read-only transaction

P.S. На мастер сервер эта таблица не должна передаваться. Она нужна только локально на slave машине.


  • Вопрос задан

    более трёх лет назад

  • 526 просмотров

Пригласить эксперта

На slave это сделать нельзя.

На slave это сделать нельзя.

Потому что это slave база.
Придётся вам завести на слейве дополнительную БД, в которую будет писать Слейв.


  • Показать ещё
    Загружается…

09 февр. 2023, в 15:13

2000 руб./за проект

09 февр. 2023, в 15:06

2000 руб./за проект

09 февр. 2023, в 15:02

12000 руб./за проект

Минуточку внимания

ok , I provide a test code to https://github.com/xionglihdfs/jpa.git.

Thanks,

The table builds statement

CREATE TABLE "public"."ecs_users" ( "user_id" int4 DEFAULT NULL, "user_name" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "password" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "email" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "real_name" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "avatar" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "source" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "sex" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "birthday" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "address_id" int4 DEFAULT NULL, "reg_time" date DEFAULT NULL, "last_login" date DEFAULT NULL, "last_time" date DEFAULT NULL, "last_ip" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "salt" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "qq" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "mobile_phone" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "is_validated" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "nick_name" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "channel" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "recommend_partner" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "plate_number" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "reg_ip" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "allinpay_id" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL ) ;

Я пытаюсь настроить данные pgexercises на моем локальном компьютере. Когда я запускаю: psql -U <username> -f clubdata.sql -d postgres -x, я получаю сообщение об ошибке: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction.

Почему на моем локальном компьютере была создана база данных, доступная только для чтения? Могу я это изменить?

5 ответов

Лучший ответ

Обратился к pgexercises.com, и они смогли мне помочь.

Я выполнил эти команды (отдельно):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Затем я сбросил базу данных с терминала dropdb exercises и снова запустил скрипт psql -U <username> -f clubdata.sql -d postgres -x -q


17

ltrainpr
4 Июл 2015 в 04:45

Это произошло, когда я восстанавливал производственную базу данных локально, база данных все еще выполняет восстановление в оперативном режиме из записей WAL.

Немного неожиданно, поскольку я предположил, что pgbackgrest создает мгновенно восстанавливаемые восстановления, возможно, нет.

 91902 postgres  20   0 1445256  14804  13180 D   4.3  0.3   0:28.06 postgres: startup   recovering 000000010000001E000000A5  


0

Kevin Parker
6 Дек 2019 в 15:28

Это не совсем ответ на исходный вопрос, но я получил ту же ошибку и нашел эту страницу, что в конечном итоге привело к исправлению.

Моя проблема заключалась в попытке запустить функцию с созданием и удалением временных таблиц. Функция была создана с правами SECURITY DEFINER, и пользователь имел доступ локально.

В другой среде я получил сообщение об ошибке cannot execute DROP TABLE in a read-only transaction. Этой средой была AWS Aurora, и по умолчанию разработчикам, не являющимся администраторами, были предоставлены права только для чтения. Таким образом, их серверные соединения были настроены на использование доступного только для чтения узла Aurora (-ro- находится в URL-адресе соединения), который должен перевести соединение в состояние только для чтения. Выполнение той же функции с тем же пользователем для узла записи сработало.

Похоже, хороший вариант использования табличных переменных, таких как SQL Server! Или, по крайней мере, AWS следует изменить свой поток, чтобы разрешить создание и удаление временных таблиц на узлах чтения.


1

ps2goat
7 Май 2020 в 15:35

Я получал cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction и другие.

Все они следовали cannot execute INSERT in a read-only transaction. Как будто соединение переключилось в режим только для чтения в середине моей пакетной обработки.

Оказывается, у меня закончилась память! Доступ для записи был отключен, когда база данных больше не могла ничего писать. Я использую Postgres в Azure. Я не знаю, случился бы такой же эффект, если бы я был на выделенном сервере.


7

Brad Mathews
15 Янв 2019 в 04:52

Обычно наиболее вероятными причинами такого рода ошибок являются:

  • попытка создания операторов на реплике, доступной только для чтения (весь экземпляр доступен только для чтения).

  • <username> имеет default_transaction_read_only значение ON

  • в базе данных установлено значение «default_transaction_read_only«

В первых строках упомянутого сценария:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

и вы сообщаете, что ошибка возникает с CREATE SCHEMA в строке 6, а не ранее.

Это означает, что CREATE DATABASE действительно работает, когда запускается <username>. И это не сработало бы, если бы любая из вышеперечисленных причин была прямо применима.

Одна из возможностей, которая технически объяснила бы это, заключалась в том, что default_transaction_read_only будет ON в файле postgresql.conf и установлен на OFF для базы данных postgres, той самой к которому подключается вызов psql через оператор ALTER DATABASE, заменяющий файл конфигурации.

Вот почему CREATE DATABASE работает, но как только он подключается к другой базе данных с помощью c, параметр default_transaction_read_only сеанса переключается на ON.

Но, конечно, это была бы довольно странная и необычная конфигурация.


29

Daniel Vérité
2 Июл 2015 в 16:37

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Error cannot enqueue query after invoking quit
  • Error cannot enqueue query after fatal error
  • Error cannot enqueue handshake after invoking quit
  • Error cannot enqueue handshake after already enqueuing a handshake
  • Error cannot determine path to tools jar library for 16

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии